Q1. Identify situations in which the DBMS_SQL package is the only applicable method of processing dynamic SQL. (Choose all that apply.) 

A. When a query returns multiple rows 

B. When a column name in a where clause is unknown at compile time. 

C. When the number of columns selected in a query is not known until run time 

D. When a table needs to be created based on an existing table structure at run time 

E. When privileges need to be granted to a new user to access an existing schema at run time 

View Answer

Q2. View the Exhibit to examine the PL/SQL code. 

Which statement is true about the execution of the code? 

A. The exception raised in the code is handled by the exception handler for the PAST_DUE exception. 

B. It does not execute because you cannot declare an exception with a similar name in the subblock. 

C. The PAST_DUE exception raised in the subblock causes the program to terminate abruptly because there is no exception handler in the subblock. 

D. The PAST_DUE exception raised by the enclosing block is not propagated to the outer block and it is handled by the WHEN OTHERS exception handler 

View Answer

Q3. 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

Q4. View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables. 

EMPLOYEES 

DEPAERTMENT 

EMP_BY_DEPT 

Examine the following code: 

What is the outcome on execution of the above code? 

A. It executes successfully but the output statements show different values. 

B. It executes successfully and both output statements show the same values. 

C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT. 

D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL 

View Answer

Q5. User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database. 

SCOTT creates the following trigger: 

CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA 

BEGIN 

RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object'); 

END: 

SCOTT does not grant the execute privilege on this trigger to any other users. 

For which user(s) would this trigger fire by default when they drop an object in the hr schema? 

A. Only HR 

B. SCOTT and HR 

C. Only SCOTT 

D. SCOTT, HR, and SYS 

View Answer

Q6. Which two statements correctly differentiate functions and procedures? (Choose two.) 

A. A function can be called only as part of a SQL statement, whereas a procedure can be called only as a PL7SQL statement. 

B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment. 

C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement. 

D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environment. 

View Answer

Q7. View the exhibit and examine the structure of the EMPLOYEES table 

The salary of EMPLOYEE_ID 195 is 2800. 

You execute the following code 

What is the outcome? 

A. It gives an error because only the innermost block is labeled. 

B. It gives an error because the same variable name cannot be used across all the nested blocks. 

C. It executes successfully and displays the resultant values in the following sequence-1000, 2800 50000, 2800. 

D. It executes successfully and displays the resultant values in the following sequence: 1000, 2800, 50000, 1000. 

View Answer

Q8. Examine the following DECLARE section of PL/SQL block: 

Which line in the above declaration would generate an error? 

A. Line 2 

B. Line 3 

C. Line 4 

D. Line 5 

E. Line 6 

View Answer

Q9. View Exhibit1 and examine the structure of the product table. 

View Exhiblt2 and examine the procedure you created. The procedure uses the prod id to determine whether the list price is within a given range. 

You then create the following trigger on the product table. 

CREATE OR REPLACE TRIGGER check_price__trg 

BEF0RE INSERT OR UPDATE OF prod_id, prod_list_price 

ON product FOR EACH ROW 

WHEN (nev.prod_id <> NVX(old.prod_id,0) OR 

New.prod__list_price <> NVL(old.prod_list_price, 0) ) 

BEGIN 

check_price (: new.prod_id) ; 

END 

/ 

Examine the following update command for an existing row in the product table. 

SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115; 

Why does it generate an error? 

A. Because the procedure call in the trigger is not valid 

B. Because the condition specified in the when clause is not valid 

C. Because both the procedure and trigger access the same table 

D. Because the WHEN clause cannot be used with a row-level trigger 

E. Because the column list specified with UPDATE in the trigger is not valid 

View Answer

Q10. Which three statements are true about wrapping? (Choose three.) 

A. The PL/SQL wrapper detects and reports only syntax errors. 

B. The PL/SQL wrapper detects and reports both syntax and semantic errors. 

C. When wrapping a package or object type, both the body and specification should be wrapped. 

D. When wrapping a package or object type, only the body should be wrapped, not the specification. 

E. To change a wrapped object, the original source code needs to be modified and then wrapped again 

F. To change a wrapped object, the wrapped code can be unwrapped, modified In a text file, and then wrapped again. 

View Answer