Examine this package specification and body:
CREATE OR REPLACE PACKAGE theater_pck
IS
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;
Which statement about the CURRENT_AVG_COST_PER_TICKET variable is true?
Explanation:
Variables declared in the package body declaration can be referenced from all constructs within the package.
These variables are considered private and cannot be referenced from outside the package.
Create Packages
Item: 7 (Ref:1z0-147e.5.4.6)
nmlkj It can only be referenced from outside the package.
nmlkj It can be referenced from outside and within the package.
nmlkj It can be referenced by all constructs within the package.
nmlkj It must be moved to the specification to compile successfully.
Answer:
It can be referenced by all constructs within the package.
Page 1 of 197
Copyright ? 2004 Self Test Software, Inc. All Rights Reserved
Objective:
Create Packages
Sub-Objective:
Designate a package construct as either public or private
References:
1. Oracle9i: Develop PL/SQL Program Units - Creating Packages
Page 2 of 197
Copyright ? 2004 Self Test Software, Inc. All Rights Reserved
While creating a package, you placed the function name in the specification and the body. Which type of
construct have you created?
Explanation:
All constructs must be declared in the package body. Constructs that are also declared in a package
specification are public and can be referenced from inside or outside of the package.
Objective:
Create Packages
Sub-Objective:
Designate a package construct as either public or private
References:
1. Oracle9i: Develop PL/SQL Program Units - Creating Packages
Item: 14 (Ref:1z0-147e.5.4.4)
nmlkj public
nmlkj illegal
nmlkj private
nmlkj one-time only
Answer:
public
Page 3 of 197
Copyright ? 2004 Self Test Software, Inc. All Rights Reserved
Examine this package specification and body:
CREATE OR REPLACE PACKAGE theater_pck
IS
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;
Which statement about the FIND_SEATS_SOLD procedure is true?
Explanation:
Procedures declared in a package specification are public and can be referenced from inside or outside of the
package.
Objective:
Item: 15 (Ref:1z0-147e.5.4.5)
nmlkj It can be referenced within a SQL statement.
nmlkj It can only be referenced from within the package.
nmlkj It can be referenced from within and outside of the package.
nmlkj It cannot also be specified in the specification because it is specified in the body.
Answer:
It can be referenced from within and outside of the package.
Page 4 of 197
Copyright ? 2004 Self Test Software, Inc. All Rights Reserved
Create Packages
Sub-Objective:
Designate a package construct as either public or private
References:
1. Oracle9i: Develop PL/SQL Program Units - Creating Packages
Page 5 of 197
Copyright ? 2004 Self Test Software, Inc. All Rights Reserved
Examine this package specification:
CREATE OR REPLACE PACKAGE theater_package
IS
PROCEDURE find_cpt
(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);
PROCEDURE update_theater (v_name IN VARCHAR2);
PROCEDURE find_seats_sold