1.
Examine this procedure:
CREATE PROCEDURE add_theater
IS
BEGIN
INSERT INTO theater
VALUES (35, 'Riverplace Theatre', '1222 River Drive, Austin, Tx.');
END;
Which three statements about this procedure are true? (Choose three.)
A. 
The ADD_THEATER procedure is written in SQL.
B. 
The ADD_THEATER procedure can be shared by multiple programs.
C. 
The ADD_THEATER procedure will be stored in the database as a schema object.
D. 
The ADD_THEATER procedure will execute with the privileges of its owner, by default.
E. 
The ADD_THEATER procedure has three parts: the specification, the body, and the exception handler.
2.
Examine this procedure:
1. CREATE OR REPLACE PROCEDURE remove_department
2. (v_deptno IN NUMBER)
3. IS
4. BEGIN
5. DELETE dept
6. WHERE deptno = v_deptno;
7. EXCEPTION
8. WHEN DEPT_DELETE_EXCEPTION
9. THEN DBMS_OUTPUT.PUT_LINE ('Cannot delete this department.');
10. END;
Which line of this procedure creates an error when compiled?
A. 
B. 
C. 
D. 
E. 
3.
The MODIFY_PAYROLL procedure contains many SQL statements and will be executed from multiple client applications. Where should this procedure be stored?
A. 
B. 
C. 
D. 
Server and client applications
4.
Examine this procedure:
CREATE OR REPLACE PROCEDURE update_employee
(v_emp_id IN NUMBER)
IS
v_comm NUMBER;
PROCEDURE calc_comm
IS
v_total NUMBER;
BEGIN
SELECT SUM(ord.total)
INTO v_total
FROM ord,customer
WHERE ord.custid = customer.custid
AND customer.repid = v_emp_id;
v_comm := v_total * .20;
END calc_comm;
v_percentage NUMBER;
BEGIN
SELECT percentage
INTO v_percentage
FROM daily_figures
WHERE TRUNC(figure_date) = TRUNC(SYSDATE);
IF v_percentage > 33 THEN
calc_comm;
END IF;
END;
Why does this code cause an error when compiled?
A. 
The SUBPROGRAM keyword does not exist.
B. 
CALC_COMM must be invoked using the EXECUTE command.
C. 
CALC_COMM must be declared after all local variable declarations.
D. 
CALC_COMM must be declared before all local variable declarations.
5.
Examine this procedure:
CREATE OR REPLACE PROCEDURE find_seats_sold
(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER)
IS
v_seats_sold gross_receipt.seats_sold%TYPE;
BEGIN
SELECT seats_sold
INTO v_seats_sold
FROM gross_receipt
WHERE movie_id = v_movie_id
AND theater_id = v_theater_id;
END;
Which command will successfully invoke this procedure in SQL*Plus?
A. 
RUN find_seats_sold (v_theater_id => 500);
B. 
EXECUTE find_seats_sold (v_movie_id => 34);
C. 
EXECUTE find_seats_sold (v_theater_id => 500);
D. 
RUN find_seats_sold (v_movie_id => DEFAULT, v_theater_id => 500);
6.
Which statement about declaring arguments for procedures is true?
A. 
Precision must be specified for VARCHAR2 arguments.
B. 
Each declared argument must have a mode specified.
C. 
An IN argument passes a value from a procedure to the calling environment.
D. 
Formal arguments allow you to transfer values to and from the calling environment.
7.
What should be placed after the IS keyword when creating a procedure?
A. 
B. 
C. 
D. 
Global and local variables
8.
You execute this code:
CREATE OR REPLACE PROCEDURE find_seats_sold
(v_movie_id IN NUMBER DEFAULT 34)
IS
v_seats_sold gross_receipt.seats_sold%TYPE;
BEGIN
null;
END;
Which statement is true?
A. 
The statement compiles, and the procedure is created.
B. 
The statement complies with errors because there is no exception handling.
C. 
The statement complies with errors because the body does not contain a SQL statement.
D. 
The statement complies, and the procedure is stored as a data dictionary object in the database.
9.
Which statement about the use of the DEFAULT clause in the declaration of a formal parameter is true?
A. 
IN parameters must be initialized with a default value.
B. 
OUT parameters must be initialized with a default value.
C. 
IN parameters cannot be initialized with a default value.
D. 
IN OUT parameters cannot be initialized with a default value.
10.
When a local procedure that references a remote procedure is compiled, what is recorded in the p-code or object code?
A. 
Both timestamps of the local and remote procedures
B. 
The timestamp of the local procedure compilation only
C. 
The timestamp of the remote procedure compilation only
D. 
No timestamps, unless a reference is made to PRAGMA RESTRICT_REFERENCES
11.
When a change is made to a referenced database object, what can be said about existing dependencies?
A. 
Only direct dependent objects are affected.
B. 
Only indirect dependent objects are affected.
C. 
Only those dependencies that directly reference a database table are affected.
D. 
All direct and indirect dependent objects are affected.
12.
Which two statements about dependent objects are true?
A. 
A procedure that updates a table is a dependent object.
B. 
A procedure that calls another procedure is a dependent object.
C. 
A table that is modified by a trigger is a dependent object.
D. 
A function that is called by another function is a dependent object.
E. 
A view that is queried by a procedure is a dependent object.
13.
Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
v_budget studio.yearly_budget%TYPE;
BEGIN
v_budget :=
[email protected](11);
IF v_budget < 30000000 THEN
[email protected] (11, 300000000);
END IF;
END;
The dependency mode is set to TIMESTAMP.
The local procedure, CALCULATE_BUDGET, was compiled yesterday at 8:00 a.m., after compiling the
GET_BUDGET remote function. The GET_BUDGET remote function was recompiled at 4:00 p.m. today.
What can be said about the subsequent executions of CALCULATE_BUDGET?
A. 
Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
v_budget studio.yearly_budget%TYPE;
BEGIN
v_budget := [email protected](11);
IF v_budget < 30000000 THEN
[email protected] (11, 300000000);
END IF;
END;
The dependency mode is set to TIMESTAMP.
The local procedure, CALCULATE_BUDGET, was compiled yesterday at 8:00 a.m., after compiling the
GET_BUDGET remote function. The GET_BUDGET remote function was recompiled at 4:00 p.m. today.
What can be said about the subsequent executions of CALCULATE_BUDGET?
B. 
The first execution attempt will result in a recompilation and, if successful, will re-execute.
C. 
Unless the formal arguments of GET_BUDGET have changed, CALCULATE_BUDGET will execute
without recompilation.
D. 
The first execution attempt will result in a runtime error. The second execution attempt will result in a
recompilation and, if successful, will re-execute.
14.
How does a local dependent object differ from a remote dependent object?
A. 
Local dependent objects are on the same node in the same database.
B. 
Remote dependent objects are on the same node in the same database.
C. 
Local dependent objects are on different nodes in the same database.
D. 
Remote dependent objects are on the same node in different databases.
15.
Examine this package:
CREATE OR REPLACE PACKAGE theater_pck
IS
v_total_budget NUMBER;
PROCEDURE find_seats_sold
(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);
END theater_pck;
CREATE OR REPLACE PACKAGE BODY theater_pck
IS
current_avg_cost_per_ticket NUMBER;
PROCEDURE find_seats_sold
(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER)
IS
v_seats_sold gross_receipt.seats_sold%TYPE;
v_budget studio.yearly_budget%TYPE;
BEGIN
SELECT seats_sold
INTO v_seats_sold
FROM gross_receipt
WHERE movie_id = v_movie_id
AND theater_id = v_theater_id;
END find_seats_sold;
FUNCTION get_budget
(v_studio_id IN NUMBER)
RETURN number
IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;
RETURN v_yearly_budget;
END get_budget;
END theater_pck;
You are considering adding a call to the stand-alone function, SET_BUDGET. If you make this change, what
should you be concerned with if the code of SET_BUDGET is modified in the future?
A. 
The entire package body will be invalidated and will recompile upon the next execution.
B. 
The entire package body will be invalidated and must be manually compiled before the next execution.
C. 
The entire package specification will be invalidated and will recompile upon the next execution.
D. 
The entire package specification will be invalidated and must be manually compiled before the next
execution.
16.
321Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
v_budget studio.yearly_budget%TYPE;
BEGIN
v_budget := get_budget (11);
IF v_budget < 30000000 THEN
set_budget (11, 300000000);
END IF;
END;
If the GET_BUDGET function is currently marked as invalid, when will it be recompiled implicitly?
A. 
Never, but may be manually recompiled
B. 
The next time the SET_BUDGET procedure is analyzed
C. 
The second time the GET_BUDGET function is executed
D. 
The next time the CALCULATE_BUDGET procedure is executed
17.
Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
v_budget studio.yearly_budget%TYPE;
v_studio_rec studio%ROWTYPE;
BEGIN
SELECT *
INTO v_studio_rec
FROM studio
WHERE NAME = 'Vince Productions';
v_budget := get_budget (v_studio_rec.id);
IF v_budget < 30000000 THEN
set_budget (11, 300000000);
END IF;
END;
Which effect could you expect if a new column were added to the STUDIO table?
A. 
Since the code references %ROWTYPE, no affect will be noticed.
B. 
The CALCULATE_BUDGET procedure would be marked valid and would automatically compile
successfully upon the next execution.
C. 
The CALCULATE_BUDGET procedure would be marked invalid and would automatically compile
successfully upon the next execution.
D. 
The CALCULATE_BUDGET procedure would be marked invalid and would automatically compile
unsuccessfully upon the next execution.
18.
Examine this function:
CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, v_new_budget IN NUMBER)
RETURN number
IS
BEGIN
UPDATE studio
SET yearly_budget = v_new_budget
WHERE id = v_studio_id;
COMMIT;
RETURN SQL%ROWCOUNT;
END;
This function is executed from within a procedure called, CALCULATE_BUDGET. When executing
CALCULATE_BUDGET, which relationships will be identified?
A. 
A direct dependency between SET_BUDGET and STUDIO only
B. 
A direct dependency between SET_BUDGET and CALCULATE_BUDGET only
C. 
A direct dependency between SET_BUDGET, CALCULATE_BUDGET, and STUDIO
D. 
A direct dependency between SET_BUDGET and CALCULATE_BUDGET, a direct dependency
between SET_BUDGET and STUDIO, and an indirect dependency between CALCULATE_BUDGET
and STUDIO
19.
Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
BEGIN
v_budget := get_budget (11);
IF v_budget < 30000000 THEN
set_budget(11, 300000000);
END IF;
END;
A table that SET_BUDGET references has been accidentally dropped. Until this problem is resolved, what is
the status of these procedures?
A. 
Only SET_BUDGET is marked invalid.
B. 
Only GET_BUDGET and SET_BUDGET are marked invalid.
C. 
Only CALCULATE_BUDGET and SET_BUDGET are marked invalid.
D. 
All three are marked invalid.
20.
Examine this code:
CREATE OR REPLACE PROCEDURE find_seats_sold
(v_movie_id IN NUMBER)
IS
v_seats_sold NUMBER(3);
BEGIN
SELECT seats_sold
INTO v_seats_sold
FROM gross_receipt
WHERE movie_id = v_movie_id;
END;
You are concerned with future changes to the database, such as modifying the maximum length of the
SEATS_SOLD column of the GROSS_RECEIPT table. Which change should you make to guarantee
successful recompilation of this procedure in the event of such a change?
A. 
Define V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE.
B. 
Define V_SEATS_SOLD using the PRAGMA EXCEPTION_INIT compiler directive.
C. 
Change V_SEATS_SOLD to be a formal argument rather than a local variable.
D. 
Define V_SEATS_SOLD using the PRAGMA RESTRICT_REFERENCES compiler directive.
21.
Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
v_budget studio.yearly_budget%TYPE;
BEGIN
v_budget := get_budget (11);
IF v_budget < 30000000 THEN
set_budget (11, 300000000);
END IF;
END;
For which reason would the recompilation of CALCULATE_BUDGET be unsuccessful?
A. 
A change to the code of SET_BUDGET
B. 
A change to the code of GET_BUDGET
C. 
A change to the arguments of SET_BUDGET or GET_BUDGET
D. 
A change to the maximum length of the YEARLY_SALARY column of the STUDIO table
22.
Which data dictionary table can you query to determine all stand-alone procedures that reference the
THEATER_PCK package?
A. 
B. 
C. 
D. 
23.
Which three tables or views could you use to help track dependencies? (Choose three.)
A. 
B. 
C. 
D. 
E. 
24.
Which statement will create the dependency structure of the DEPARTMENT table in the PROD schema?
A. 
EXECUTE deptree_fill ('PROD.DEPARTMENT')
B. 
EXECUTE deptree_fill ('PROD', 'DEPARTMENT')
C. 
EXECUTE deptree_fill ('TABLE', 'PROD.DEPARTMENT')
D. 
EXECUTE deptree_fill ('TABLE', 'PROD', 'DEPARTMENT')
E. 
EXECUTE deptree_fill ('TABLE', 'PROD', 'DEPARTMENT')
25.
Examine this procedure:
CREATE OR REPLACE PROCEDURE calculate_budget
IS
v_budget studio.yearly_budget%TYPE;
BEGIN
v_budget :=
[email protected](11);
IF v_budget < 30000000 THEN
set_budget (11, 300000000);
END IF;
END;
You are about to add an argument to the local procedure, CALCULATE_BUDGET. What effect will this have
on the remote procedure, GET_BUDGET?
A. 
It will be marked as invalid
B. 
It will be recompiled at the next execution.
C. 
It will be recompiled at the second execution.
D.