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)

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