oracle 1z0-144 practice test
Oracle Database 11g: Program with PL/SQL
Question 1
Examine the following code:
Which statement is true about the execution of the above code?
- A. It executes and displays null.
- B. It executes and the condition returns true.
- C. It executes and control goes to the else statement.
- D. It fails because no value is assigned to the v_myage variable.
Answer:
C
Question 2
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.
Answer:
A
Question 3
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.
Answer:
A
Question 4
View the Exhibit and examine the code and its outcome on execution:
What would be the effect on the two procedures if the value of debug is set to false? (Choose two.)
- A. MY_PROC2 is not recompiled.
- B. MY_PROC1 is recompiled but remains unchanged.
- C. MY_PROC2 is recompiled but remains unchanged.
- D. MY_PROC1 is recompiled without the debugging code.
Answer:
C D
Question 5
Examine the following PL/SQL code;
The execution of the code produces errors. Identify the error in the code.
- A. The open cursor is missing.
- B. The fetch clause is missing.
- C. The exit when condition is missing.
- D. The EMP_NAME and EMP_JOB variables cannot be used in the for clause of the cursor FOR statement.
Answer:
D
Question 6
Which two statements are true about the instead of triggers? (Choose two.)
- A. Delete operations cannot be performed using the instead of triggers.
- B. The instead or triggers must be created to add or modify data through any view.
- C. The instead of triggers can be written only for views, and the before and after timing options are not valid.
- D. The check option for views is not enforced when Insertions or updates to the view are performed by using the instead of triggers.
Answer:
C D
Explanation:
References:
Question 7
Which two statements are true about the handling of internally defined or user-defined PL/SQL exceptions? (Choose two.)
- A. Add exception handlers whenever errors occur.
- B. An exception handler should commit the transaction.
- C. Handle named exceptions whenever possible instead of using when others in exception handlers.
- D. Instead of adding exception handlers to your PL/SQL block, check for errors at every point where they may occur.
Answer:
A C
Explanation:
References:
Question 8
You want to maintain an audit of the date and time when each user of the database logs off.
Examine the following code:
Which two clauses should be used to fill in the blanks and complete the above code? (Choose two.)
- A. ON SCHEMA
- B. ON QRXABASE
- C. AFTER LOGOFF
- D. BEFORE LOGOFF
Answer:
B D
Question 9
Examine the following PL/SQL code:
Which statement is true about the execution of the PL/SQL code?
- A. It executes successfully.
- B. It generates a run-time exception.
- C. It does not execute because of syntax error.
- D. It executes successfully and generates a warning.
Answer:
B
Explanation:
References:
Question 10
View the exhibit and examine the structure of the EMPLOYEE table.
EMPLOYEE_SEQ is an existing sequence.
Examine the following block of code:
Which statement is true about the above block of code?
- A. It consists of two transactions
- B. It consists of a single transaction.
- C. The data is automatically committed after the block execution ends.
- D. It gives an error on execution because sequences cannot be used in anonymous blocks.
Answer:
B
Question 11
Which statements are true about the WHEN OTHERS exception handler? (Choose all that apply.)
- 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.
Answer:
B C
Question 12
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.
Answer:
B
Question 13
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.
Answer:
A
Explanation:
In this case, the debug output will only occur if there is an exception.
Question 14
Which two tasks should be created as functions instead of as procedures? (Choose two.)
- A. Reference host or bind variables in a PL/SQL block of code
- B. Tasks that compute and return multiple values to the calling environment
- C. Tasks that compute a value that must be returned to the calling environment
- D. Tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application
Answer:
C D
Question 15
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
Answer:
B