Oracle Database 11g Program with PLSQL

Here you have the best Oracle 1z0-144 practice exam questions

You have 76 total questions to study from
Each page has 5 questions, making a total of 16 pages
You can navigate through the pages using the buttons at the bottom
This questions were last updated on November 13, 2024

Question 1 of 76

View the Exhibit to examine the PL/SQL code:

DECLARE -

x NUMBER := 5;

y NUMBER := NULL;

BEGIN -

IF x != y THEN yields NULL, not TRUE

DBMS_OUTPUT.PUT_LINE(x != y); not run

ELSIF x = y THEN also yields NULL

DBMS_OUTPUT.PUT_LINE(x = y);

ELSE -

DBMS_OUTPUT.PUT_LINE -

(Cant tell if x and y are equal or not.);

END IF;

END;

SREVROUPUT is on for the session. Which statement is true about the output of the PL/SQL block?

The output is x = y.

It produces an error.

The output is x != y.

The output is Can't tell if x and y are equal or not.

Correct Answer: D

In PL/SQL, when you compare any value with NULL, the result is always NULL, not TRUE or FALSE. Since x is 5 and y is NULL, both the conditions x != y and x = y will yield NULL. The ELSE part of the code will be executed, resulting in the output "Can't tell if x and y are equal or not."

Question 2 of 76

Examine the following command:

SQL>ALTER SESSION -

SET plsql_warnings *

'enable: severe',

'enable: performance',

'ERROR: 05003';

What is the implication of the above command?

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

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

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

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

Correct Answer: B

The command `ALTER SESSION SET plsql_warnings = 'enable: severe', 'enable: performance', 'ERROR: 05003';` configures the session to handle PL/SQL warnings. The 'enable: severe' setting enables warnings for serious issues, 'enable: performance' enables warnings for performance problems, and 'ERROR: 05003' treats the specific warning `PLW-05003` as an error, causing the compilation to fail when this warning occurs. Hence, the command ensures that the compilation fails whenever the `PLW-05003` warning is triggered.

Question 3 of 76

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?

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

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

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

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

Correct Answer: B

When the procedure DELETE_DETAILS is invoked, it first attempts to delete a record from the products table based on the provided prod_id. The commit statement ensures that the deletion is permanent. If the deletion is successful, the procedure finishes without error. However, if an error occurs during the deletion, the exception section is triggered. The exception section captures the error message using the SQLERRM function and passes it to the debugging procedure. The debugging procedure then records the error message into the debug_output table. As a result, the procedure not only executes successfully but also logs any error messages encountered during execution into the debug_output table. Therefore, the correct answer is that the procedure executes successfully and any error messages are recorded in the debug_output table.

Question 4 of 76

Which two tasks should be created as functions instead of as procedures? (Choose two.)

Reference host or bind variables in a PL/SQL block of code

Tasks that compute and return multiple values to the calling environment

Tasks that compute a value that must be returned to the calling environment

Tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into

Correct Answer: B, C

Functions should be created for tasks that compute and return values to the calling environment. This is because functions in PL/SQL are designed to return a value or a record of values. Tasks that compute and return multiple values to the calling environment align with this definition as they can return composite types like records. Similarly, tasks that compute a single value that must be returned to the calling environment also fit this definition. Therefore, 'Tasks that compute and return multiple values to the calling environment' and 'Tasks that compute a value that must be returned to the calling environment' are appropriate for functions.

Question 5 of 76

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

View Exhibit 2 and examine the code.

What would be the outcome when the code is executed?

It executes successfully.

It gives an error because the SAL variable is not visible in the increase function.

It gives an error because the increase function cannot be called from the RAISE_SALARY procedure.

It gives an error because the increase function and the RAISE_SALARY procedure should be declared at the beginning of the declare section before all the

Correct Answer: B

The code will give an error because the SAL variable is not visible in the increase function. In PL/SQL, variables declared in the declarative section must be accessible to be used within functions and procedures. In this case, the SAL variable is declared but not assigned a value before being used in the increase function, causing an error. The function increase tries to use SAL from the DECLARE section but since sal is declared as a separate variable inside of the BEGIN block, it lacks the proper scope visibility.