Deck 7: PL/SQL Packages

Question 1

A one time only procedure is included in a package body as an anonymous PL/SQL block at the end of the body code.

Answer 1

True

Question 2

A REEF CURSOR data type declared in a package specification may be referenced by multiple program units.

Answer 2

True

Question 3

Functions that have not been declared in a package specification are considered public.

Answer 3

False

Question 4

Functions are used in CHECK constraints or as a default values of table columns.

Question 5

The term user scope is the range of visibility for a particular element or construct contained in a package. _________________________

Question 6

A(n) package body is the program unit that contains the code for any procedures and functions declared in the specification. _________________________

Question 7

Packaged variable values are persistent throughout a user session and each user of the package has his or her own copy of the packaged variable.

Question 8

Developers can use the PRAGMA RESTRICT_REFERENCES compiler instruction to indicate the purity level of the function in the package specification.

Question 9

Packages can be created in local libraries, as can procedures and functions.

Question 10

A package specification can contain declarations for procedures, functions, variables, exceptions, cursors, and types.

Question 11

All code in the procedure and function header sections of a package body must match exactly to the declarations in the corresponding specification.

Question 12

Package elements are considered public if they can be called only from other program units within the same package. _________________________

Question 13

The pragma level defines what type of data structures the function reads or modifies.

Question 14

When compiling objects that call packaged program units, only the package specification or program unit header is used for verification.

Question 15

Functions written in other languages such as JAVA can also be called from Oracle programs.

Question 16

When the values of package element persist throughout a user session and, therefore, can be referenced in code within various parts of the application during a user session, the elements are considered to be global. _________________________

Question 17

For overloading to be successful, the formal parameters in the procedures or functions must be the same.

Question 18

Items declared in the package body and not in the specification can be used only by other procedures and functions within this same package body.

Question 19

Package code is not cached, nor is package data such as variables and cursors.

Question 20

Adding the program unit name to the END statement that closes the procedure or function statements in the package body is mandatory.

Question 21

____ can be created only as stored program units.

A) Program units
B) Package specifications
C) Packages
D) Cursors

Question 22

The DEL command can be used to remove the package specification and body. _________________________

Question 23

The AUTHID CURRENT_USER clause is used to override definer-rights behavior. _________________________

Question 24

The term ____ refers to the range of visibility for a particular element or construct contained in a package.

A) package scope
B) package specification
C) package body
D) forward declaration

Question 25

The USER_OBJECTS data dictionary view allows us to view the source code of packages. _________________________

Question 26

A(n) program declaration is a declaration of a program unit in a package body by placing the header code at the top of the package body code. _________________________

Question 27

Using the same name on multiple program units within the same package is referred to as ____.

A) overloading
B) declaring
C) initializing
D) referencing

Question 28

Package elements are considered ____ if they can be called only from other program units within the same package.

A) public
B) overloaded
C) one time procedures
D) private

Question 29

Package elements that can be referenced from outside the package are considered ____.

A) public
B) overloaded
C) one time only procedures
D) private

Question 30

A(n) ____ is a statement used to define a data construct, such as a variable, name, and data type.

A) package specification
B) package header
C) declaration
D) initialization

Question 31

CREATE OR REPLACE PACKAGE ordering_pkg IS
Pv_total_num NUMBER(3,2);
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
P_sub OUT NUMBER);
FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER;
END;
When creating a package body for the package specification above, which of the following statements is incorrect?

A) The package body must be created using the same name as the existing specification.
B) All code in the procedure and function header sections in the package body must match exactly to the declarations in the corresponding specification.
C) The package body cannot include declarations of variables, cursors, types, and program units not found in the corresponding specification.
D) Items declared in the body and not in the specification can be used only by other procedures and functions within this same package body.

Question 32

CREATE OR REPLACE PACKAGE ordering_pkg IS
Pv_total_num NUMBER(3,2);
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
P_sub OUT NUMBER);
FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER;
END;
Based on the package specification above, which of the following code fragments correctly creates a package body?

A) CREATE OR REPLACE PACKAGE BODY ordering_pkg IS FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER
IS
Lv_ship_num NUMBER(5,2);
BEGIN
... code detail ...
END ship_calc_pf;
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
P_sub OUT NUMBER)
IS
BEGIN
... code detail ...
END order_total_pp;
END;
B) CREATE OR REPLACE PACKAGE ordering_pkg IS FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER
IS
Lv_ship_num NUMBER(5,2);
BEGIN
... code detail ...
END ship_calc_pf;
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
P_sub OUT NUMBER,
IS
BEGIN
... code detail ...
END order_total_pp;
END;
C) CREATE OR REPLACE PACKAGE BODY ordering_pkg IS FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER
IS
Lv_ship_num NUMBER(5,2);
BEGIN
... code detail ...
END ship_calc_pf;
END;
D) CREATE OR REPLACE PACKAGE BODY order_pkg IS FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER
IS
Lv_ship_num NUMBER(5,2);
BEGIN
... code detail ...
END ship_calc_pf;
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
P_sub OUT NUMBER)
IS
BEGIN
... code detail ...
END order_total_pp;
END;

Question 33

A(n) ____ is a declaration of a program unit in a package body by placing the header code at the top of the package body code.

A) package scope
B) package declaration
C) package specification
D) forward declaration

Question 34

CREATE OR REPLACE PACKAGE ordering_pkg IS
Pv_total_num NUMBER(3,2);
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
P_sub OUT NUMBER);
FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER;
END;
The code fragment above is an example of a(n) ____.

A) anonymous block
B) package body
C) package specification
D) package scope

Question 35

If a cursor is declared in a package specification that referenced the PV_TOTAL_NUM package variable, the variable ____.

A) need not be declared
B) must be declared after the cursor
C) must be declared as private
D) must be declared before the cursor

Question 36

CREATE OR REPLACE PACKAGE metric_pkg IS cup_to_liter CONSTANT NUMBER := .24;
Pint_to_liter CONSTANT NUMBER := .47;
Qrt_to_liter CONSTANT NUMBER := .95;
END;
Which of the following statements about the code fragment above is correct?

A) The CONSTANT option allows the values of the variables to be modified.
B) There is no need to create a body for this package.
C) Program units are included in the package.
D) The package body must be created.

Question 37

Full procedures or function codes are contained in the ____.

A) package specification
B) package body
C) package header
D) anonymous block

Question 38

The AUTHID directive needs to be included in the package specification to clearly state the purity levels applicable to the function. _________________________

Question 39

The USER_SOURCE data dictionary view is useful to identify what packages exist on the system. _________________________

Question 40

The ____ is referred to as the package header.

A) purity level
B) construct scope
C) program units
D) package specification

Question 41

All of the following represent restrictions on functions, except ____.

A) Functions cannot modify any tables in Oracle8i and prior versions.
B) Functions cannot be used in a CHECK constraint or as a default value of a table column.
C) If used in a remote or parallel operation, no reading or writing of packaged variables is allowed.
D) If used in a SELECT,VALUES, or SET clause, the function cannot write values to packaged variables.

Question 42

The term ____________________ in packages refers to the ability to use the same name on multiple program units within the same package.

Question 43

When compiling objects that call packaged program units, which of the following statements is True?

A) Restriction violations are not discovered until runtime.
B) Only the program body is used for verification.
C) Because only the package body information is used, the PL/SQL compiler cannot determine if the restrictions on functions within SQL statements are being respected.
D) Restriction violations are discovered at compile time.

Question 44

CREATE OR REPLACE PACKAGE product_info_pkg IS PROCEDURE prod_search_pp
(p_id IN bb_product.idproduct%TYPE,
P_sale OUT bb_product.saleprice%TYPE,
P_price OUT bb_product.price%TYPE);
PROCEDURE prod_search_pp
(p_id IN bb_product.productname%TYPE,
P_sale OUT bb_product.saleprice%TYPE,
P_price OUT bb_product.price%TYPE);
END;
The code fragment above is an example of a(n) ____.

A) package body
B) overloaded procedure
C) purity level
D) package scope

Question 45

Which of the following correctly sets the default purity level for a packaged function?

A) RESTRICT_REFERENCES(DEFAULT, WNDS, WNPS);
B) PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, WNPS);
C) PRAGMA RESTRICT_REFERENCES(function_name, WNDS, WNPS);
D) RESTRICT_REFERENCES(function_name, WNDS, WNPS);

Question 46

All of the following represent packaged function purity levels, except ____.

A) WNDS
B) RNDS
C) WNPS
D) RIPS

Question 47

The statement ____ would successfully remove the package specification and body.

A) DROP PACKAGE BODY package_name;
B) DEL PACKAGE BODY package_name;
C) DROP PACKAGE package_name;
D) DEL PACKAGE package_name;

Question 48

One advantage that packages provide is that constructs, such as variables, cursors, types, and exceptions, declared in the specification are ____________________.

Question 49

The statement ____ would successfully declare a REF CURSOR data type.

A) CREATE testcur as REF CURSOR;
B) TYPE testcur IS ref cursor;
C) TYPE testcur ref_cursor;
D) testcur REF_CURSOR;

Question 50

Forcing the user to use his or her own privileges for a program unit, and not that of the owner is called ____.

A) definer-rights
B) user-rights
C) user scope
D) invoker-rights

Question 51

Functions cannot be used in a(n) ____________________ constraint or as a default value of a table column.

Question 52

The ____ directive needs to be included in the package specification to clearly state the purity levels applicable to the function.

A) PRAGMA REFERENCES
B) PRAGMA RESTRICT
C) PRAGMA RESTRICT_REFERENCES
D) RESTRICT_REFERENCES

Question 53

A(n) ____________________ is a declaration of a program unit in a package body by placing the header code at the top of the package body code.

Question 54

The ____ packaged function purity level does not modify database tables.

A) RNDS
B) WNDS
C) WNPS
D) RNPS

Question 55

The ____ packaged function purity level does not modify packaged variables.

A) WNDS
B) RNDS
C) WNPS
D) RNPS

Question 56

A(n) ____________________ procedure is a procedure in a package that runs only once, when the package is initially invoked.

Question 57

The ____ defines the type of data structures the function reads or modifies.

A) cursor
B) purity level
C) package scope
D) one time only procedure

Question 58

The ____________________ serves as a map to what program units are available in the package and the parameters of the program unit.

Question 59

When a user assumes the rights of the program unit owner just during the processing of that program unit, this is referred to as ____.

A) user-rights
B) user scope
C) definer-rights
D) definer-scope

Question 60

Elements declared in a package are considered ____________________ if they can be called only from other program units within the same package.

Question 61

The ____________________ data dictionary view is also useful to identify what packages exist on the system.

Question 62

The ____________________ directive needs to be included in the package specification to clearly state the purity levels applicable to the function.

Question 63

Why would a package require two procedures or functions with the same name?

Question 64

The query to be used to populate a ____________________ data type can be determined at run time.

Question 65

What is meant by the term package body?

Question 66

When compiling objects that call packaged program units, only the ____________________ is used for verification.

Question 67

The ____________________ data dictionary view allows us to view the source code of packages.

Question 68

Define the term package, and explain why it is used.