Oracle Oracle Database 1Z0-144 Questions & Answers

• Question 1:

  View the Exhibit and examine the blocks of code that you plan to execute.

  

  Which statement is true about the blocks of code?

  A. All the blocks execute successfully and the anonymous block displays

  B. All the blocks execute successfully and the anonymous block displays

  C. The anonymous block gives an error because the function invocation in line 2 is not valid.

  D. The procedure creation gives an error because the function invocation in line 1 is not valid.

  Correct Answer: A

• Question 2:

  View the Exhibit and examine the partial data in the PRODUCTS table. PROD_ID is the primary key.

  

  Examine the following code: What is the outcome on execution of the above code?

  

  A. It executes successfully.

  B. It gives an error because the DECODE function can be used only in a SQL statement.

  C. It gives an error because the SUBSTR and INSTR functions can be used only in a SQL statement.

  D. It gives an error because both the MAX and DECODE functions can be used only in a SQL statement.

  Correct Answer: A

• Question 3:

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

  

  View Exhibit 2 and examine the PL/SQL 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.

  Correct Answer: E

• Question 4:

  Which three statements are true about anonymous blocks and subprograms? (Choose three.)

  A. Only subprograms can be parameterized.

  B. Only subprograms are persistent database objects.

  C. Both anonymous blocks and subprograms can be parameterized.

  D. Both anonymous blocks and subprograms are persistent database objects.

  E. Only subprograms can return values that persist after the execution of the subprogram.

  F. Both anonymous blocks and subprograms can return values that persist in SQL*Plus variables after their execution.

  Correct Answer: BCF

• Question 5:

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

  

  EMPLOYEES_SEQ is an existing sequence. Examine the following block of code: Which statement is true about the above block of code?

  

  A. It gives an error on execution because sequences cannot be used in anonymous blocks.

  B. It consists of a single transaction.

  C. It consists of two transactions.

  D. The data is automatically committed after the block execution ends.

  Correct Answer: B

• Question 6:

  View the Exhibit and examine the structure of the customer table.

  

  You create the following trigger to ensure that customers belonging to category "A" or "B" in the CUSTOMER table can have a credit limit of more than 8000.

  

  What is the outcome?

  A. The trigger is fired, a message is displayed, and the update is successful.

  B. The trigger is fired and a message is displayed, but the update is rolled back.

  C. The trigger is not fired because the WHEN clause should be used to specify the condition; however, the update is successful.

  D. The trigger is not fired because column names must be specified with the UPDATE event to identify which columns must be changed to cause the trigger to fire; however, the update is successful.

  Correct Answer: A

• Question 7:

  View Exhibit 1 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 DIRECTORY my_dir AS '/temp/my_files';

  SQL>GRANT WRITE ON DIRECTORY my_dir TO scott;

  View Exhibit 2 and examine the procedure code.

  

  You issue the following command:

  SQL>EXEC sal_status ('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.

  Correct Answer: A

• Question 8:

  View the Exhibit to examine the PL/SQL code.

  

  The record for the employee with employee_id 100 in the EMPLOYEES table is as follows:

  

  Identify the correct output for the code.

  A. King 17-JUN-87 1500

  B. King 17-JUN-87 24000

  C. King current sysdate 1500

  D. King current sysdate 24000

  Correct Answer: C

• Question 9:

  View the Exhibit and examine the structure of the CUSTOMER table.

  

  You need to create a trigger to ensure that customers in category "A" and "B" have a credit limit of more than 8000.

  Examine the following trigger.

  

  Which statement is correct about the outcome of this trigger?

  A. It compiles successfully and fires whenever the specified condition is met.

  B. It compiles successfully but does not fire even when the condition is met.

  C. It gives an error on compilation because the NEW qualifier is prefixed with a colon.

  D. It gives an error on compilation because the NEW qualifier can be used only in row-level triggers.

  Correct Answer: D

• Question 10:

  View the Exhibit to examine the PL/SQL code.

  

  Which statement is true about the exception handlers in the PL/SQL code?

  A. All the exceptions in the code are trapped by the exception handler.

  B. All the "no data found" errors in the code are trapped by the exception handler.

  C. The PL/SQL program does not execute because an exception is not declared in the DECLARE section.

  D. An exception handler in the code traps the "no data found" error after executing the handler code and the program flow returns to the next line of code.

  Correct Answer: B