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

Q2. Which two statements are true about the %ROWTYPE attribute? (Choose two.) 

A. It is used to declare a record that can hold multiple rows of a table. 

B. The attributes of fields in the record with the %ROWTYPE attribute can be modified manually. 

C. The attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable. 

D. It ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered. 

View Answer

Q3. View the exhibit and examine the structure of the products table. 

Examine the following code 

Which statement is true when the procedure DELETE_DETAILS is invoked? 

A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table 

B. It executes successfully and any error messages get recorded in the DEBUG_OUTPUT table. 

C. It gives an error because PRAGMA AUTONOMOUS_TRANSACTION can be used only in packaged procedures. 

D. It gives an error because procedures containing PRAGMA AUTONOMOUS_TRANSACTION cannot be called from the exception section. 

View Answer

Q4. Which statement is true about triggers on data definition language (DDL) statements? 

A. They can be used to track changes only to a table or index. 

B. They can be defined by all users in the database or only by a specific user. 

C. They are fired only when the owner of the object Issues the DDL statement. 

D. They can be used to track changes to a table, table space, view, or synonym. 

View Answer

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

View Exhibit2 and examine the code. 

EKPNOS 7845 and 7900 exist in the EMP table. 

Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? (Choose two.) 

A. call in line 6 

B. call in line 7 

C. call in line 8 

D. call in line 9 

View Answer

Q6. Examine the following code: 

The above code generates an error on execution. 

What must you do to ensure that the code executes successfully? 

A. Use the TO_DATE function in line 2. 

B. Use the TO_DATE function in line 7. 

C. Use the TO_NUMBER function in line 6. 

D. Use both the TO_DATE function in line 2 and the TO_NUMBER function in line 6. 

View Answer

Q7. Which two statements are true about anonymous blocks and named subprograms? 

(Choose two) 

A. Subprograms are by default executed with definer's rights. 

B. The declare section is optional for both anonymous blocks and subprograms. 

C. Both anonymous blocks and subprograms execute by default with invoker's rights. 

D. The declare section is mandatory for anonymous blocks and optional for subprograms. 

View Answer

Q8. Examine the following code: 

What is the outcome? 

A. The procedure is created successfully and displays the values 20 and 30 when it is called. 

B. The procedure gives errors because the parameters should be in out mode. 

C. The procedure gives errors because the host variables cannot be referenced anywhere in the definition of a PL/SQL stored procedure. 

D. The procedure is created successfully but does not display any values when it is called because the host variables cannot be displayed inside the procedure. 

View Answer

Q9. Which two statements are true about the usage of the cursor for loops? (Choose two.) 

A. The cursor needs to be closed after the iteration is complete. 

B. The implicit open, fetch, exit, and close of the cursor happen. 

C. The record type must be explicitly declared to control the loop. 

D. The PL/SQL creates a record variable with the fields corresponding to the columns of the cursor result set. 

View Answer

Q10. You want to create a trigger that fires whenever rows are deleted from the customer table and that displays the number of rows remaining in the table. 

Which two statements are correct about the trigger to be created for the above requirement? (Choose two.) 

A. It should be an after trigger. 

B. It should be a before trigger. 

C. It should be a row-level trigger. 

D. It should be a statement-level trigger. 

E. It can be a before or an after trigger. 

View Answer