Lab 16.1 Gathering Stored Code Information

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 16.  Stored Code


Lab Objectives

After this Lab, you will be able to:

  • Get Stored Code Information from the Data Dictionary

  • Enforce Purity Level with RESTRICT_REFERENCES Pragma

  • Overload Modules

Stored programs are stored in compiled form in the database. Information about the stored programs is accessible through various data dictionary views. In Chapter 11 you learned about the two data dictionary views USER_OBJECTS and USER_SOURCE. Additionally, you learned about the USER_TRIGGERS view in Chapter 8. There are a few more data dictionary views that are useful for obtaining information about stored code. In this lab, you will learn how to take advantage of these.

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?

graphics/intfig03.gif 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.

Lab 16.1 Exercise Answers

16.1.1 Answers

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.

A1:

Answer: You can use the USER_OBJECTS view you learned about in Chapter 11. This view has information about all database objects in the schema of the current user. Remember, if you want to see all the objects in other schemas that the current user has access to, then use the ALL_OBJECTS view. There is also a DB A_OBJECTS view for a list of all objects in the database regardless of privilege. The STATUS will either be VALID or INVALID. An object can change status from VALID to INVALID if an underlying table is altered or privileges on a referenced object have been revoked from the creator of the function, procedure, or package. The following SELECT statement produces the answer you are looking for.

 SELECT OBJECT_TYPE, OBJECT_NAME, STATUS  FROM   USER_OBJECTS  WHERE  OBJECT_TYPE IN         ('FUNCTION', 'PROCEDURE', 'PACKAGE',          'PACKAGE_BODY')  ORDER BY OBJECT_TYPE; 
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?

graphics/intfig03.gif 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; 
A2:

Answer: The scode_at_line function provides an easy mechanism for retrieving the text from a stored program for a specified line number. This is useful if a developer receives a compilation error message referring to a particular line number in an object. The developer can then make use of this function to find out the text that is in error.

The procedure 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.

graphics/intfig07.gif

The output from a call to SHOW ERRORS in SQL*Plus displays the line number in which an error occurred, but the line number doesn't correspond to the line in your text file. Instead, it relates directly to the line number stored with the source code in the USER_SOURCE view.

c)

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

A3:

Answer: The view stores current errors on the user's stored objects. The text file contains the text of the error. This is useful in determining the details of a compilation error.The next exercise walks you through using this view.

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

A4:

 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 

e)

How can you retrieve information from the USER_ERRORS view?

A5:

 SELECT line||'/'||position "LINE/COL", TEXT "ERROR"  FROM user_errors  WHERE name = 'FORCE_ERROR' 

It is important for you to know how to retrieve this information from the USER_ ERRORS view since the SHO ERR command only shows you the most recent errors. If you run a script creating a number of objects, then you have to rely on the USER_ ERRORS view.

f)

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

A6:

Answer: The DEPENDENCIES view is useful for analyzing the impact that may occur from table changes or changes to other stored procedures. If tables are about to be redesigned, an impact assessment can be made from the information in USER_ DEPENDENCIES. ALL_DEPENDENCIES and DB A_DEPENDENCIES show all dependencies for procedures, functions, package specifications, and package bodies.

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

Type the following:

 SELECT referenced_name  FROM user_dependencies  WHERE name = 'SCHOOL_API'; 

Analyze what you see and explain how it is useful.

A7:

 REFERENCED_NAME  ------------------------------ DUAL  DUAL  STANDARD  STANDARD  DBMS_STANDARD  DBMS_OUTPUT  DBMS_OUTPUT  INSTRUCTOR_ID_SEQ  COURSE  COURSE  ENROLLMENT  INSTRUCTOR  INSTRUCTOR  SECTION  STUDENT  STUDENT  DBMS_OUTPUT  DUAL  SCHOOL_API 

This list of dependencies for the school_api package lists all objects referenced in the package. This includes tables, sequences, and procedures (even Oracle-supplied packages). This information is very useful when you are planning a change to the database structure. You can easily pinpoint what the ramifications are for any database changes.

h)

Type DESC school_api. What do you see?

A8:

 PROCEDURE DISCOUNT  FUNCTION NEW_INSTRUCTOR_ID RETURNS NUMBER(8)  FUNCTION TOTAL_COST_FOR_STUDENT RETURNS NUMBER(9,2)  Argument Name    Type                 In/Out Default?  ------------------------------- --------------------- I_STUDENT_ID     NUMBER(8)               IN 

DEPTREE

There is also an Oracle-supplied utility called DEPTREE that shows you, for a given object, which other objects are dependent upon it. There are three pieces to this utility. You need to have DBA access to the database in order to use this utility.

 utldtree.sql script  DEPTREE_FILL(type, schema, object_name) procedure  ideptree view 

First, run utldtree.sql in your schema. This creates the necessary objects to map the dependencies. The location of utldtree.sql is dependent on your particular installation, so ask your DBA. (c:\orant\ rdbms80\admin\utldtree.sql)

 ($ORACLE_HOME/rdbms/admin/utldtree.sql) 

Second, fill the deptree e_temptab table by running DEPTREE_FILL.

 Example: SQL> exec DEPTREE_FILL('TABLE', USER,  'MESSAGE_LOG') 

Third, look at the deptree information in the ideptree view.

 Example: SQL> SELECT * FROM ideptree; 

The result contains the following kind of information:

 DEPENDENCIES  -------------------------------------- TABLE CTA.MESSAGE_LOG  PACKAGE BODY CTA.API  TRIGGER CTA.COURSE_AFTER_I  PACKAGE CTA.API  PACKAGE BODY CTA.API 
i)

Explain what you are seeing. How is this different from the USER_DEPENDENCIES view?

A9:

Answer: The DESC command you have been using to describe the columns in a table is also used for procedures, packages, and functions. The DESC command shows all the parameters with their default values and an indication of whether they are IN or OUT. If the object is a function, then the return datatype is displayed. This is very different from the USER_DEPENDENCIES view, which has information on all the objects that are referenced in a package, function, or procedure.

16.1.2 Answers

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

Answer: At first glance you may have thought you would see a list of student_ids with the total cost for the courses they took. But instead you see the following error:

 ERROR at line 1:  ORA-06571: Function TOTAL_COST_FOR_STUDENT  does not guarantee not to update database 

Although functions can be used in a SELECT statement, if a function is in a package, it requires some additional definitions to enforce its purity.

Requirements for Stored Functions in SQL

Need a hand with this? I used this command from my shell prompt:

  1. The function must be stored in the database (not in the library of an Oracle tool).

  2. The function must be a row-specific function and not a column or group function.

  3. As for all functions (whether to be used in SQL statements or not), parameters must be the IN mode.

  4. Datatypes of the function parameters and the function RETURN clause must be recognized within the Oracle server. (Not, as of yet, BOOLEAN, BINARY_ INTEGER, PL/SQL tables, PL/SQL records, and programmer-defined subtypes. Maybe in the futurekeep your fingers crossed).

There are numerous function side effects that must be considered. Modification of database tables in stored functions may have ripple effects on queries using the function. Modification of package variables can have an impact on other stored functions or procedures, or in turn the SQL statement using the stored function. Stored functions in the WHERE clause may subvert the query optimization process. A SQL statement may use a stand-alone function or package function as an operator on one or more columns, provided the function returns a valid Oracle database type.

A user-defined function may select from database tables or call other procedures or functions, whether stand-alone or packaged. When a function is used in a SELECT statement, it may not modify data in any database table with an INSERT, UPDATE, or DELETE statement, or read or write package variables across user sessions.

The Oracle server automatically enforces the rules for stand-alone functions, but not with a stored function in a package. The purity level (the extent to which the function is free of side effects) of a function in a package must be stated explicitly. This is done via a pragma.

The reason the error message was received is because the pragma was not used. You will now learn how to make use of a pragma.

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?

A2:

Answer: The pragma restriction is added to the package specification and ensures that the function total_cost_for_student has met the required purity restriction for a function to be in a SELECT statement. The SELECT statement now functions properly and projects a list of the total cost for each student and the student's ID.

Rules for Using Pragma Restrictions

Only the WNDS level is mandatory.

You need a separate pragma statement for each packaged function used in an SQL statement.

The pragma must come after the function declaration in the package specification.

c)

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

A3:

Answer: The extent to which a function is free of side effects is called the purity level of the function.The function is now very pure. It has the following levels of purity: (1) WNDS means write no database state; that is, it does not make any changes to database tables. (2) WNPS means the function writes no package state; that is, the function does not alter the values of any package variables. (3) RNPS means it reads no package state; that is, no package variables are read in order to calculate the return for the function. There is also a RNDS pragma, which means no database tables are read. If this is added, the function is too pure for the needs here and cannot be used in a SELECT statement.

Table 16.1 shows a summary of the codes and their meanings.

Table 16.1. Pragma Restricitons

Purity Level

Code Description

Assertion

WNDS

Writes No Database State

No modification of any database table.

WNPS

Writes No Package State

No modification of any packaged variable.

RNDS

Reads No Database State

No reading of any database table.

RNPS

Reads No Package State

No reading of any package variables.

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

Answer: No. You added an update statement and violated the purity level of the pragma restriction WNDSwrites no database state. You receive the following error message when you try to compile the new package:

 Errors for PACKAGE BODY SCHOOL_API:  LINE/COL ERROR  -------- -------------------------------------------- 0/0      PL/SQL: Compilation unit analysis terminated  60/2     PLS-00452: Subprogram 'TOTAL_COST_FOR_STUDENT'                      violates its  associated pragma 

16.1.3 Answers

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

Answer: No, you have not created Frankenstein, it's just an overloaded procedure. The specification has two procedures with the same name and different IN parameters both in number and in datatype. The OUT parameters are also different in number and datatype. This overloaded function accepts either of the two sets of IN parameters and performs the version of the function corresponding to the datatype passed in.

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

Answer: 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 zipcode of the student. If it finds them, they are returned as well as a return code of 0. If they cannot be found, then null values are returned and 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 zipcode, and a return code of 0. If a match is not found, then the values returned are null as well as an exit code of -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 as follows: (1) Overloading simplifies the call interface of packages and reduces many program names to one. (2) Modules are easier to use and hence more likely to be used. The software determines the context. (3) The volume of code is reduced because code required for different datatypes is often the same.

graphics/intfig07.gif

The rules for overloading are as follows: (1) The compiler must be able to distinguish between the two calls at runtime. Distinguishing between the uses of the overloaded module is what is important and not solely the spec or header. (2) The formal parameters must differ in number, order, or datatype family. (3) You cannot overload the names of standalone modules. (4) Functions differing only in RETURN datatypes cannot be overloaded.

c)

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

A3:

Answer: A suitable bride for Frankenstein is as follows:

 SET SERVEROUTPUT ON  PROMPT ENTER A student_id  ACCEPT p_id  PROMPT ENTER a differnt student's first name surrounded  PROMPT by quotes  ACCEPT p_first_name  PROMPT Now enter the last name surrounded by quotes  ACCEPT p_last_name  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; 

It is important for you to realize the benefits of using a && variable. The value for the variable need only be entered once, but if you run the code a second time, you will not be prompted to enter the value again since it is now in memory.

graphics/intfig07.gif

Here are a few things 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 datatype 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 get the following message at runtime:

 PLS-00307: too many declarations of '<program>' match this  call 

Lab 16.1 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 16.1.

1)

What is the purpose of the USER_ERRORS view?

  1. _____ It prevents you from having to make use of the SHO ERR command.

  2. _____ It has the details on database objects in an invalid state.

  3. _____ It is a record of all compilation errors you have ever made.

  4. _____ It has no purpose but to take up database space.

2)

The DESC command behaves like an overloaded procedure.

  1. _____ True

  2. _____ False

3)

All functions require a pragma restriction to be used in an SQL statement.

  1. _____ True

  2. _____ False

4)

What does the purity level of a pragma restriction mean?

  1. _____ It refers to whether it is kosher or not.

  2. _____ It tells you if the function can be used in a SELECT statement.

  3. _____ It shows the effect executing the function will have on other objects in the database or the package.

  4. _____ It tells you if the function is overloaded.

5)

What is the principal benefit of an overloaded function?

  1. _____ An overloaded function is able to bypass any pragma restriction.

  2. _____ An overloaded function behaves differently depending on the type of data passed in when it is called.

  3. _____ It is just a lot of hypeoverloaded functions have no benefit.

  4. _____ An overloaded function is like a ghost function.


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net