Oracle Built−in Packages- P9: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Suppose that my formula for calculating total compensation for an employee is salary plus commission. Here is that formula implemented in PL SQL CREATE OR REPLACE FUNCTION totcomp sal_in IN NUMBER comm_in IN NUMBER RETURN NUMBER IS BEGIN RETURN sal_in NVL comm_in 0 END Once this program is stored in the database I can call it from within a query as follows SQL SELECT ename totcomp sal comm total_compensation FROM emp ENAME TOTALCOMPENSATION SMITH 800 . MILLER 1300 You can also call a packaged function from within a SQL statement. In this case however you must also provide a special statement the RESTRICT_REFERENCES pragma to enable that function for use inside SQL. Here for example is the code you would have to write to place totcomp inside a package and still call it from a query CREATE OR REPLACE PACKAGE empcomp IS FUNCTION totcomp sal_in IN NUMBER comm_in IN NUMBER RETURN NUMBER PRAGMA RESTRICT_REFERENCES total WNDS RNDS WNPS RNPS END CREATE OR REPLACE PACKAGE BODY empcomp IS FUNCTION totcomp sal_in IN NUMBER comm_in IN NUMBER RETURN NUMBER IS BEGIN RETURN sal_in NVL comm_in 0 END END The line in bold is the statement asserting that the function does not violate any of the restrictions on functions in SQL. Here is how you would call this packaged function inside SQL SQL SELECT ename sal comm total_comp from emp ENAME TOTALCOMP SMITH 800 . MILLER 1300 The same rules apply for built-in packaged programs callable from SQL. Oracle Corporation itself must provide a RESTRICT_REFERENCES pragma in its own package specifications for any procedure or function that is to be used from within SQL. And since Oracle did not pragmatize built-in packages prior to Oracle you will not be able to call built-in packaged programs from SQL directly or indirectly until you Accessing Built-in Packaged Technology from Within SQL 31 Appendix A What s on the Companion Disk install Oracle or later. If you
