Lab 16.1 Exercises

16.1.1 Get Stored Code Information from the Data Dictionary

Answer the following questions:

a)	Query the data dictionary to determine all the stored procedures, functions, and packages in the current schema of the database. Also include the current status of the stored code. Write the SELECT statement.
b)	Type the following script into a text file and run the script in SQL*Plus. It creates the function scode_at_line . Explain what the purpose of this function is. What is accomplished by running it? When does a developer find it useful?

FOR EXAMPLE

 -- ch16_1a.sql 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; 

c)	Type DESC USER_ERRORS. What do you see? In what way do you think this view is useful for you?
d)	Type the following script to force an error. CREATE OR REPLACE PROCEDURE FORCE_ERROR as BEGIN SELECT course_no INTO v_temp FROM course; END; Now type: SHO ERR What do you see?
e)	How can you retrieve information from the USER_ERRORS view?
f)	Type DESC USER_DEPENDENCIES. What do you see? How can you make use of this view?
g)	Type the following: SELECT referenced_name FROM user_dependencies WHERE name = 'SCHOOL_API'; Analyze what you see and explain how it is useful.
h)	Type DESC school_api . What do you see?
i)	Explain what you are seeing. How is this different from the USER_DEPENDENCIES view?

16.1.2 Enforce Purity Level with RESTRICT_REFERENCES Pragma

Answer the following questions:

a)	Add the following function to the school_api package specification that you created in Chapter 13: 6 FUNCTION total_cost_for_student 7 (i_student_id IN student.student_id%TYPE) 8 RETURN course.cost%TYPE; 9 END school_api; Append to the body: 60 FUNCTION total_cost_for_student 61 (i_student_id IN student.student_id%TYPE) 62 RETURN course.cost%TYPE 63 IS 64 v_cost course.cost%TYPE; 65 BEGIN 66 SELECT sum(cost) 67 INTO v_cost 68 FROM course c, section s, enrollment e 69 WHERE c.course_no = s.course_no 70 AND e.section_id = s.section_id 71 AND e.student_id = i_student_id; 72 RETURN v_cost; 73 EXCEPTION 74 WHEN OTHERS THEN 75 RETURN NULL; 76 END total_cost_for_student; 77 BEGIN 78 SELECT trunc(sysdate, 'DD') 79 INTO v_current_date 80 FROM dual; 81 END school_api; If you performed the following SELECT statement, what would you expect to see? SELECT school_api.total_cost_for_student(student_id), student_id FROM student; A pragma is a special directive to the PL/SQL compiler. You use the RESTRICT_REFERENCES pragma to tell the compiler about the purity level of a packaged function. To assert the purity level, use the syntax: PRAGMA RESTRICT_REFERENCES (function_name, WNDS [,WNPS], [,RNDS] [,RNPS])
b)	Alter the package specification for school_api as follows : 6 FUNCTION total_cost_for_student 7 (i_student_id IN student.student_id%TYPE) 8 RETURN course.cost%TYPE; 9 PRAGMA RESTRICT_REFERENCES 10 (total_cost_for_student, WNDS, WNPS, RNPS); 11 END school_api; Now run the SELECT statement from question (a). What do you expect to see?
c)	What is the "purity level" of the function school_api. total_cost_for_student ?
d)	If you add the following three lines, will the package compile without error? 81 UPDATE STUDENT 82 SET employer = 'Prenctice Hall' 83 WHERE employer is null; 84 END school_api;

16.1.3 Overload 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 runtime 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

a)	Add the following lines to the package specification of school_api . Then recompile the package specification. Explain what you have created. 11 PROCEDURE get_student_info 12 (i_student_id IN student.student_id%TYPE, 13 o_last_name OUT student.last_name%TYPE, 14 o_first_name OUT student.first_name%TYPE, 15 o_zip OUT student.zip%TYPE, 16 o_return_code OUT NUMBER); 17 PROCEDURE get_student_info 18 (i_last_name IN student.last_name%TYPE, 19 i_first_name IN student.first_name%TYPE, 20 o_student_id OUT student.student_id%TYPE, 21 o_zip OUT student.zip%TYPE, 22 o_return_code OUT NUMBER); 23 END school_api;
b)	Add the following code to the body of the package school_api . Explain what has been accomplished. 77 PROCEDURE get_student_info 78 (i_student_id IN student.student_id%TYPE, 79 o_last_name OUT student.last_name%TYPE, 80 o_first_name OUT student.first_name%TYPE, 81 o_zip OUT student.zip%TYPE, 82 o_return_code OUT NUMBER) 83 IS 84 BEGIN 85 SELECT last_name, first_name, zip 86 INTO o_last_name, o_first_name, o_zip 87 FROM student 88 WHERE student.student_id = i_student_id; 89 o_return_code := 0; 90 EXCEPTION 91 WHEN NO_DATA_FOUND 92 THEN 93 DBMS_OUTPUT.PUT_LINE ('Student ID is not valid.'); 94 o_return_code := -100; 95 o_last_name := NULL; 96 o_first_name := NULL; 97 o_zip := NULL; 98 WHEN OTHERS 99 THEN 100 DBMS_OUTPUT.PUT_LINE ('Error in procedure get_student_info'); 101 END get_student_info; 102 PROCEDURE get_student_info 103 (i_last_name IN student.last_name%TYPE, 104 i_first_name IN student.first_name%TYPE, 105 o_student_id OUT student.student_id%TYPE, 106 o_zip OUT student.zip%TYPE, 107 o_return_code OUT NUMBER) 108 IS 109 BEGIN 110 SELECT student_id, zip 111 INTO o_student_id, o_zip 112 FROM student 113 WHERE UPPER(last_name) = UPPER(i_last_name) 114 AND UPPER(first_name) = UPPER(i_first_name); 115 o_return_code := 0; 116 EXCEPTION 117 WHEN NO_DATA_FOUND 118 THEN 119 DBMS_OUTPUT.PUT_LINE ('Student name is not valid.'); 120 o_return_code := -100; 121 o_student_id := NULL; 122 o_zip := NULL; 123 WHEN OTHERS 124 THEN 125 DBMS_OUTPUT.PUT_LINE ('Error in procedure get_student_info'); 126 END get_student_info; 127 BEGIN 128 SELECT TRUNC(sysdate, 'DD') 129 INTO v_current_date 130 FROM dual; 131 END school_api;
c)	Write a PL/SQL block using the overloaded function you just created.