Lab 16.1 Exercises

16.1.1 Get Stored Code Information from the Data Dictionary

Answer the following questions:


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.


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?


 -- 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; 

Type DESC USER_ERRORS. What do you see? In what way do you think this view is useful for you?


Type the following script to force an error.


Now type:


What do you see?


How can you retrieve information from the USER_ERRORS view?


Type DESC USER_DEPENDENCIES. What do you see? How can you make use of this view?


Type the following:

 SELECT referenced_name FROM user_dependencies WHERE name = 'SCHOOL_API'; 

Analyze what you see and explain how it is useful.


Type DESC school_api . What do you see?


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:


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]) 

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?


What is the "purity level" of the function school_api. total_cost_for_student ?


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


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, 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, 22        o_return_code OUT NUMBER); 23  END school_api; 

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,  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, 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; 

Write a PL/SQL block using the overloaded function you just created.

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: