OCP CERTIFICATION

PL\SQL

QUESTION 1/61: Which statement can be used in Procedure Builder to modify procedures

in a client-side PL/SQL library? Top of Form 1

A. .open library lib_name.
B. Alter procedure proc_name compile;
C. .attach library lib_name.
D. .open file lib_name.
Bottom of Form 1

ANSWER: A: To connect a library to Procedure Builder in order to

edit source code, the .open library command should be used.

QUESTION 2/61: When developing a PL/SQL library, the developer defines variable NUM2
as IN OUT in the ADD_NUMS( ) function. Which of the following statements may happen as a

result of this? Top of Form 1

A. The add_nums( ) function will not compile.
B. Code running after add_nums( ) that uses the NUM2 variable may behave
unpredictably because NUM2's value was changed in add_nums( ).

C. The add_nums( ) function will return errors to the user when run.

D. The add_nums( ) function will cause a memory leak.

Bottom of Form 1

ANSWER: B: When executed, the add_nums( ) procedure returns a

modified NUM2 variable.

QUESTION 3/61: Which of the following lines in the PL/SQL source code will return an

error? Top of Form 1

A. create procedure estatus (
 B. eid NUMBER, statchar CHAR ) is begin
C. select status into statchar from EMP where EMPID = eid; end;

D. There are no errors in this code block

Bottom of Form 1

ANSWER: C: You cannot assign a value to a parameter variable if

that variable has not explicitly been set as an in out or out variable.

QUESTION 4/61: The developer attempts to execute client-side procedure REMOVE_EMP( )

in library EMP_ACTIONS, which results in Oracle saying the REMOVE_EMP variable must be

declared. The appropriate way to correct this error is to Top of Form 1

A. Reestablish connection to the Oracle database

B. Open the procedure using the Stored Program Unit Editor module

C. Execute .attach library EMP_ACTIONS from the PL/SQL Interpreter module

D. Execute .open file REMOVE_EMP from the PL/SQL Interpreter module

Bottom of Form 1

ANSWER: C: When you attempt to execute a procedure in a library

that is not currently attached to the Object Navigator, Procedure Builder returns an error
stating that it expected the name of the procedure to be declared in some way.

QUESTION 5/61: The RAISE_APPLICATION_ERROR( ) procedure defines errors returned in

which of the following numeric ranges? Top of Form 1

A. -00000 and -99999

B. -01200 and -01299
C. -00030 and -00039
D. -20000 and -20999
Bottom of Form 1

ANSWER: D: The range of accepted values for use in

raise_application_error( ) should be between -20000 and -20999.
QUESTION 6/61: The @ command in SQL*Plus will do which of the following with a named

PL/SQL block stored in a flat file? Top of Form 1

A. Load the PL/SQL code into its processing buffer only.

B. Load PL/SQL code into its processing buffer and compile the code against the
database only.
C. Load PL/SQL code into its processing buffer and compile the code on the client side
only.
D. Load, compile, and run PL/SQL code against the Oracle database.

Bottom of Form 1

ANSWER: B: Since the question said the block was named. Thus,
SQL*Plus will simply load and compile, but not execute, the named PL/SQL block in the flat
file given.

QUESTION 7/61: Which two commands are available in SQL*Plus for editing PL/SQL

blocks? Top of Form 1

A. Attach
B. Change
C. Edit
D. Load
Bottom of Form 1

ANSWER: B, C: The change command is sometimes abbreviated c,

and the edit command is sometimes abbreviated ed.

QUESTION 8/61: Which two of the following Procedure Builder modules are not usable
unless you are connected to the Oracle database? (Choose two) Top of Form 1

A. Stored Program Unit Editor

B. Database Trigger Editor
C. Program Unit Editor
D. Object Navigator
Bottom of Form 1
 ANSWER: A, B: Since the Stored Program Unit Editor and Database
Trigger Editor modify server-side objects like stored procedures and triggers, you must be
connected to the Oracle database to use them.

QUESTION 9/61: In which areas of the PL/SQL block must code be placed in order to

handle Oracle-defined exceptions? Top of Form 1

A. Declaration section only
B. Declaration and executable sections only
C. Exception handler only
D. Declaration, executable, and exception handler sections

Bottom of Form 1

ANSWER: C: The only code required for Oracle-defined exceptions on

the part of the developer is an exception handler to prevent unhanded exceptions returning
errors to the caller.

QUESTION 10/61: A procedure declares a user-defined exception but does not raise it
explicitly. Which of the following statements is true about this function? Top of Form 1

A. The user-defined exception will never be raised.

B. The user-defined exception will be handled by a WHEN OTHERS exception handler.

C. The procedure will fail on compile.

D. The user-defined exception is defined incorrectly.

Bottom of Form 1

ANSWER: A: Oracle will never raise a user-defined exception unless

it is a pragma exception, meaning the code must explicitly raise the exception.

QUESTION 11/61: In which areas of a PL/SQL block must the developer place code for

user-defined exceptions? (Choose three) Top of Form 1

A. Command line PL/SQL block call

B. Variable declaration section
 C. Executable section
D. Exception handler
Bottom of Form 1

ANSWER: B, C, D: You should never have to code anything to handle

an exception from the command line; it all should happen within the declaration, execution,
and exception sections of the block.

QUESTION 12/61: An Oracle-defined exception is raised Top of Form 1

A. By the PL/SQL raise statement

B. In the PL/SQL exception handler
C. Automatically by Oracle
D. By the user
Bottom of Form 1

ANSWER: C: Remember, Oracle always raises its own exceptions.

QUESTION 13/61: The developer is debugging server-side PL/SQL from SQL*Plus. Text
output has been embedded into the procedure, but output still is not appearing. The most

appropriate fix is to Top of Form 1

A. Execute set serveroutput on
B. Recompile the procedure
C. Use debug triggers
D. Shut down and restart the computer
Bottom of Form 1

ANSWER: A: If calls to DBMS_OUTPUT are present, the developer

can turn the output producing functionality on and off by using the set serveroutput on
statement from the SQL prompt in SQL*Plus.
QUESTION 14/61: With which two Oracle-supplied procedures can you emulate the

functionality of the PL/SQL Interpreter module? Top of Form 1

A. TOOL_RES.rfread( )
B. DEBUG.interpret( )
C. TOOL_RES.rf_handle( )
D. DEBUG.break( )
Bottom of Form 1

ANSWER: B, D: Both interpret( ) and break( ) allow you to emulate

interactive PL/SQL and the functionality of breakpoints.

QUESTION 15/61: Which Oracle-supplied package's procedures are called internally from

Oracle? Top of Form 1

A. TOOL_RES
B. OLE2
C. STPROC
D. DEBUG
Bottom of Form 1

ANSWER: C: Only the STPROC package contains procedures and

functions called exclusively within Procedure Builder or Oracle.

QUESTION 16/61: When stepping through PL/SQL execution, which button terminates the

run time and returns the interpreter to its prerun state? Top of Form 1

A. Compile
B. Go
C. Step Through
D. Reset
Bottom of Form 1
 ANSWER: D: The Reset button is used to reset the PL/SQL
Interpreter to the state just prior to PL/SQL code execution.

QUESTION 17/61: On compiling a debug trigger, the developer receives an error saying
"identifier must be declared." Which three of the following are potential causes for this error?

Top of Form 1
A. Datatype mismatch between intended variable and function used

B. Variable to be obtained not enclosed properly

C. Function called without reference to package name

D. Variable whose value to be obtained not present in the package

Bottom of Form 1

ANSWER: A: The only situation where you don't receive any errors
on compilation is when the datatype expected in the get( ) call doesn't match the datatype
for the variable whose value is being obtained

QUESTION 18/61: The runtime error stack is found in which PL/SQL program module? Top

of Form 1
A. Stored Program Unit Editor
B. Database Trigger Editor
C. PL/SQL Interpreter
D. Object Navigator
Bottom of Form 1

ANSWER: D: Object Navigator contains drill-downs to help you locate

any attached or open client- or server-side PL/SQL program unit available to Procedure
Builder.

QUESTION 19/61: The developer is using SQL*Plus for debugging. What data dictionary
view contains information about compile-time errors on PL/SQL programs? Top of Form 1

A. USER_ERRORS
 B. USER_SOURCE
C. SOURCE#
D. SYS
Bottom of Form 1

ANSWER: A: USER_ERRORS contains errors produced by the user in

the most recent PL/SQL code compilation effort.

QUESTION 20/61: The developer wants to modify the value of a PL/SQL variable from
within a debug trigger. Which procedure or function is most appropriate? Top of Form 1

A. DEBUG.interpret( )
B. DEBUG.setc( )
C. ORA_PROF.create_timer( )
D. TEXT_IO.put_line( )
Bottom of Form 1

ANSWER: B: The setc( ) function of the DEBUG package is used to

modify values in variables from within a debug trigger.

QUESTION 21/61: The developer needs to design an application that uses external
procedures written in C. The Oracle-supplied package most appropriate for use in this

situation is Top of Form 1

A. TOOL_ERR
B. TEXT_IO
C. ORA_FFI
D. OLE2
Bottom of Form 1

ANSWER: C: The ORA_FFI package contains procedures and

functions that assist with interfacing between PL/SQL programs and those written in C.

QUESTION 22/61: Type declarations for variables referenced in debug triggers are

generally found in Top of Form 1

A. The runtime procedure call
B. PL/SQL programs
 C. Debug triggers
D. Database triggers
Bottom of Form 1

ANSWER: B: A variable used in PL/SQL programs is declared in that

PL/SQL program, with the exception of when global variables are used in packages. Answers
in Depth...

QUESTION 23/61: A debug trigger has been coded to fire on every breakpoint within a
certain PL/SQL program. The breakpoint will be entered when in relation to the code line on

which the breakpoint appears? Top of Form 1

A. After
B. Before
C. During
D. Never, debug triggers fire only when disabled

Bottom of Form 1

ANSWER: B: Breakpoints are entered just before the statement on

which the breakpoint appears in the PL/SQL Interpreter is executed, not after or during.

QUESTION 24/61: The GET( ) and SET( ) functions are part of which package? Top of Form 1

A. TOOL_ENV
B. ORA_FFI
C. DEBUG
D. LIST
Bottom of Form 1

ANSWER: C: As two functions commonly employed for debug

triggers, it should be obvious that these functions are part of the DEBUG package.

QUESTION 25/61: Signatures are in use on the Oracle database. Function F1( ) is
compiled containing an INSERT statement on table T1. A NOT NULL constraint is then added

to T1. What is the status of function F1( )? Top of Form 1

 A. Valid, because the signature on T1 did not change

B. Invalid, because timestamps are always used for enforcing object dependencies

C. Valid, because table T1 was not dropped

D. Invalid, because the constraint added altered the specification on function f1( )

Bottom of Form 1

ANSWER: B: Important to remember is that tables are marked for

validity with timestamps, not signatures.

QUESTION 26/61: Which of the following cannot be altered about a function without

invalidating its signature? Top of Form 1

A. Parameter names
B. Default values
C. Direction from implicit to explicitly defined in

D. Return datatype from CHAR to RAW

Bottom of Form 1

ANSWER: D: As with parameter datatypes, the major change in

return datatype for this function renders the signature invalid.

QUESTION 27/61: Which two of the following methods specify PL/SQL signatures in effect

for all sessions in the Oracle database? Top of Form 1

A. Alter system set REMOTE_DEPENDENCIES_MODE=TRUE

B. Alter system set REMOTE_DEPENDENCIES_MODE=signature

C. Alter session set REMOTE_DEPENDENCIES_MODE=timestamp

D. Alter REMOTE_DEPENDENCIES_MODE in init.ora and set it to signature

Bottom of Form 1

ANSWER: B, D: Setting REMOTE_DEPENDENCIES_MODE to signature

can be done either in the init.ora file followed by restarting the database or by issuing the
alter system statement to change the value set for this parameter.
QUESTION 28/61: The developer wants to obtain a source code listing from Oracle. Which

of the following statements is most appropriate for the task? Top of Form 1

A. select * from USER_SOURCE where NAME = 'proc_name'

B. select TEXT from USER_ERRORS where NAME = 'proc_name' order by LINE

C. select LINE from USER_SOURCE where NAME = 'proc_name'

D. select TEXT from USER_SOURCE where NAME = 'proc_name' order by LINE

Bottom of Form 1

ANSWER: D: The TEXT column of USER_SOURCE contains the actual

source code for the PL/SQL block, and ordering by LINE will prevent any chance of source
code being returned in incorrect order.

QUESTION 29/61: Function FOO( ) references table FOOBAR via an invalid database link.
If FOOBAR has not been modified, what should the status of FOO( ) be? Top of Form 1

A. Valid, because the underlying table hasn't been modified

B. Invalid, because the database link is invalid

C. Valid, because the signature method is being used to enforce procedure

dependency
D. Invalid, because Oracle does not support object dependencies through database
links
Bottom of Form 1

ANSWER: B: Since the database link is an underlying database

object, it must be corrected in order for foo( ) to be recompiled.

QUESTION 30/61: Function CALORIE_COUNT( ) is compiled on the Oracle database using

the signature method of procedure dependency enforcement. In which three of the following
situations will a signature be invalidated? Top of Form 1

A. When a parameter name is changed

B. When a new parameter is added
C. When calorie_count( ) is part of a package and a new procedure is added to the
package before of it
D. When an in parameter becomes in out
Bottom of Form 1

ANSWER: B, C, D: Only choice A in this question offers an option

that will leave a PL/SQL signature valid if executed.

QUESTION 31/61: Procedure PROC1( ) calls function FUNC1( ), which in turn updates data
on the TABLE1 table. The developer drops TABLE1 accidentally. What must be done to rectify

the problem? Top of Form 1

A. Re-create TABLE1, then recompile proc1( ), then recompile func1( )

B. Re-create TABLE1, then recompile func1( ), then recompile proc1( )

C. Recompile func1( ), then recreate TABLE1, then recompile proc1( )

D. Recompile proc1( ), then recompile func1( ), then recreate TABLE1

Bottom of Form 1

ANSWER: B: When dependencies have caused objects to become

invalid, the right way to set things straight is to handle the underlying objects first, followed
by those PL/SQL programs with object dependencies, followed by PL/SQL programs with
procedure dependencies.

QUESTION 32/61: What is the effect of revoking SELECT access on table EMPLOYEES from
user JOYCE after granting her EXECUTE privilege on SELECT_EMP( ), a procedure containing

SELECT * FROM EMPLOYEES? Top of Form 1

A. Her execution of select_emp( ) will work properly.

B. Her execution of select_emp( ) will work, but the select will encounter "permission
denied" errors.
 C. Her execute privilege on select_emp( ) will be revoked.

D. Oracle will disconnect when select_emp( ) is executed.

Bottom of Form 1

ANSWER: A: The only privilege user JOYCE needs in this situation is

execute on select_emp( ).

QUESTION 33/61: The best source of timestamp information in the Oracle database is

which of the following items? Top of Form 1

A. USER_SOURCE dictionary view
B. Source code for the PL/SQL program
C. DBA_ERRORS dictionary view
D. ALL_OBJECTS dictionary view
Bottom of Form 1

ANSWER: D: DBA_OBJECTS and USER_OBJECTS both contain

timestamp and status information for all database objects in the database and all objects
owned by the current user, respectively.

QUESTION 34/61: Procedure P1( ) calls function F1( ), which contains a SELECT statement
on table T1. All objects are stored on one database. Table T1 is dropped. What happens to

procedure P1? Top of Form 1

A. Procedure p1( ) is invalidated immediately
B. Procedure p1( ) is invalidated the next time it is called

C. Procedure p1( ) stays valid as long as function f1( ) is never called

D. Procedure p1( ) is dropped along with table T1

Bottom of Form 1

ANSWER: A: Oracle wastes no time invalidating local PL/SQL code

when dependencies are violated.
QUESTION 35/61: The DBA_ERRORS dictionary view contains which of the following

information? Top of Form 1

A. Compilation and runtime errors for all PL/SQL on the database

B. Compilation errors for user-owned PL/SQL code only

C. Compilation errors for all PL/SQL on the database

D. Runtime errors for user-owned PL/SQL code only

Bottom of Form 1

ANSWER: C: All compilation errors for all PL/SQL currently invalid in

the database can be found in DBA_ERRORS.

QUESTION 36/61: The developer wants to change a parameter's datatype from ROWID to
something else, but doesn't want to invalidate the signature. Which of the following

datatypes are acceptable alternatives? Top of Form 1

A. VARCHAR
B. DATE
C. CHARACTER
D. RAW
Bottom of Form 1

ANSWER: A: Signatures are not invalidated by minor datatype

changes.

QUESTION 37/61: UPDATE_PTABLE( ) performs some updates on an underlying table

called PTABLE. According to the signature method of procedure dependency enforcement, if a

column is added to PTABLE, what happens to UPDATE_PTABLE( )? Top of Form 1

 A. PTABLE is dropped
B. Nothing, the specification of PTABLE did not change

C. Procedure update_ptable( ) is invalidated

D. Nothing, the specification of update_ptable( ) did not change

Bottom of Form 1

ANSWER: C: The signature method of procedure dependency

enforcement does not apply to database objects, which are marked only by their timestamp.

QUESTION 38/61: The developer wants her PL/SQL program unit to issue DDL to create
temporary tables to hold data. Which of the following methods should she use to do it? Top of

Form 1
A. Issue the create table statements directly from the program unit

B. Use DBMS_SESSION to alter the environment such that the PL/SQL program unit
can create tables
C. Use the DBMS_OUTPUT and UTL_FILE packages to issue the create table
statement from the operating system
D. Use DBMS_SQL to generate the create table statement on the fly

Bottom of Form 1

ANSWER: D: You must use DBMS_SQL to generate the create table

statement and execute it from PL/SQL.

QUESTION 39/61: Which of the following statements are true of a private program unit in

a package? Top of Form 1

A. The package specification will contain comments about a private program unit's
general usage.
B. Only the package body will contain references to the private program unit.

C. Any package can reference the private program unit.

D. Only the package specification will contain reference to the private program unit.

Bottom of Form 1

ANSWER: B: A private program unit is one that appears only in a

package body.
QUESTION 40/61: The use of which of the following Oracle server-supplied program units
may cause problems with instantiating a package after recompilation? Top of Form 1

A. DBMS_ALERT.waitany( )
B. DBMS_PIPE.create_pipe( )
C. DBMS_SESSION.describe_procedure( )
D. DBMS_SHARED_POOL.keep( )
Bottom of Form 1

ANSWER: D: The keep( ) procedure in DBMS_SHARED_POOL forces

Oracle to pin the package into memory

QUESTION 41/61: In which of the following scenarios when developing intersession

communication in an application is the use of a private pipe appropriate? Top of Form 1

A. When any user should be able to access information on the pipe

B. When developers and users should be able to access information on the pipe

C. When only the owner of the pipe should have access to information on that pipe

D. When the pipe is being sent to a file

Bottom of Form 1

ANSWER: C: A private pipe is readable by only the owner of the pipe

and privileged users like the DBA. Answers in Depth...

QUESTION 42/61: Which of the following are good uses of comments in packages?

(Choose two) Top of Form 1

A. General descriptions of program usage in package body

B. General description of program usage in package specification

C. Detailed description of program logic in package specification

D. Detailed description of program logic in package body

Bottom of Form 1
 ANSWER: B, C: Good comment usage is demonstrated by Oracle
server-supplied packages, where comments on general program unit usage, parameter
passing options, and other general items on using the package appear in the package
specification.

QUESTION 43/61: The term that best describes a situation where several procedures in a
package all have the same name but accept parameters of different datatypes is: Top of Form 1

A. Instantiating
B. Overloading
C. Datatyping
D. Compilation error
Bottom of Form 1

ANSWER: B: The text of the question describes overloading.

QUESTION 44/61: Which Oracle server-supplied program unit may cause locking issues if

a COMMIT is not issued before its use? Top of Form 1

A. DBMS_ALERT.waitany( )
B. DBMS_PIPE.create_pipe( )
C. DBMS_SESSION.describe_procedure( )
D. DBMS_SHARED_POOL.keep( )
Bottom of Form 1

ANSWER: A: The waitany( ) procedure in DBMS_ALERT forces the

session to wait until it receives a signal from any of the alerts it is registered to receive.

QUESTION 45/61: Which of the following situations will cause a package specification to

invalidate? Top of Form 1

A. Syntax error in the specification
B. Referenced procedure in another package invalidates
 C. Program unit specification in package body doesn't match

D. Package body fails on compilation

Bottom of Form 1

ANSWER: A: Package specifications rarely invalidate because of

another package specification or body changing on the database. Answers in Depth...

QUESTION 46/61: The user of a program unit has received the following error: "ORA-
04068: existing package state has been discarded." The next step the developer should take

is: Top of Form 1

A. Rerun the program unit just attempted
B. Recompile the package containing the program unit

C. Drop the package containing the program unit

D. Recompile the program unit individually

Bottom of Form 1

ANSWER: A: The program unit must be reinstantiated after

recompilation in order to be used.

QUESTION 47/61: After sending a message across a pipe, which of the following
procedures would a developer use to retrieve the message from the pipe? Top of Form 1

A. pack_message( )
B. send_message( )
C. receive_message( )
D. unpack_message( )
Bottom of Form 1

ANSWER: C: Reading the message off the pipe into your local buffer
is done with receive_message( ) while unpack_message( ) removes if from the local buffer,
allowing you to work with it.
QUESTION 48/61: Which two Oracle server-supplied packages approximate the use of the

TEXT_IO package supplied with Procedure Builder? Top of Form 1

A. DBMS_SESSION and DBMS_PIPE

B. DBMS_OUTPUT and UTL_FILE
C. DBMS_DDL and DBMS_UTILITY
D. STANDARD and DBMS_LOCK
Bottom of Form 1

ANSWER: B: On the Oracle database, DBMS_OUTPUT allows you to

write your output to the screen, while UTL_FILE allows you to write output to a file.

QUESTION 49/61: The datatype in a program unit specification in the package body does
not match its counterpart in the specification. Which of the following events best describes

what will happen? Top of Form 1

A. The package body will fail on compile because the program unit specifications
don't match.
B. The specification will fail on compile because the program unit specifications don't
match.
C. Both the package body and package specification will compile fine.

D. The package body will compile fine, but the package specification will fail.

Bottom of Form 1

ANSWER: A: The package body must conform to the package

specification on matters of program unit specification.

QUESTION 50/61: The developer finds a logic error in a program unit called by other
programs in several different packages. The developer corrects the error and recompiles the
package. Assuming no calls to the referenced program were made, what are the effects of

her action? Top of Form 1

 A. The specification to the recompiled package body invalidates

B. The bodies of other packages were invalidated

C. The specification to other packages were invalidated

D. Nothing, the referenced package specification did not invalidate

Bottom of Form 1

ANSWER: D: There is little you can do to invalidate a package

specification other than compile it with syntax errors.

QUESTION 51/61: The developer of an application wants the application to acquire table
locks in order to execute its processing. Which of the following methods for acquiring that

table lock are appropriate? Top of Form 1

A. Issuing update statements with where clauses

B. Issuing select for update statements where the select statement has a where
clause
C. Issuing calls to program units in DBMS_LOCK

D. Issuing calls to program units in DBMS_TRANSACTION

Bottom of Form 1

ANSWER: C: Table-level locks may only be acquired by an application

when the DBMS_LOCK package is used.

QUESTION 52/61: To achieve better performance on row triggers, you should: Top of Form 1

A. Set the trigger to process after the triggering statement

B. Set the trigger to process before the trigging statement

C. Set the trigger to process in parallel with the triggering statement

D. Set the trigger to update the triggering statement

Bottom of Form 1
 ANSWER: A: Processing the trigger execution after the triggering
statement allows Oracle to perform the work necessary with fewer logical reads of data,
thereby reducing overall processing time for the trigger and the triggering statement.
Answers in Depth...

QUESTION 53/61: The developer issues the following statement: CREATE OR REPLACE
TRIGGER soccer_fans_snacks_02 BEFORE DELETE ON SOCCER_FANS FOR EACH ROW BEGIN
DELETE FROM soccer_fans_snacks WHERE fan_id = :old.fan_id; END;. Which of the following

statements best describes the trigger created? Top of Form 1

A. An update trigger that fires before Oracle processes the triggering statement

B. An insert trigger that fires after Oracle processes the triggering statement

C. An insert trigger that fires after Oracle processes the triggering statement

D. A delete trigger that fires before Oracle processes the triggering statement

Bottom of Form 1

ANSWER: D: The key clause in this case is before delete on

SOCCER_FANS.

QUESTION 54/61: The developer issues the following statement: CREATE OR REPLACE
TRIGGER soccer_fans_snacks_02 BEFORE DELETE ON SOCCER_FANS BEGIN DELETE FROM
soccer_fans_snacks WHERE fan_id = :old.fan_id; END;. Why will trigger creation fail? Top of

Form 1
A. The row trigger does not properly reference the old value in FAN_ID.

B. The statement trigger should have been defined as a row trigger.

C. The statement trigger fires after the delete statement is processed.

D. The row trigger does not properly define the associated table.

Bottom of Form 1

ANSWER: B: A statement trigger cannot reference old and new

column values in the affected rows

QUESTION 55/61: You define a trigger that contains the clause AFTER UPDATE OR DELETE
ON SOCCER_FAN_SNACKS. Which two of the following keywords may be useful in your
trigger source code to distinguish what should run, and when? Top of Form 1

A. inserting
B. updating
C. deleting
D. truncating
Bottom of Form 1

ANSWER: B, C: Since the trigger will fire for two triggering

statements, update and delete, it stands to reason that you will want to place in your trigger
an if-then-else statement that determines which block of code to run in the case of an update
or delete, respectively.

QUESTION 56/61: Table SOCCER_FAN_SEAT contains two columns: FAN and SEAT_NUM.
A trigger is created in this table, whose triggering statement definition is AFTER UPDATE OF
SEAT_NUM ON SOCCER_FAN_SEAT. You issue an UPDATE statement that changes column
FAN only. Which of the following best describes what happens next? Top of Form 1

A. The trigger fires successfully.

B. The trigger fires unsuccessfully.
C. Nothing, the SEAT_NUM column was not updated.

D. The trigger invalidates.

Bottom of Form 1

ANSWER: C: Since the trigger contains the clause after update of

SEAT_NUM on SOCCER_FAN_SEAT, the trigger will not fire if an update statement affects the
FAN column only.

QUESTION 57/61: Which of the following trigger types will be impacted by constraining

factors brought on by mutating tables? Top of Form 1

A. Row triggers only

B. Statement triggers only
C. Both row and statement triggers
D. Neither row or statement triggers
Bottom of Form 1
 ANSWER: A: Since you can refer to old and new column values in
rows affected by the triggering statement of your row trigger, mutating a table has the
potential to adversely impact row trigger performance.

QUESTION 58/61: The developer issues the following statement: CREATE OR REPLACE
TRIGGER soccer_fans_snacks_02 BEFORE DELETE ON SOCCER_FANS FOR EACH ROW BEGIN
DELETE FROM soccer_fans_snacks WHERE fan_id = :old.fan_id; END;. Which of the following

permissions are required to run this trigger? Top of Form 1

A. execute on SOCCER_FANS
B. execute on SOCCER_FANS_SNACKS_02
C. delete on SOCCER_FANS_SNACKS_02
D. delete on SOCCER_FANS
Bottom of Form 1

ANSWER: D: No other permission is required to execute a trigger

than permission to run the triggering statement.

QUESTION 59/61: The SOCCER_FANS table has a trigger associated with it that inserts
data into SOCCER_FANS_SNACKS whenever rows are inserted into SOCCER_FANS. A foreign
key constraint exists between FAN_ID on SOCCER_FANS and SOCCER_FANS_SNACKS. What

happens when the trigger fires? Top of Form 1

A. The trigger processes normally.
B. The trigger invalidates.
C. The trigger execution fails because of a mutating or constraining table.

D. The trigger execution succeeds because the trigger is a statement trigger.

Bottom of Form 1

ANSWER: C: In order to resolve the problem, you must drop the

foreign key constraint.
QUESTION 60/61: To find information about trigger status, which of the following views

are appropriate? Top of Form 1

A. ALL_TRIGGERS
B. ALL_OBJECTS
C. ALL_TRIGGER_COLS
D. ALL_SOURCE
Bottom of Form 1

ANSWER: A: The ALL_TRIGGERS view contains status information for

triggers, along with source code, when clause (if any), and most every other component you
define with the trigger.

QUESTION 61/61: The developer issues the following statement: CREATE OR REPLACE
TRIGGER soccer_fans_snacks_02 BEFORE DELETE ON SOCCER_FANS FOR EACH ROW BEGIN
DELETE FROM soccer_fans_snacks WHERE fan_id = :prechange.fan_id; END;. Why does the

trigger fail on creation? Top of Form 1

A. The statement trigger improperly references the changed row data.

B. The row trigger does not define prechange as the referencing keyword for old
column values.
C. Row triggers cannot process before the triggering statement.

D. Statement triggers cannot process before the triggering statement.

Bottom of Form 1

ANSWER: B: In order to use any keyword to reference old or new

column values in rows affected by the triggering statement other than old or new, you must
define the other keyword with the referencing old as old new as new clause.