1Z0-144 Exam - Oracle Database 11g: Program with PL/SQL

certleader.com

Q1. Examine the following PL/SQL code: 

Which statement is true about the execution of the PL/SQL code? 

A. It executes successfully. 

B. It generates a run-time exception. 

C. It does not execute because of syntax error 

D. It executes successfully and generates a warning. 

Answer:

Explanation: Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm 

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

Answer: B,C 

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

Answer:

Q4. Which two statements are true about the continue statement? (Choose two.) 

A. The PL/SQL block execution terminates immediately. 

B. The CONTINUE statement cannot appear outside a loop. 

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

D. The statements after the continue statement in the iteration are executed before terminating the LOOP. 

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

Answer: B,E 

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

Answer:

Q6. Examine the following snippet of code from the DECLARE section of PL/SQL 

DECLARE 

Cust_name VERCHAR2 (20) NOT NULL : = ‘Tom Jones’: 

Same_name cust_name%TYPE: 

Which statement is correct about the above snippets of code? 

A. The SAME_NAME variable inherits only the data type from the CUST_NAME variable. 

B. The SAME_NAME variable inherits only the data type and default value from the CUST_NAME variable. 

C. The SAME_NAME variable inherits the data type, constraint, and default value from the CUST_NAME variable. 

D. The SAME_NAME variable inherits only the data type and constraint from the CUST_NAME variable resulting in an error 

Answer:

Q7. Which two statements are true about the instead of triggers? (Choose two.) 

A. Delete operations cannot be performed using the instead of triggers. 

B. The instead or triggers must be created to add or modify data through any view. 

C. The instead of triggers can be written only for views, and the before and after timing options are not valid. 

D. The check option for views is not enforced when Insertions or updates to the view are performed by using the instead of trigger. 

Answer: B,C 

Q8. The STRING_TAB table has the following structure: 

View the Exhibit and examine the code. 

What is the outcome on execution? 

A. It displays 

Output buffer not long enough. 

This is my test string.-. 

B. It displays only 

Output buffer not long enough, and exits the anonymous block. 

C. It displays only 

This is my test string. - Because EXCEPTION should have been defined in the anonymous 

block to get the error message. 

D. It does not display any of the MEMS_PUTPUT messages and gives an error because a 

transaction control statement cannot be used in the exception section of a procedure. 

Answer:

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

View Exhibit2 and examine the code. 

The anonymous block gives an error on execution. What is the reason? 

A. The assignment in line 7 is not valid. 

B. The SQL does not support the Boolean data type. 

C. A null value cannot be applied to the bind arguments In the using clause in line 10 

D. The names of bind variables must be the same as the using clause bind arguments in line 10 

Answer:

Q10. Examine the following PL/SQL code: 

Which statement is true about the execution of the code if the query in the PL/SQL block returns no rows? 

A. The program abruptly terminates and an exception is raised. 

B. The program executes successfully and the output is No ROWS_FOUND. 

C. The program executes successfully and the query fetches a null value in the V_LNAME variable. 

D. Program executes successfully, fetches a NULL value in the V_LNAME variable and an 

exception is raised. 

Answer: