Q1. Consider the following scenario:
Local procedure a calls remote procedure B
Procedure A 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.
Answer: D
Q2. 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.
Q3. Which system events can be used to create triggers that fire both at database and schema levels? (Choose all that apply)
A. AFTER LOGON
B. AFTER STARTUP
C. BEFORE SHUTDOWN
D. AFTER SERVERERROR
Answer: A,D
Explanation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS2064
Q4. View the Exhibit and examine the structure of the EMP table.
Which stages are performed when the above block is executed? (Choose all that apply)
A. Bind
B. Parse
C. Fetch
D. Execute
Answer: B,C,D
Q5. Identify two features of obfuscation. (Choose two.)
A. The Import and Export utilities accept wrapped files.
B. SQL' Plus cannot process the obfuscated source files.
C. Only the wrap utility can obfuscate multiple programs at a time.
D. Both the DBMS_DDL package and the Wrap utility can obfuscate multiple programs at a time.
E. The source code is visible only through the DBA_SOURCE view and not through the USER_SOURCE or ALL_SOURCE View
Answer: A,C
Q6. 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.
Answer: A,C
Q7. View the Exhibit and examine the structure of the customer table.
You need to create a trigger to ensure that customers in category “A” and “B” have a credit
limit of more than 8000.
Examine the following trigger.
Which statement is correct about the outcome of this trigger?
A. It compiles successfully and fires whenever the specified condition is met.
B. It compiles successfully but does not fire even when the condition is met
C. It gives an error on compilation because the new qualifier is prefixed with a colon.
D. It gives an error on compilation because the new qualifier can be used only in row-level triggers.
Answer: A
Q8. Which three statements are true about anonymous blocks and subprograms? (Choose three.)
A. Only subprograms can be parameterized.
B. Only subprograms are persistent database objects.
C. Both anonymous blocks and subprograms can be parameterized.
D. Both anonymous blocks and subprograms are persistent database objects
E. Only subprograms can return values that persist after the execution of the subprogram.
F. Both anonymous blocks and subprograms can return values that persist In SQL*Plus variables after their execution.
Answer: B,E,F
Q9. Examine the following partial code:
Which statement is correct about the unnamed block of code at the end of a package body?
A. It generates an error because all the blocks of code in a package body must be named.
B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specification.
C. It acts as a package initialization block that executes once, when the package is first invoked within the user session.
D. It acts as a package initialization block that executes each time a package subprogram is invoked within the user session and refreshes the initialized variable value.
Answer: C
Q10. View Exhibit1 and examine the structure of the EMP table.
View Exhibit2 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.
Answer: C