Oracle Oracle Certifications 1Z0-144 Questions & Answers

• Question 61:

  Examine the following package specification.

  SQL>CREATE OR REPLACE PACKAGE emp_pkf IS PROCEDURE search_emp (empdet NUMBER); PROCEDURE search_emp (empdet DATE); PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2 PROCEDURE search_emp (empdet NUMBER); RETURN DATE END emp_pkg /

  The package is compiled successfully

  Why would it generate an error at run tune?

  A. Because function cannot be overload.

  B. Because function cannot differ only in return type.

  C. Because all the functions and procedures in the package cannot have the same number of parameters with the same parameter name.

  D. Because the search EMP (EMPDET NUMBER) procedure and the SEARCH_DEPT (EMPDET NUMBER) cannot have identical parameter names and data types.

  Correct Answer: B

• Question 62:

  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.

  Correct Answer: A

• Question 63:

  Consider the following scenario:

  Local procedure A calls remote procedure B.

  Procedure B was compiled at 8 AM.

  Procedure A was modified and recompiled at 9 AM.

  Remote procedure B was later modified and recompiled at 11 AM.

  The dependency mode is set to TIMESTAMP.

  Which statement correctly describes what happens when procedure A is invoked at 1 PM?

  A. Procedure A is invalidated and recompiled immediately.

  B. There is no effect on procedure A and it runs successfully.

  C. Procedure B is invalidated and recompiled again when invoked.

  D. Procedure A is invalidated and recompiles when invoked the next time.

  Correct Answer: D

• Question 64:

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

  

  The salary of EMPLOYEE_ID 195 is 2800. You execute the following code:

  

  What is the outcome?

  A. It gives an error because only the innermost block is labeled.

  B. It gives an error because the same variable name cannot be used across all the nested blocks.

  C. It executes successfully and displays the resultant values in the following sequence- 1000, 2800 50000, 2800.

  D. It executes successfully and displays the resultant values in the following sequence: 1000, 2800, 50000, 1000.

  Correct Answer: C

• Question 65:

  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.

  Correct Answer: BD

• Question 66:

  In which of the following scenarios would you recommend using PL/SQL records?

  A. when you want to retrieve an entire row from a table and perform calculations

  B. when you know the number of elements in advance and the elements are usually accessed sequentially

  C. when you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries

  D. when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked

  Correct Answer: A

• Question 67:

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

  

  Execute the following block of code:

  SQL > SET SERVEROUTPUT ON

  

  What is the outcome?

  A. It gives an error because the variable name and column name are the same in the WHERE clause of the SELECT statement.

  B. It executes successfully and displays the sum of salaries in department 60.

  C. It gives an error because group functions cannot be used in anonymous blocks.

  D. It executes successfully and displays the incorrect sum of salaries in department 60.

  Correct Answer: D

• Question 68:

  Examine the following snippet of PL/SQL code:

  

  View the Exhibit for table description of EMPLOYEES table. The EMPLOYEES table has 200 rows.

  

  Identify OPEN statements for opening the cursor that fetches the result set consisting of employees with JOB_ID as `ST_CLERK' and SALARY greater than 3000. (Choose three.)

  A. OPEN c1 (NULL, 3000);

  B. OPEN c1 (emp_job, 3000);

  C. OPEN c1 (3000, emp_salary);

  D. OPEN c1 (`ST_CLERK', 3000)

  E. OPEN c1 (emp_job, emp_salary);

  Correct Answer: BDE

• Question 69:

  In which of the following scenarios would you recommend using associative arrays?

  A. When you want to retrieve an entire row from a table and perform calculations

  B. When you know the number of elements in advance and the elements are usually accessed sequentially

  C. When you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries

  D. When you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked.

  Correct Answer: D

• Question 70:

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

  

  View Exhibit 2 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

  Correct Answer: CD