1Z0-144 Online Practice Questions and Answers

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

CUST_ID and CUST_CREDIT_LIMIT are existing columns in the CUSTOMER table. Examine the following trigger code:

Which statement is true about the above trigger?

A. It gives an error on compilation because it should be a statement-level trigger.

B. It compiles and fires successfully when the credit limit is updated in the customer table.

C. It gives an error on compilation because of the commit command in the trigger code.

D. It compiles successfully, but gives an error when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.

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,

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.

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.

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.

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

View Exhibit 2 and examine the code created by the user SCOTT:

SCOTT grants the necessary privileges to green to access the EMP table and execute the package.

Examine the following sequence of activities:

SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command.

SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command.

green starts a session while SCOTT's session is running and issues THE SQL>EXEC CURS_PKG.NEXT

command.

SCOTT issues the SQI>>EXEC SCOTT.CURS_PKG.NEXT command.

The EMP table contains sequential EMPNOS from 100 through 108.

Which statement correctly describes the output?

A. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows an error.

B. SCOTT's session shows the EMPNO 100, GREEN'S session shows EMPNO 100, and SCOTT's session shows the EMPNO 101.

C. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows the second EMPNO 101.

D. SCOTT's session shows the EMPNO 100, GREEN'S session shows EMPNO 101, and SCOTT's session shows the second EMPNO 102.

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.

Which two statements are true about the PL/SQL initialization parameters? (Choose two.)

A. To use native code compilation, PLSQL_OPTIMIZE_LEVEL should be set to a value less than or equal to l.

B. The default value of 2 for PLSQL_OPTIMIZE_LEVEL allows the compiler to rearrange code for better performance.

C. Setting PLSQL_CODE_TYPE to NATIVE provides the greatest performance gains only for computation-intensive procedural operations.

D. Changing the value of the PLSQL_CODE_TYPE parameter affects all the PL/SQL library units that have already been compiled.

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.

Examine the following partial declare section from a block of PL/SQL code

Which line(s) in the above code are NOT valid? (Choose two.)

A. line 2

B. line 3

C. line 4

D. line 5

Examine the following code: The above code generates an error on execution.

What must you do to ensure that the code executes successfully?

A. Use the TO_DATE function in line 2.

B. Use the TO_DATE function in line 7.

C. Use the TO_NUMBER function in line 6.

D. Use both the TO_DATE function in line 2 and the TO_NUMBER function in line 6.

Examine the following package specification:

Which statement is true?

A. g_comm has a value of 15 at 9:06 AM only for Jones.

B. g_comm has a value of 10 at 9:03 AM for both Jones and Smith.

C. g_comm has a value of 15 at 9:03 AM for both Jones and Smith.

D. g_comm has a value of 20 at 9:06 AM for both Jones and Smith.

You create a procedure to handle the processing of bank current accounts which rolls back payment transactions if the overdraft limit is exceeded.

The procedure should return an "error" condition to the caller in a manner consistent with other Oracle server errors.

Which construct should be used to handle this requirement?

A. The SQLERRM function

B. The PRAGMA EXCEPTION_INIT function

C. The RAISE_APPLICATION_ERROR procedure

D. A user-defined exception used with a RAISE statement

Examine the following DECLARE section of PL/SQL block:

Which line in the above declarations would generate an error?

A. Line 2

B. Line 3

C. Line 4

D. Line 5

E. Line 6

View the Exhibit and examine the package code created by SCOTT. The execute privilege on this package is granted to GREEN.

Examine the following sequence of commands issued by SCOTT:

What is the outcome?

A. SCOTT'S session displays 5, and then 0, GREEN'S session displays 0.

B. SCOTT'S session displays 5, and then 0, GREEN'S session displays 5.

C. SCOTT'S session displays 5, and then 5, again, GREEN'S session displays 0.

D. SCOTT'S session displays 5, and then 5, again; GREEN'S session displays 5.