1Z0-144 Online Practice Questions and Answers

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.

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 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.

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.

Identify situations in which the DBMS_SQL package is the only applicable method of processing dynamic SQL. (Choose two.)

A. When a query returns multiple rows

B. When a column name in a where clause is unknown at compile time

C. When the number of columns selected in a query is not known until run time

D. When a table needs to be created based on an existing table structure at run time

E. When privileges need to be granted to a new user to access an existing schema at run time

Examine the following block of code: Which two statements are correct about the code above? (Choose two.)

A. The function goes through only the parse and executes phases.

B. The function goes through the parse, bind, and execute phases.

C. The function goes through the parse, bind, execute, and fetch phases.

D. All the processing phases for the function are performed only at run time.

E. Only the EXECUTE IMMEDIATE statement inside the function is parsed at run time.

Identify the scenario in which you would use the CURRENT OF clause for an UPDATE or DELETE statement to rows fetched from a cursor.

A. when you want to lock the rows fetched by the cursor

B. when you want to update or delete the result set without affecting the rows in the table

C. when you want the database not to wait if the requested rows are locked by another user

D. when you want to ensure that the current rows fetched by the cursor are updated or deleted

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

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.

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.