Oracle 1Z0-144 Free Practice Questions

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

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

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

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

View Exhibit2 and examine the code. 

What is the outcome when the code is executed? 

A. Both blocks compile and execute successfully when called. 

B. Both blocks compile successfully but the CALC_SAL procedure gives an error on execution. 

C. The CALC_SAL procedure gives an error on compilation because the amt variable should be declared in the RAISE_SALARY procedure. 

D. The CALC_SAL procedure gives an error on compilation because the RAISE_SALARY procedure cannot call the stand-alone increase function. 

View Answer

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

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

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

Q8. You execute the following block of code: 

Which statement is true about the outcome? 

A. Both Output statements show different values. 

B. Both output statements show exactly the same values. 

C. It gives an error because the nested blocks are not labeled. 

D. It gives an error because the V_CUSTOMER variable have different types in the nested blocks 

View Answer

Q9. View Exhibit 1 and examine the structure of the EMP table. 

View Exhlbit2 and examine the code of the packages that you have created. 

You issue the following command: 

SQL> DROP PACKAGE manage_emp; 

What is the outcome? 

A. It drops both the MANAGE_EMP AND EMP__DET packages because of the cascading effect. 

B. It drops the MANAGE_EMP package and invalidates only the body for the EMP_DET package. 

C. It returns an error and does not drop the MAMAGE_EMP package because of the cascading effect. 

D. It drops the MANAGE_EMP package and invalidates both the specification and body for the EMP_DET package. 

View Answer

Q10. Examine the following PL/SQL code; 

The execution of the code produces errors. Identify the error in the code. 

A. The open cursor is missing 

B. The fetch clause is missing 

C. The exit when condition is missing 

D. The EMP_NAME and EMP_JOB variables cannot be used in the for clause of the cursor FOR statement. 

View Answer

Q11. Which two statements are true about statement-level and row-level triggers? (Choose two.) 

A. A row trigger fires once even if no rows are affected. 

B. A statement trigger fires once even if no rows are affected. 

C. Row triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself. 

D. Statement triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself. 

View Answer

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

View Answer

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

User SCOTT needs to generate a text report that contains the names of all employees and their salaries. 

Examine the following commands issued by the DBA: 

SQL_CREATE DICTORY my_dir AS '/temp/my_files* ; 

SQL_GRANT WRITE ON DIRECTORY my_dir TO SCOTT; 

View Exhibit2 and examine the procedure code. 

You issue the following command: You issue the following command: 

SQL_EXEC sal_5tatus ('MY_DIR', ‘EMPREPORT.TXT') 

What is the outcome? 

A. It executes successfully and creates the report. 

B. It gives an error because the text file should be opened in append mode. 

C. It gives an error because the "no data found" condition is not handled to come out of the loop. 

D. It gives an error because user SCOTT should be granted both read and write privileges to the directory alias. 

E. It executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file. 

View Answer

Q14. View the Exhibit to examine the PL/SQL block. 

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

A. It executes successfully and gives the desired output. 

B. It does not execute because the definition of type population is indexed by VARCHAR2. 

C. It executes, and the string keys of an associative array are not stored in creation order, but in sorted order. 

D. It does not execute because the value that is once assigned to the element of the associative array cannot be changed. 

View Answer

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