3.6 Stored Programs
Programs written in PL/SQL can be stored in compiled form in the database. These programs are referred to as either procedures or functions . The only difference is that, by definition, a function must return a value, while a procedure does not have to return a value. However, there is no problem in coding a procedure to return a value. Stored programs can be executed by table triggers, applications, or users. In this book, we use the phrase "stored program" or "program" to refer to both procedures and functions.
Stored programs can be created using either of two methods . You can create the program simply as a program, or you can create it in two parts : a package that specifies the programs to be implemented, and a package body containing the actual code.
3.6.1 Executing a Procedure or Function
Users or applications can execute stored programs provided that EXECUTE permission on the program has been granted. Unlike table triggers, procedures and functions are executed by an explicit call. For example, suppose a procedure called give_raise has been written and requires an employee number and percentage of raise as arguments. The call might then look like this:
SQL> EXECUTE give_raise(8138, 23); PL/SQL procedure successfully completed
Executing a function interactively is a bit more complex, as a function always returns a value and the calling statement structure must be able to receive the value. Stored functions can be included in SQL statements. The position in the SELECT statement satisfies the requirement for a place to receive the return value from the function. However, since only a single value is returned and not a set of values, this is generally useful only in SQL statements that are expected to return only one row.
3.6.2 Why Use Packages?
A package is a method of creating a program in two parts, the specification and the body. There are several advantages to using the package approach. One of these is overloading . Overloading means that one program name can be used more than once as long as the formal argument parameter list is different. So, if a program in a package is created as:
give_raise(empno IN NUMBER, pct IN NUMBER)
then it can be declared again as either of the following:
give_raise (empno IN NUMBER, pct IN NUMBER, effdate IN DATE) give_raise (ename IN VARCHAR2, pct IN NUMBER).
Since the argument lists are all different, the three programs can exist with the same name in the package. The PL/SQL engine will figure out which program within the package to use based on the arguments provided in the call.
The second advantage to using packages is the ability to avoid dependent invalidation . If the give_raise stored procedure is called by other stored procedures and is then changed, the other stored procedures are invalidated and must be recompiled. However, if the package/package body approach has been used, then as long as the package is not recompiled the dependent programs will remain valid. This allows the package body to be developed and changed without the inconvenience of having to find and recompile all the dependent programs. The assumption here is that the specification in the package does not change. If the specification in the package does change, the dependent programs will of course have to be recompiled. That, however, does not happen as frequently as recompiling of the package body occurs.
3.6.3 Using the PL/SQL Wrapper
We have stressed the advantages to using PL/SQL procedures and packages but, until version 7.2, there was one very distinct disadvantage . You might need to deliver your application code outside of your own area or company. In order to install the PL/SQL code, the source code must CREATE OR REPLACE the programs. Anyone could see the source code by accessing the view ALL_SOURCE. Value-added resellers (VARs) registered their dissatisfaction at having their proprietary source code visible to customers and competitors . Oracle responded by adding a utility which can be used to effectively encrypt or "wrap" the PL/SQL source code. This utility is generally referred to as the PL/SQL Wrapper. 
 For more detailed information about using the PL/SQL Wrapper, see Chapter 23 of Steven Feuerstein and Bill Pribyl's Oracle PL/SQL Programming , second edition (O'Reilly & Associates, 1997).
When you run the utility, your readable ASCII text source code is converted into unreadable ASCII text source code. The unreadable code can then be delivered anywhere for creation in any Oracle version 7.2 or higher database, and its contents are secured. The code is treated in the database like normal PL/SQL code and is just as portable and usable as unencrypted code. The utility is actually an executable file which is usually located in the $ORACLE_HOME/bin directory on a UNIX system and in the c:/orant/bin directory on a Windows NT system. The executable file is named wrapNN.exe where the NN represents the RDBMS version number. For a version 7.3 database, the executable will be named wrap73.exe .
To run the utility to encrypt your PL/SQL code, you would type the following:
wrapNN iname=readable_filename [oname=encrypted_filename]
where NN is the version number of your RDBMS (72, 73, 80, etc.); the readable_filename points to your original source code file; and the encrypted_filename points to the output file name which you want the encrypted file to have. If you do not specify an extension, .sql is assumed, and if you do not supply an oname value, the original filename will be used for the output file with the extension .plb for "PL/SQL binary." Here is an example of using the wrap73 utility to encrypt a file named my_plsql.sql on a Windows NT machine:
Using all default values:
Using explicit, overriding values:
c:\orant\bin\wrap73 iname=my_plsql.sql oname=hideit.sec
3.6.4 Procedure Ownership and Privileges
It is very important for you to understand the relationship between the owner of a stored procedure and the user who executes the procedure. In general, stored procedures execute on behalf of the user, but use the privileges of the creator. Also, synonyms (described later in this chapter) are resolved by the owner of a stored procedure , not by the user. For ease in maintenance, a good policy to follow would be to ensure that the schema owner also owns all stored procedures, including triggers associated with that schema's objects. There may, however, be some cases where it makes sense to have another owner for the stored procedure. For development purposes, this approach makes sense because each developer can make changes and test the same procedure without breaking the application.
If the creator does not own the tables that will be read or modified by the stored program, is there a problem? Well, let's take an example, walk through it, and see for ourselves . Suppose mary is the schema owner and has granted SELECT and UPDATE privileges on the employee table to ralph .
GRANT SELECT, UPDATE ON employee TO ralph;
User ralph then writes a stored program to update the employee number of an employee record that exists in mary 's table.
SQL> CREATE OR REPLACE PROCEDURE test AS 2 e_no number; 3 BEGIN 4 SELECT employee_num 5 INTO e_no 6 FROM employee 7 WHERE ename = 'MILLER'; 8 DBMS_OUTPUT.PUT_LINE ('e_no is 'e_no); 9 UPDATE mary.employee 9 SET employee_num = 1154 10 WHERE employee_num = 7934; 11 SELECT employee_num 12 INTO e_no 13 FROM employee 14 WHERE employee_name = 'SUE'; 15 DBMS_OUTPUT.PUT_LINE ('e_no is 'e_no); 16 END; 17 / Procedure created.
| || |
The concatenation of the numeric variable e_no with the character string "e_no is " will not produce an error as it does in some versions of PL/SQL used in Oracle Forms. The conversion of the numeric value to character is automatic.
Since this is not the real world, the procedure makes little sense. However, we have tested the code to verify that it works correctly, so we will assume here that ralph tests the stored program and everything works to his specifications.
The user ed , who does not have privileges on mary 's employee table, runs the application that will attempt to update a record in the employee table. The stored program will succeed!
SQL> GRANT EXECUTE ON test TO ed; Grant succeeded. SQL> connect ed/miok4u? Connected. SQL> set serveroutput on SQL> execute ralph.test e_no is 7934 e_no is 1154 PL/SQL procedure successfully completed.
Why did this happen? The user mary granted privileges to ralph . The stored program worked for ralph because he has the appropriate privileges on the employee tables. ed , on the other hand, has no privileges on the employee table , but the procedure still worked. When the program ran, even though ed was recognized as the user, the procedure was owned by ralph . The Oracle database knew that ralph could perform updates to the employee table and carried that privilege over to ed . This is a really cool feature of Oracle!
| || |
The user who runs a procedure takes on the privileges of the procedure, package, or function owner!
3.6.5 Privileges, Procedures, and Roles
There is one "gotcha." Privileges granted via roles are not understood by stored programs. You cannot grant privileges to a role, grant that role to a user, and have the user successfully run the stored program with access determined by the role privileges. This is yet another argument for the schema owner to own all programs.
There are two categories of stored programs: those supplied by Oracle and those which are custom-written. We recommend that both DBAs and developers look in the Oracle home directory under the RDBMS/ADMIN directory for the DBMS_<NAME>.SQL scripts. These scripts create most of the stored packages available in the database. One of these, DBMSUTIL.SQL , contains the DBMS_SESSION package that we will use in Chapter 9.
| || |
The DBA should take the time to carefully walk through the DBMSUTIL.SQL script since there are many utilities of interest within this file.