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: In a local module in the same PL/SQL block In a package specification 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. |
|