22. Stored Code
In Chapter 19 you learned about procedures, in Chapter 20 you learned about functions, and in Chapter 21 you learned about the process of grouping functions and procedures into a package. Now you will learn more about what it means to have code bundled into a package. Numerous data dictionary views can be accessed to gather information about the objects in a package.
Functions in packages are required to meet additional restrictions to be used in a SELECT statement. In this chapter, you will learn what those restrictions are and how to enforce them. You will also learn an advanced technique to overload a function or procedure so that it executes different code depending on the type of parameter passed in.
Lab 22.1: Gathering Information about Stored Code
Stored programs are held in a compiled form in the database. Information about such stored programs is accessible through various data dictionary views. In Chapter 19, you learned about two data dictionary views: USER_OBJECTS and USER_SOURCE. In Chapter 13, you learned about another view, USER_TRIGGERS. A few other data dictionary views are also useful for obtaining information about stored code. In this lab, you will learn how to take advantage of these options.
Getting Stored Code Information from the Data Dictionary
The Oracle data dictionary contains system views that can be used to examine all the stored procedures, functions, and packages in the current schema of the database. They also provide the current status of the stored code. The primary view to be used for this purpose is the USER_OBJECTS view you encountered in Chapter 11. This view has information about all database objects in the schema of the current user. In contrast, if you want to see all the objects in other schemas to which the current user has access, you would use the ALL_OBJECTS view. There is also a DBA_OBJECTS view that lists all objects in the database regardless of privilege. The status of each object will be marked as either VALID or INVALID. That status can change from VALID to INVALID if an underlying table is altered or if privileges on a referenced object are revoked by the creator of the function, procedure, or package.
The following SELECT statement lists all functions, procedures, and packages that are in the schema of the current user.
For Example ch22_1.sql
SELECT OBJECT_TYPE, OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN
('FUNCTION', 'PROCEDURE', 'PACKAGE',
'PACKAGE_BODY')
ORDER BY OBJECT_TYPE;
The user_source view in the data dictionary can be used to extract the source code for procedures, functions, and packages. The column TEXT holds the actual source code text, NAME holds the name, and TYPE indicates if it is a function, procedure, package, or package body. The text is listed in order by line number in the column line.
The following example creates a function called scode_at_line that provides an easy mechanism for retrieving the text from a stored program for a specified line number.
CREATE OR REPLACE FUNCTION scode_at_line
(i_name_in IN VARCHAR2,
i_line_in IN INTEGER := 1,
i_type_in IN VARCHAR2 := NULL)
RETURN VARCHAR2
IS
CURSOR scode_cur IS
SELECT text
FROM user_source
WHERE name = UPPER (i_name_in)
AND (type = UPPER (i_type_in)
OR i_type_in IS NULL)
AND line = i_line_in;
scode_rec scode_cur%ROWTYPE;
BEGIN
OPEN scode_cur;
FETCH scode_cur INTO scode_rec;
IF scode_cur%NOTFOUND
THEN
CLOSE scode_cur;
RETURN NULL;
ELSE
CLOSE scode_cur;
RETURN scode_rec.text;
END IF;
END;
This function is useful if a developer receives a compilation error message referring to a particular line number in an object. The developer can call this function to find out which text is the source of the error.
The scode_at_line function uses three parameters:
name_in The name of the stored object.
line_in The line number of the line you wish to retrieve. The default value is 1.
type_in The type of object you want to view. The default for type_in is NULL.
The default values are designed to make this function as easy as possible to use.
You can use the USER_ERRORS view to get more details about compilation errors that occur when you are writing code. This view stores current errors on the user’s stored objects. The text file contains the text of the error—a handy feature when you are trying to pin down the details of a compilation error. Following are the columns for the USER_ERRORS view that you would see if you entered the command DESC USER_ERRORS in SQL*Plus.
Name Null? Type
-------------------- -------- -----------
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
SEQUENCE NOT NULL NUMBER
LINE NOT NULL NUMBER
POSITION NOT NULL NUMBER
TEXT NOT NULL VARCHAR2(2000)
The following code fragment produces a forced error so that we can demonstrate the various methods used to debug a problem:
CREATE OR REPLACE PROCEDURE FORCE_ERROR
as
BEGIN
SELECT course_no
INTO v_temp
FROM course;
END;
In SQL Developer, the errors would then be seen in the compiler log screen. In SQL*Plus, you need to type SHO ERR to see the same information. In either case, the errors will be shown as follows:
Errors for PROCEDURE FORCE_ERROR:
LINE/COL ERROR
-------- --------------------------------------------
4/4 PL/SQL: SQL Statement ignored
5/9 PLS-00201: identifier 'V_TEMP' must be declared
6/4 PL/SQL: ORA-00904: : invalid identifier
You can use a SELECT statement to retrieve information from the USER_ERRORS view:
SELECT line||'/'||position "LINE/COL", TEXT "ERROR"
FROM user_errors
WHERE name = 'FORCE_ERROR'
It is important to know how to retrieve this information from the USER_ERRORS view because the SHO ERR command simply brings up the most recent errors. If you run a script creating a number of objects, then you must rely on the USER_ERRORS view to identify all of the errors.
The USER_DEPENDENCIES view is useful for analyzing how table changes or changes to other stored procedures affect other parts of the script. If you plan to redesign tables, for example, you might want to assess their impact by examining the information in this view. The ALL_DEPENDENCIES and DBA_DEPENDENCIES views show all dependencies for procedures, functions, package specifications, and package bodies. Entering the command DESC USER_DEPENDENCIES in SQL&*Plus produces the following output:
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
REFERENCED_OWNER VARCHAR2(30)
REFERENCED_NAME NOT NULL VARCHAR2(30)
REFERENCED_TYPE VARCHAR2(12)
REFERENCED_LINK_NAME VARCHAR2(30)
The following SELECT statement demonstrates the dependencies for the school_api package:
SELECT referenced_name
FROM user_dependencies
WHERE name = 'SCHOOL_API';
This is the result of running the SELECT statement:
REFERENCED_NAME
-----------------------------
STANDARD
STANDARD
DUAL
DBMS_STANDARD
DBMS_OUTPUT
COURSE
ENROLLMENT
INSTRUCTOR
INSTRUCTOR
INSTRUCTOR_ID_SEQ
SCHOOL_API
SECTION
This list of dependencies for the school_api package lists all objects referenced in the package. It includes tables, sequences, and procedures (even Oracle-supplied packages). This information is very useful when you are planning a change to a database structure. You can easily pinpoint what the ramifications are for any database changes.
The DESC command in SQL*Plus is used to describe the columns in a table as well as to identify procedures, packages, and functions. This command shows all the parameters with their default values and indicates whether they are IN or OUT. If the object is a function, then the return data type is displayed. This is very different from the USER_DEPENDENCIES view, which provides information on all the objects that are referenced in a package, function, or procedure. In SQL Developer, the same information can be obtained by finding the name of the object in the tree and hovering the cursor over the name.
Overloading Modules
When you overload modules, you give two or more modules the same name. The parameter lists of the modules must differ in a manner significant enough for the compiler (and run-time engine) to distinguish between the different versions.
You can overload modules in three contexts:
1. In a local module in the same PL/SQL block
2. In a package specification
3. In a package body
The following changes to the school_api package demonstrate how module overloading can be used.
For Example ch22_3.sql
CREATE OR REPLACE PACKAGE school_api as
v_current_date DATE;
PROCEDURE Discount_Cost;
FUNCTION new_instructor_id
RETURN instructor.instructor_id%TYPE;
FUNCTION total_cost_for_student
(i_student_id IN student.student_id%TYPE)
RETURN course.cost%TYPE;
PRAGMA RESTRICT_REFERENCES
(total_cost_for_student, WNDS, WNPS, RNPS);
PROCEDURE get_student_info
(i_student_id IN student.student_id%TYPE,
o_last_name OUT student.last_name%TYPE,
o_first_name OUT student.first_name%TYPE,
o_zip OUT student.zip%TYPE,
o_return_code OUT NUMBER);
PROCEDURE get_student_info
(i_last_name IN student.last_name%TYPE,
i_first_name IN student.first_name%TYPE,
o_student_id OUT student.student_id%TYPE,
o_zip OUT student.zip%TYPE,
o_return_code OUT NUMBER);
END school_api;
In this example of an overloaded procedure, the specification has two procedures with the same name and different IN parameters (different both in number and in data type). The OUT parameters are also different in number and data type. This overloaded function accepts either of the two sets of IN parameters and performs the version of the function corresponding to the data type passed in. The next example contains the package body.
CREATE OR REPLACE PACKAGE BODY school_api AS
PROCEDURE discount_cost
IS
CURSOR c_group_discount
IS
SELECT distinct s.course_no, c.description
FROM section s, enrollment e, course c
WHERE s.section_id = e.section_id
GROUP BY s.course_no, c.description,
e.section_id, s.section_id
HAVING COUNT(*) >=8;
BEGIN
FOR r_group_discount IN c_group_discount
LOOP
UPDATE course
SET cost = cost * .95
WHERE course_no = r_group_discount.course_no;
DBMS_OUTPUT.PUT_LINE
('A 5% discount has been given to'
||r_group_discount.course_no||'
'||r_group_discount.description);
END LOOP;
END discount_cost;
FUNCTION new_instructor_id
RETURN instructor.instructor_id%TYPE
IS
v_new_instid instructor.instructor_id%TYPE;
BEGIN
SELECT INSTRUCTOR_ID_SEQ.NEXTVAL
INTO v_new_instid
FROM dual;
RETURN v_new_instid;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
v_sqlerrm VARCHAR2(250) :=
SUBSTR(SQLERRM,1,250);
BEGIN
RAISE_APPLICATION_ERROR(-20003,
'Error in instructor_id: '||v_sqlerrm);
END;
END new_instructor_id;
FUNCTION total_cost_for_student
(i_student_id IN student.student_id%TYPE)
RETURN course.cost%TYPE
IS
v_cost course.cost%TYPE;
BEGIN
SELECT sum(cost)
INTO v_cost
FROM course c, section s, enrollment e
WHERE c.course_no = s.course_no
AND e.section_id = s.section_id
AND e.student_id = i_student_id;
RETURN v_cost;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END total_cost_for_student;
PROCEDURE get_student_info
(i_student_id IN student.student_id%TYPE,
o_last_name OUT student.last_name%TYPE,
o_first_name OUT student.first_name%TYPE,
o_zip OUT student.zip%TYPE,
o_return_code OUT NUMBER)
IS
BEGIN
SELECT last_name, first_name, zip
INTO o_last_name, o_first_name, o_zip
FROM student
WHERE student.student_id = i_student_id;
o_return_code := 0;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('Student ID is not valid.'),
o_return_code := -100;
o_last_name := NULL;
o_first_name := NULL;
o_zip := NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
('Error in procedure get_student_info'),
END get_student_info;
PROCEDURE get_student_info
(i_last_name IN student.last_name%TYPE,
i_first_name IN student.first_name%TYPE,
o_student_id OUT student.student_id%TYPE,
o_zip OUT student.zip%TYPE,
o_return_code OUT NUMBER)
IS
BEGIN
SELECT student_id, zip
INTO o_student_id, o_zip
FROM student
WHERE UPPER(last_name) = UPPER(i_last_name)
AND UPPER(first_name) = UPPER(i_first_name);
o_return_code := 0;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('Student name is not valid.'),
o_return_code := -100;
o_student_id := NULL;
o_zip := NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
('Error in procedure get_student_info'),
END get_student_info;
BEGIN
SELECT TRUNC(sysdate, 'DD')
INTO v_current_date
FROM dual;
END school_api;
In this version of the school_api, a single function name, get_student_info, accepts either a single IN parameter of student_id or two parameters consisting of a student’s last_name and first_name. If a number is passed in, then the procedure looks for the name and ZIP code of the student. If it finds them, they are returned along with a return code of 0. If they cannot be found, then null values are returned along with a return code of 100. If two VARCHAR2 parameters are passed in, then the procedure searches for the student_id corresponding to the names passed in. As with the other version of this procedure, if a match is found, the procedure returns a student_id, the student’s ZIP code, and a return code of 0. If a match is not found, then the values returned are null and the exit code is 100.
PL/SQL uses overloading in many common functions and built-in packages. For example, TO_CHAR converts both numbers and dates to strings. Overloading makes it easy for other programmers to use your code in an API.
The main benefits of overloading are threefold. First, overloading simplifies the call interface of packages and reduces many program names to one. Second, modules are easier to use and hence more likely to be used. The software determines the context. Third, the volume of code is reduced because the code required for different data types is often the same.
The following PL/SQL block shows how this overloaded function can be used:
DECLARE
v_student_ID student.student_id%TYPE;
v_last_name student.last_name%TYPE;
v_first_name student.first_name%TYPE;
v_zip student.zip%TYPE;
v_return_code NUMBER;
BEGIN
school_api.get_student_info
(&&p_id, v_last_name, v_first_name,
v_zip,v_return_code);
IF v_return_code = 0
THEN
DBMS_OUTPUT.PUT_LINE
('Student with ID '||&&p_id||' is '||v_first_name
||' '||v_last_name
);
ELSE
DBMS_OUTPUT.PUT_LINE
('The ID '||&&p_id||'is not in the database'
);
END IF;
school_api.get_student_info
(&&p_last_name , &&p_first_name, v_student_id,
v_zip , v_return_code);
IF v_return_code = 0
THEN
DBMS_OUTPUT.PUT_LINE
(&&p_first_name||' '|| &&p_last_name||
' has an ID of '||v_student_id );
ELSE
DBMS_OUTPUT.PUT_LINE
(&&p_first_name||' '|| &&p_last_name||
'is not in the database'
);
END IF;
END;
When you run this script, you will be prompted for these three values. Here is an example of a valid value to enter as the input:
Enter value for p_id: 149
Enter value for p_last_name: 'Prochaska'
Enter value for p_first_name: 'Judith'
This example demonstrates the benefits of using a && variable. The value for the variable need be entered only once, but if you run the code a second time, you will not be prompted to enter the value again because it is now in memory.
Here are a few points to keep in mind when you overload functions or procedures. These two procedures cannot be overloaded:
PROCEDURE calc_total (reg_in IN CHAR);
PROCEDURE calc_total (reg_in IN VARCHAR2).
In these two versions of calc_total, the two different IN variables cannot be distinguished from each other. In the following example, an anchored type (%TYPE) is relied on to establish the data type of the second calc’s parameter.
DECLARE
PROCEDURE calc (comp_id_IN IN NUMBER)
IS
BEGIN ... END;
PROCEDURE calc
(comp_id_IN IN company.comp_id%TYPE)
IS
BEGIN ... END;
PL/SQL does not find a conflict at compile time with overloading even though comp_id is a numeric column. Instead, you will see the following message at run time:
PLS-00307: too many declarations of '<program>' match this call
Summary
In this chapter, you learned about the various data dictionary views that can be used to gather information about stored code. These views enable you to obtain information about the parameters and dependencies of the functions, procedures, and packages. You also learned about how to overload functions and procedures so that the same object can be used in different ways depending on how many and which type of values are passed to the calling function or procedure.