| Lab Objectives After this Lab, you will be able to: 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?  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. |
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? 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.  | 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: The function must be stored in the database (not in the library of an Oracle tool). The function must be a row-specific function and not a column or group function. As for all functions (whether to be used in SQL statements or not), parameters must be the IN mode. 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. | | 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. 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? _____ It prevents you from having to make use of the SHO ERR command. _____ It has the details on database objects in an invalid state. _____ It is a record of all compilation errors you have ever made. _____ It has no purpose but to take up database space.
| | 2) | The DESC command behaves like an overloaded procedure. _____ True _____ False
| | 3) | All functions require a pragma restriction to be used in an SQL statement. _____ True _____ False
| | 4) | What does the purity level of a pragma restriction mean? _____ It refers to whether it is kosher or not. _____ It tells you if the function can be used in a SELECT statement. _____ It shows the effect executing the function will have on other objects in the database or the package. _____ It tells you if the function is overloaded.
| | 5) | What is the principal benefit of an overloaded function? _____ An overloaded function is able to bypass any pragma restriction. _____ An overloaded function behaves differently depending on the type of data passed in when it is called. _____ It is just a lot of hypeoverloaded functions have no benefit. _____ An overloaded function is like a ghost function.
|
|
