Oracle PL/SQL by Example, Fifth Edition (2015)

---

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. TheALL_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.

For Example ch22_4.sql

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.