Oracle Oracle Database 1Z0-144 Questions & Answers

• Question 41:

  Examine the following PL/SQL code: Which statement is true about the fetch statements in the PL/SQL code?

  

  A. Each fetch retrieves the first row and assigns values to the target variables.

  B. Each fetch retrieves the next consecutive row and assigns values to the target variables.

  C. They produce an error because you must close and reopen the cursor before each fetch -statement.

  D. Only the first fetch retrieves the first row and assigns values to the target variables- the second produces an error.

  Correct Answer: B

• Question 42:

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

  

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

  Correct Answer: A

• Question 43:

  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. The code executes successfully and gives the desired output.

  B. The code generates an error because the EMP_RECORD variable is not declared.

  C. The code generates an error because the cursor is not opened before the FOR loop.

  D. The code generates an error because the loop does not have the exit when clause.

  Correct Answer: A

• Question 44:

  Which two statements are true about the %ROWTYPE attribute? (Choose two.)

  A. It is used to declare a record that can hold multiple rows of a table.

  B. The attributes of fields in the record with the %ROWTYPE attribute can be modified manually.

  C. The attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable.

  D. It ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered.

  Correct Answer: CD

• Question 45:

  You want to store values of different data types in a PL/SQL block and store one record at a time for processing the information.

  Which type of composite data type would you choose to fulfill the requirement?

  A. VARRAYS

  B. Nested table

  C. PL/SQL records

  D. Associative arrays

  Correct Answer: C

• Question 46:

  Which type of exceptions is qualified as nonpredefined Oracle server errors?

  A. the exceptions that are explicitly raised by the program and can be caught by the exception handler

  B. the exceptions that are raised implicitly by the Oracle server and can be caught by the exception handler

  C. an exception that the developer determines as abnormal, are in the declarative section and raised explicitly

  D. an exception that is raised automatically when the PL/SQL program violates a database rule or exceeds a system-dependent limit

  Correct Answer: B

• Question 47:

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

  

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

  Correct Answer: B

• Question 48:

  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 PL/SQL 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.

  Correct Answer: BC

• Question 49:

  View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.

  EMPLOYEES

  

  DEPARTMENTS

  

  EMP_BY_DEPT

  

  Examine the following code:

  

  What is the outcome on execution of the above code?

  A. It executes successfully but the output statements show different values.

  B. It executes successfully and both output statements show the same values.

  C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT.

  D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL

  Correct Answer: A

• Question 50:

  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.

  Correct Answer: CD