Oracle PL/SQL Fundamentals

Which one of the following built-in packages do you use to work around the previous 1,000,000 hard limit on the buffer size of DBMS_OUTPUT for any session when directly writing output from PL/SQL?
Choice 1 : DBMS_PIPE
Choice 2 : DBMS_IO
Choice 3 : UTL_FILE
Choice 4 : UTL_PIPE
Choice 5 : DBMS_FILE

===============================================================

Scenario
The database has reported the error:
ORA-04091: table is mutating, trigger/function may not see it.
What is one possible explanation for the error displayed in the scenario above?
Choice 1 : A statement trigger your statement fired has executed an improperly formed Select result, resulting in a large Cartesian product.
Choice 2 : You have attempted to change a table currently being updated by a row trigger another user has fired.
Choice 3 : Another user has changed a table referenced by a row trigger that your statement fired.
Choice 4 : A statement trigger that your statement fired has attempted to select from a global temporary table.
Choice 5 : A row trigger that your statement fired has attempted to access its triggering table.

===============================================================

Scenario
You have data containing a numeric postal code in the form nnnnn-zzzz, where the -zzzz is an optional item that you want to capture if available. You want to isolate the postal code from the line of text in which it appears.

The data is stored in address in the form:

City, State Postal Code

Given the scenario described above, which one of the following regular expressions isolates the postal code?
Choice 1 : SELECT REGEXP_SUBSTR(address, '[[:digit:]]{5}') post_code FROM dual;
Choice 2 : REGEXP_SUBSTR(address, '[[:digit:]]{5}(-[[:digit:]]{4})*$')
Choice 3 : SELECT REGEXP_INSTR(address, '[[:digit:]]{5}', 1, 1, 0, 'i') FROM datatable;
Choice 4 : SELECT REGEXP_SUBSTR(address, '[[:digit:]]{5}', 1, 1, 0, 'i') FROM datatable;
Choice 5 : REGEXP_LIKE(address, '[[:digit:]]{5}.*[[:space:]]')

===============================================================

Which one of the following is a fundamental difference between WHILE Loops and numeric FOR Loops?
Choice 1 : When numbers are used in a WHILE Loop, they are actually character data; numeric FOR Loops use binary_integer values.
Choice 2 : WHILE Loops cannot be based on numeric conditions; numeric FOR Loops are always based on numeric conditions.
Choice 3 : WHILE Loops terminate explicitly; the termination point of numeric FOR Loops is stated implicitly.
Choice 4 : WHILE Loops depend on the state of a variable and terminate when the state changes; FOR Loops do not.
Choice 5 : The number of iterations a WHILE Loop makes is unknown or not specified; numeric FOR Loops are designed to loop a specific number of times.

===============================================================


Scenario
You are creating a routine to update the database that uses a transaction. You need to create a transaction in which queries within that transaction see only changes that were committed before the transaction began.

Given the scenario described above, which command do you use to limit queries to the data you need?
Choice 1 : SET TRANSACTION ISOLATION LEVEL READ ONLY
Choice 2 : ROLLBACK
Choice 3 : SET QUERY SCOPE COMMITTED
Choice 4 : ROLLBACK TO COMMITTED
Choice 5 : LIMIT QUERY TO COMMITTED

===============================================================

For which one of the following do you use the NULL statement?

Choice 1 : To perform no operation
Choice 2 : To execute in real-time mode
Choice 3 : To return an undefined value
Choice 4 : To pause execution for 50 milliseconds and, often, to time sensitive areas of code
Choice 5 : To call an exception handler

===============================================================

Scenario
You have two strings that you need to join together to make one string. One string is in variable a and the other is in variable b. You want b to precede a.
Given the scenario described above, which command do you use to join together two separate strings?
Choice 1 : RIGHT JOIN (a,b)
Choice 2 : LPAD (b,a)
Choice 3 : LEFT JOIN (a,b)
Choice 4 : CONCAT(b, a)
Choice 5 : SUBSTR (b,a)

===============================================================

Read more...

Oracle PL/SQL Fundamentals

With regard to performance, why do you combine SQL statements into PL/SQL blocks?
Choice 1 : More debugging information is available for SQL statements within PL/SQL.
Choice 2 : The query optimizer is run during compile time only.
Choice 3 : Authentication time is decreased.
Choice 4 : Network traffic is decreased.
Choice 5 : Storage requirements are decreased.

===============================================================

Scenario
You want to use the DBMS_LDAP functions and procedures, but when you try to use them, you receive an error that indicates that DBMS_LDAP cannot be found.
Given the scenario described above, how do you enable DBMS_LDAP?
Choice 1 : Turn off basic authentication.
Choice 2 : Set the LDAP server to authenticate using version 3 of the protocol.
Choice 3 : Add the shared object files for the LDAP implementation you are using.
Choice 4 : Load $ORACLE_HOME/rdbms/admin/catldap.sql.
Choice 5 : Import the LDAP binary drivers.

===============================================================

Which one of the following is a PL/SQL subprogram?
Choice 1 : UPDATE
Choice 2 : PROCEDURE
Choice 3 : ANONYMOUS BLOCK
Choice 4 : DBMS_SESSION
Choice 5 : PACKAGE
===============================================================

Scenario
You are using a cursor named employee_cur in a program. Your program occasionally displays ORA-06511: PL/SQL: cursor already open; you would like to eliminate that error.
Given the scenario described above, how do you eliminate the error in your program?
Choice 1 : Clear the cursor cache by issuing cursorsync prior to opening employee_cur.
Choice 2 : Delete all records from the v$open_cursor view.
Choice 3 : Open the cursor only if employee_cur%ISOPEN is false.
Choice 4 : Synchronize v$librarycache.
Choice 5 : Delete records with a Type of Cursor from the v$bh view.

===============================================================

Which one of the following implicit datatype conversions is supported in Oracle?
Choice 1 : NUMBER to ROWID
Choice 2 : DATE to VARCHAR2
Choice 3 : DATE to BLOB
Choice 4 : NUMBER to DATE
Choice 5 : ROWID to DATE

===============================================================

Scenario
You have a project that requires for SQL code to adhere to the ISO SQL standard. You need to perform explicit conversions between data types.
Given the scenario described above, which command do you use?
Choice 1 : TO_NUMBER
Choice 2 : CONVERT
Choice 3 : TO_CHAR
Choice 4 : TYPE
Choice 5 : CAST

===============================================================

Scenario
Your company is implementing several security imperatives. To comply with one of the imperatives; you need to limit the file system directories that are accessible via UTL_FILE.
Given the scenario described above, how do you allow only specific directories to be accessible?
Choice 1 : Define the value PL_ALLOWED_DIR with the allowed directories in V$SYSTEM
Choice 2 : Set the file system to be read-only.
Choice 3 : Set the file system with the NOSUID attribute.
Choice 4 : Set UTL_FILE_DIR='/allowed/directory' in INIT.ORA
Choice 5 : Use the command INSERT INTO V$FILES (UTIL_FILE) VALUES ('/allowed/directory');.

===============================================================

Scenario
You have a large database configured. You want to allow a portion of a transaction to be rolled back after a certain point in processing.
Given the scenario described above, which one of the following commands do you use to mark the spot for rollback?
Choice 1 : BOOKMARK
Choice 2 : SAVEPOINT
Choice 3 : COMMIT TO
Choice 4 : SET TRANSACTION
Choice 5 : ROLLBACK TO

===============================================================

Sample Code
TYPE relatives IS TABLE OF relatives%ROWTYPE
INDEX BY BINARY_INTEGER;

The sample code above is an example of which one of the following?

Choice 1 : A PL/SQL record
Choice 2 : A PL/SQL table type
Choice 3 : A PL/SQL table
Choice 4 : A PL/SQL table index
Choice 5 : A multi-row table variable

===============================================================

Read more...

Oracle PL/SQL Fundamentals

Scenario : You are in the process of issuing several UPDATE statements within a single transaction. During this transaction, an update fails and you issue a rollback.
Given the scenario described above, what happens to the database?
Choice 1 : A transaction is valid ONLY for INSERT statements; therefore, all UPDATE statements will be written to the database.
Choice 2 : The database retries the failed UPDATE statement one more time and undoes its action if it fails again.
Choice 3 : The successful UPDATE statements are permanently written to the database. The last UPDATE did not get committed and was rolled back.
Choice 4 : All UPDATE statements within the transaction are rolled back and the database is consistent with its state before the transaction.
Choice 5 : All data is reverted to the state the database was in when it was first started.
===================================================================

Scenario : You are using an IF-THEN-ELSIF-ELSE-END IF statement, and all the conditions specified evaluate to NULL.
Given the scenario described above, which one of the following statements is executed?
Choice 1 : One of the ELSIF parts
Choice 2 : The NULL value handler
Choice 3 : The ELSE part
Choice 4 : The divide by zero exception handler
Choice 5 : The IF part
==================================================================

Scenario : There is a package named UTILPKG, which contains 84 procedures and 16 functions.

Referring to the scenario above, when you call a function UTILPKG.file_rename, which one of the following fully describes what is loaded into memory?

Choice 1 : Both the function specification and definition for UTILPKG.file_rename and dependencies in UTILPKG are loaded.
Choice 2 : Both the entire package specification and body for UTILPKG are loaded.
Choice 3 : Only the function definition for UTILPKG.file_rename and its dependencies, if UTILPKG.file_rename has cursor references in the specification, is loaded.
Choice 4 : Only the function definition for UTILPKG.file_rename
Choice 5 : Only the function specification for UTILPKG.file_rename
===========================================================================================
Scenario : You have the following string:

This is a test. This is only a test.
You want to replace every occurrence of the word test with the word trial.

Given the scenario described above, which one of the following commands do you use to accomplish this?
Choice 1 : 'This is a test. This is only a test.' =~ s/test/trial/g
Choice 2 : 'This is a test. This is only a test.' =~ s/test\./trial\./
Choice 3 : SUBSTR('This is a test. This is only a test.','test','trial')
Choice 4 : REPLACE('This is a test. This is only a test.', 'test','trial')
Choice 5 : UPDATE('This is a test. This is only a test.' set 'test','trial')
==================================================================

Which one of the following is a data definition language (DDL) command?
Choice 1 : INSERT
Choice 2 : TRUNCATE
Choice 3 : DELETE
Choice 4 : SELECT
Choice 5 : UPDATE
===============================================================
Oracle stores P-code in the database for which one of the following structures?
Choice 1 : Indexes
Choice 2 : Named PL/SQL blocks
Choice 3 : ODBC clients
Choice 4 : Object relational models
Choice 5 : Anonymous PL/SQL blocks
===============================================================

Read more...

Oracle PL/SQL Fundamentals

Scenario: You have a database that uses a computationally intensive calculation that is accessed often in a SELECT query. You want to improve the execution speed of this calculation.

Given the scenario described above, which one of the following do you use to accomplish this?
Choice 1 : Global and local index
Choice 2 : B-tree cluster index
Choice 3 : Function-based index
Choice 4 : Real Application Cluster with load balancing enabled
Choice 5 : The parallel computing package

===============================================================

When tracing is enabled, which one of the following scripts do you run in order to create the tables that collect trace information generated by DBMS_TRACE?

Choice 1 : TRACETAB.sql
Choice 2 : UTLXTRC.sql
Choice 3 : TRCTABLE.sql
Choice 4 : DBMSTRC.sql
Choice 5 : PLSQLTRC.sql

===============================================================

Scenario : You need to create 50 tables. The names are based on parameters stored in an existing table. You have created a PL/SQL program that retrieves the data from the table and loops through each item. The program outputs the correct data when you use dbms_output.put_line('CREATE TABLE ' || X || '(a number)'), but when you try to execute CREATE TABLE X (A number) it does not function.

Given the scenario described above, which one of the following do you do in order for the CREATE TABLE statement to function properly?
Choice 1 : Precede the statement with EXECUTE IMMEDIATE.
Choice 2 : Pipe the output to the DBMS.SQL function.
Choice 3 : Update the program state to DDL mode before executing the statement.
Choice 4 : Clear the statement cache prior to executing the statement.
Choice 5 : Close any existing open cursors before running the statement.
==========================================================

When you store a PL/SQL table (Table type) in the database, how many columns does the table have?
Choice 1 : 1
Choice 2 : 2
Choice 3 : 3
Choice 4 : As many as are necessary in the program
Choice 5 : One plus the number of columns in the nested table
============================================================

Scenario : You have created a new database. You want to use DBMS_PROFILER to measure execution time of a pl/sql program unit. When you try to use DBMS_PROFILER, you find that it is not available.
Given the scenario described above, how do you make DBMS_PROFILER available for use?
Choice 1 : Create the stats collection table in your database.
Choice 2 : Execute dbms_profiler.flush_data.
Choice 3 : Run profload.sql as sys and proftab.sql as the profile user.
Choice 4 : Increase the system global area heap size to at least two gigabytes.
Choice 5 : Set serverprofiler on enabled.
============================================================================================

Which one of the following is a common reason for slow PL/SQL program execution?
Choice 1 : Setting garbage collection intervals
Choice 2 : Forgetting to SET SERVEROUTPUT ON
Choice 3 : Disabling the Oracle Trace facility
Choice 4 : Writing SQL statements that are not optimized
Choice 5 : Neglecting to specify as many columns as possible to be NOT NULL during table creation
===========================================================

For which one of the following purposes do you use regular expressions?
Choice 1 : Matching text patterns
Choice 2 : Balancing binary trees
Choice 3 : Tracking changes over time
Choice 4 : Improving readability of code
Choice 5 : Describing the purpose of a function or statement
============================================================

Why do you use cursor attributes?
Choice 1 : To open and close cursors
Choice 2 : To populate cursors
Choice 3 : To record cursor execution time
Choice 4 : To control cursors
Choice 5 : To get information about cursors
=============================================================

Scenario : You have the following line of code in a program:

statement := "SELECT * FROM users WHERE name = '" + Name + "';"

The line is called from an application running on a public facing Web server. Given the scenario described above, what is the security issue with this code?

Choice 1 : SQL injection
Choice 2 : Fragment attack
Choice 3 : Buffer overflow
Choice 4 : Heap exhaustion
Choice 5 : Spoofing attack
===============================================================

Read more...