Q1. Which two statements are true about triggers? (Choose two.) 

A. All the triggers that are created on a table cannot be disabled simultaneously. 

B. Any user who has the alter privilege on a table can create a trigger using that table. 

C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database. 

D. Triggers become invalid if a dependent object, such as 3 stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation. 

View Answer

Q2. Examine the following code that you plan to execute: 

What correction should be performed in the above code? 

A. The PROC2 procedure code should be defined in the package body. 

B. The PROC3 procedure should be declared in the package specification. 

C. The PROC3 procedure header should be declared at the beginning of the package body. 

D. The variable x must be declared in the package body and removed from the specification, 

View Answer

Q3. Examine the following partial declare section from a block of PL/SQL code 

Which line(s) in the above code are NOT valid? (Choose all that apply.) 

A. line 2 

B. line 3 

C. line 4 

D. line 5 

View Answer

Q4. /temp/my_files is an existing folder in the server, facultylist.txt is an existing text file in this folder 

Examine the following commands that are executed by the DBA: 

SQL>CREATE DIRECTION my_dir AS ‘ /temp/my_files’: 

SQL>GRANT READ ON DIRECTORY my_dir To pubiic: 

View the Exhibit and examine the procedure created by user SCOTT to read the list of faculty names from the text file. 

SCOTT executes the procedure as follows: 

SQL>SET SERVEROUTPUT ON 

SQL>EXEC read_file (‘MY_DIR’, FACULTYLIST.TXT’) 

What is the outcome? 

A. It goes into an infinite loop. 

B. It executes successfully and displays only the list of faculty names. 

C. It does not execute and displays an error message because the end-of-file condition is not taken care of. 

D. It executes successfully and displays the list of faculty names followed by a "no data found” error message. 

View Answer

Q5. View the Exhibit and examine the structure of the departments table in SCOTT’s schema. 

Examine the following block of code: 

CREATE OR REPLACE PROCEDURE add_dept( 

p_id NUMBER, p_name VARCHAR2) IS 

BEGIN 

INSERT INTO departments VALUES <p_id, p_name, NULL, NULL); 

END; 

/ 

The above procedure is created by user SCOTT. Another user JONES needs to use the 

procedure. 

Which two statements are true in the above scenario? (Choose two.) 

A. JONES executes the procedure with definer's rights. 

B. JONES executes the procedure with invoker's rights. 

C. SCOTT should grant only the execute privilege for the procedure to JONES. 

D. SCOTT should grant both the BXKCOTE privilege for the procedure and insert privilege for the table to 

View Answer

Q6. Which system events can be used to create triggers that fire both at database and schema levels? (Choose all that apply) 

A. AFTER LOGON 

B. AFTER STARTUP 

C. BEFORE SHUTDOWN 

D. AFTER SERVERERROR 

View Answer

Q7. Examine the following PL/SQL code: 

The server output is on for the session. Which statement is true about the execution of the code? 

A. It displays null if no employee with employee_id 123 exists. 

B. It produces the ora-01403: no data found error if no employee with employee_id 123 exists. 

C. It displays an error because the select into clause cannot be used to populate the PL/SQL record type. 

D. The code executes successfully even if no employee with employee_id 123 exists and displays Record Not Found. 

View Answer

Q8. Examine the following block of code: 

Which two statements are correct about the code above? (Choose two.) 

A. The function goes through only the parse and executes phases. 

B. The function goes through the parse, bind, and execute phases. 

C. The function goes through the parse, bind, execute, and fetch phases. 

D. All the processing phases for the function are performed only at run time. 

E. Only the EXECUTE IMMEDIATE statement inside the function is parsed at run time. 

View Answer

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

View Exhibit2 and examine the code. 

What would be the outcome when the code is executed? 

A. It executes successfully. 

B. It gives an error because the SAL variable is not visible in the increase function. 

C. It gives an error because the increase function cannot be called from the RAISE_SALARY procedure. 

D. It gives an error because the increase function and the RAISE_SALARY procedure should be declared at the beginning of the declare section before all the other declarations. 

View Answer

Q10. View the Exhibit to examine the PIVSQL block. 

Which statement is true about the output of the PL/SQL block? 

A. It executes and the Output is emprec.deptname: . 

B. It executes and the Output is emprec.deptname: Sales. 

C. It produces an error because NULL is assigned to the emprec.empid field in the record. 

D. It produces an error because the CHECK constraint is violated while assigning a value to the emprec.deptid field in the record. 

View Answer