Q1. View the exhibit to examine the PL/SQL code. 

Which statement is true about the exception handlers in the PL/SQL code? 

A. All the exceptions in the code are trapped by the exception handler. 

B. All the "no data found" errors in the code are trapped by the exception handler. 

C. The PL/SQL program does not execute because an exception is not declared in the declare section. 

D. An exception handler in the code traps the "no data found" error after executing the handler code and the program flow returns to the next line of code. 

View Answer

Q2. What is the correct definition of the persistent state of a packaged variable? 

A. It is a private variable defined in a procedure or function within a package body whose 

value is consistent within a user session. 

B. It is a public variable in a package specification whose value is consistent within a user session. 

C. It is a private variable in a package body whose value is consistent across all current active sessions. 

D. It is a public variable in a package specification whose value is always consistent across all current active sessions. 

View Answer

Q3. View the Exhibit and examine the structure of the EMP table. 

Which stages are performed when the above block is executed? (Choose all that apply) 

A. Bind 

B. Parse 

C. Fetch 

D. Execute 

View Answer

Q4. Identify two situations where the DBMS_SQL package should be used. (Choose two.) 

A. The SELECT list is not known until run time. 

B. The dynamic SQL statement retrieves rows into records. 

C. You do not know how many columns a select statement will return, or what their data types will. 

D. You must use the %found SQL cursor attribute after issuing a dynamic SQL statement that is an insert or update statement. 

View Answer

Q5. In which of the following scenarios would you recommend using associative arrays? 

A. When you want to retrieve an entire row from a table and perform calculations 

B. When you know the number of elements in advance and the elements are usually accessed sequentially 

C. When you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries 

D. When you want to create a relatively small lookup table, where the collection can be constructed on memory each time a subprogram is invoked. 

View Answer

Q6. Which statements correctly describe the features of functions and procedures? (Choose all that apply.) 

A. A procedure can contain a return statement without a value. 

B. A function can return multiple values using a single return clause, 

C. A procedure can be executed as part of a SQL expression or as a PL/SQL statement, 

D. A function can contain zero or more parameters that are transferred from the calling environment. 

View Answer

Q7. Examine the following PL/SQL code: 

Which statement is true about the fetch statements in the PL/SQL code? 

A. Each fetch retrieves the first row and assigns values to the target variables. 

B. Each fetch retrieves the next consecutive row and assigns values to the target variables. 

C. They produce an error because you must close and reopen the cursor before each fetch -statement. 

D. Only the first fetch retrieves the first row and assigns values to the target variables- the second produces an error. 

View Answer

Q8. View Exhibit1 and examine the structure of the EMP table. 

View Exhibit2 and examine the PIVSQL block of code. 

What is the outcome? 

A. It gives an error because the return type is not valid. 

B. It gives an error because the record type is not defined within the function 

C. It gives an error because the function call in DBMS_OUTPUT. PUT__LINE is not valid 

D. It executes successfully and displays the names and salaries of all employees who earn the highest salary. 

E. It executes successfully but does not display the names and salaries of all employees who earn the highest salary. 

View Answer

Q9. Which two statements are true about the exit statement encountered in loop? (Choose two) 

A. The PL/SQL block execution terminates immediately after the exit statement. 

B. The loop completes immediately and control passes to the statement after end loop 

C. The statements after the exit statement in the Iteration are not executed before terminating the LOOP. 

D. The current iteration of the loop completes immediately and control passes to the next iteration of the loop. 

View Answer

Q10. You create a procedure to handle the processing of bank current accounts which rolls back payment transactions if the overdraft limit is exceeded. 

The procedure should return an "error" condition to the caller in a manner consistent with other Oracle server errors. 

Which construct should be used to handle this requirement? 

A. The SQLERRM function 

B. The PRAGMA EXCEPTION_INIT function 

C. The RAISE_APPLICATION_ERROR procedure 

D. A user-defined exception used with a raise statement 

View Answer