1Z0-144 Online Practice Questions and Answers

Examine the following command:

SQL>ALTER SESSION SET plsql_warnings * 'enable: severe', 'enable: performance', 'ERROR: 05003';

What is the implication of the above command?

A. It issues a warning whenever ERROR: 05003 occur during compilation.

B. It causes the compilation to fail whenever the warning ERROR.05003 occurs.

C. It issues warnings whenever the code causes an unexpected action or wrong results performance problems.

D. It causes the compilation to fail whenever the code gives wrong results or contains statements that are never executed.

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

Examine the following code: Which statement is true when the procedure DELETE_DETAILS is invoked?

A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table

B. It executes successfully and any error messages get recorded in the DEBUG_OUTPUT table.

C. It gives an error because PRAGMA AUTONOMOUS_TRANSACTION can be used only in packaged procedures.

D. It gives an error because procedures containing PRAGMA AUTONOMOUS_TRANSACTION cannot be called from the exception section.

User SCOTT has been granted CREATE ANY TRIGGER and ALTER ANY TABLE by the DBA. HR is an

existing schema in the database.

SCOTT creates the following trigger:

CREATE OR REPLACE TRIGGER drop_trigger

BEFORE DROP ON hr.SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (-20000, `Cannot drop object');

END;

SCOTT does not grant the EXECUTE privilege on this trigger to any other users.

For which user(s) would this trigger fire by default when they drop an object in the HR schema?

A. Only HR

B. SCOTT and HR

C. Only SCOTT

D. SCOTT, HR, and SYS

Examine the following code that you plan to execute:

What correction should be performed in the above code?

A. The PROC2 procedure code should be defined in the package body.

B. The PROC3 procedure should be declared in the package specification.

C. The PROC3 procedure header should be declared at the beginning of the package body.

D. The variable x must be declared in the package body and removed from the specification,

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

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

Which two statements are true about PL/SQL exception propagation? (Choose two.)

A. The exception reproduces itself in successive enclosing blocks until a handler is found.

B. Exception- can propagate across the remote subprograms that are called through database links.

C. If you declare a local exception in a subblock and a global exception in the outer block, the local declaration overrides the global exception.

D. If you declare a local exception in a subblock and a global exception in the outer block, the global declaration overrides the local exception.

View Exhibit 1 and examine the structure of the EMP and DEPT tables.

View Exhibit 2 and examine the trigger code that is defined on the DEPT table to enforce the UPDATE and DELETE RESTRICT referential actions on the primary key of the DEPT table.

What is the outcome on compilation?

A. It compiles and executes successfully.

B. It gives an error on compilation because it is not a row-level trigger.

C. It gives an error on compilation because the EXCEPTION section is used in the trigger.

D. It compiles successfully but gives an error on execution because it is not a row-level trigger.

Which statements are true about the WHEN OTHERS exception handler? (Choose two.)

A. It can be the first exception handler.

B. It can be the only exception handler for the code.

C. It traps all the exceptions that are not already trapped.

D. You can have multiple OTHERS clauses to trap all the multiple unhandled exceptions.

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.