PROCEDURE calc_profits
(company_id_in IN NUMBER,
profit_type_in IN VARCHAR2 := 'NET');
If I try to use this procedure in Oracle Forms, I must include a value for both parameters, even if I use
the default:
calc_profits (:company.company_id, 'NET');
Suppose I now create the following local procedure (either in the form itself or in a PL/SQL library):
PROCEDURE of_calc_profits
(company_id_in IN NUMBER,
profit_type_in IN VARCHAR2 := 'NET')
IS
BEGIN
calc_profits (company_id_in, profit_type_in);
END;
I can now call this procedure using the default value of NET, since the Oracle Forms version of the
procedure always passes a value to the stored procedure version:
of_calc_profits (:company.company_id);
(In Version 7.1 of the Oracle Server, by the way, you will be permitted to use default values in
remote procedure calls.)
This situation with Oracle Server Version 7 reinforces one of my suggestions regarding default
parameter values: always specify a value for each of the module's parameters, even if a value is the
same as the default. You will find yourself less vulnerable to problems down the line.
Previous: 22.6 Construct
Abstract Data Types
(ADTs)
Oracle PL/SQL
Programming, 2nd Edition
Next: 23. Managing Code
in the Database
22.6 Construct Abstract Data
Types (ADTs)

●
The database manages dependencies between your stored objects. For example, if a stored
function relies on a certain table and that table's structure is changed, the status of that
function is automatically set to INVALID and recompilation takes place, again automatically,
when someone tries to execute that function.
●
Any session with EXECUTE authority can execute the stored object. Whether you are in an
Oracle Forms application, a database trigger, or a batch process, that stored object is
accessible as long as the database is accessible. The compiled code for the object is made
available from the shared memory of the database instance.
●
You can execute a module stored in another (remote) database. This is known as a remote
procedure call, or RPC. The ability to execute code on a different database means that you do
not have to distribute application code to multiple databases in order to support distributed
applications.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
●
Stored modules offer transaction-level security, which goes well beyond the table- and
column-level data and referential integrity offered by the database. With a stored module you
can make sure that transactions involving multiple tables and different steps are always
performed properly. This was actually one of the original motivations for the development of
the PL/SQL language.
●
Execution of a stored object shifts CPU load to the server. Rather than run your code locally
in a form or report, the stored object executes on the database server. Assuming that the server
is sized to handle the load, this shift could improve overall application performance. This
feature could be less of a benefit, of course, if the server is already heavily burdened.
Stored objects play an important role in today's client-server applications. The information in this
chapter will help you make the best possible use of stored objects.
23.1 Executing Stored Code

calc_totals;
END;
END-EXEC;
23.1.2 Executing Functions
The situation with functions is a little bit different. A function returns a value, so you can't just
execute it directly. If you try to run a function as if it is a procedure, you will get the following error:
SQL> exec total_sales (1997)
begin total_sales (1997); end;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TOTAL_SALES' is not a procedure or is
undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
If you want to run a function simply to view its return value, you can execute it within a call to
DBMS_OUTPUT.PUT_LINE as follows:
SQL> exec DBMS_OUTPUT.PUT_LINE (total_sales (1997))
Of course, this only works if the datatype of the return value of the function matches one of the
datatypes supported by DBMS_OUTPUT: number, string and date. For anything else, you will need
to execute the function within an anonymous block and then convert the value as necessary for
display. In the following example, I declare a PL/SQL table of strings to retrieve the value from a
function that returns all the foreign key information for a table. I then use a FOR loop to display that
information:
/* Employs PL/SQL 2.3 syntax for PL/SQL tables. */
DECLARE
TYPE strtabtype IS TABLE OF VARCHAR2(100) INDEX BY
BINARY_INTEGER;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
strtab strtabtype;

●
Retrieve data from the database and deliver it to user processes (a PL/SQL procedure
executing an explicit cursor, an Oracle Forms application querying records through a base
table block, an Internet HTML page utilizing the Oracle WebServer interface, and so on).
●
Change data and then manage the transaction processing (commits and rollbacks) based on
those changes.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
●
Provide access to PL/SQL programs for execution in the server itself.
●
Transfer information between Oracle sessions using database pipes and database alerts.
When you use a stored program element (by executing a procedure or function or by making
reference to a nonexecutable part of a package like a constant or cursor), it must be accessible from
the SGA. So when you reference a program element, its compiled version is loaded into the SGA's
shared pool, if it is not already present. Then any program data associated with that element is
instantiated in your Program Global Area (PGA). At that point, the code can be executed or otherwise
used in your session.
The next time a session with execute authority on that program element references it, it will already
have been loaded and will therefore be available for execution.
When the SGA shared pool area fills up, code in the pool will be aged out (that is, room will be made
for newly requested programs) on a least-recently-used basis. Programs which are used often will
therefore most likely be available immediately upon request. You can also pin programs into shared
memory if you want to make certain that they will not be aged out. See
Chapter 25, Tuning PL/SQL
Applications, for more details on pinning.
Note that even if your compiled code is aged out of the SGA, any persistent, package-based data will
remain available in your PGA. In other words, code may be swapped out of the SGA, but program
data remains and persists for the duration of your session (or until you explicitly request a
reinitialization of your schema).

NOTE: The architecture in the multi-threaded server (MTS) is a bit different, but the
principle is the same: different users have their own copies of their program data.
●
Stored code runs under the authority of that code's owner, not that of the schema which is
executing the code. Suppose that you have stored all the code for your order entry application
in the SYSTEM account (a very bad idea, as this example will illustrate). You then grant
EXECUTE authority on that code to SCOTT, the traditional Oracle demonstration account,
which has very little power outside of doing whatever it wants with the emp and dept tables.
Now suppose further that the delall procedure of the ordent package allows you to delete all
orders from the application table. The intention is that only administrators will have the ability
to do that. In fact, the only account that can delete from the orders table at all is SYSTEM.
Once you have granted EXECUTE authority on ordent to SCOTT, however, all heck can very
decidedly break loose. For when a user logged in as SCOTT executes the ordent.delall
procedure, that user is, for that moment, acting as though she were connected to SYSTEM. All
rows are removed from the orders table!
You must, consequently, very careful about (a) the authority of the schema owning your
shared application code, and (b) to whom you grant EXECUTE authority on that code.
●
Even though stored code runs under the authority of that code's owner, the USER function still
returns the name of the schema currently connected to Oracle. The USER function does not,
in other words, return the name of the schema which owns the code in which the USER
function was called.
Previous: 22.7 Tips for
Parameter Design
Oracle PL/SQL
Programming, 2nd Edition
Next: 23.2 Transaction
Integrity and Execute
Authority
22.7 Tips for Parameter

successfully or is completely rolled back, regardless of who executes the process.
23.2.1 Execute Authority on Stored Objects
The secret to achieving this level of transaction integrity is the concept of execute authority (also
known as run authority). Instead of granting to a role or user the authority to update a table, you grant
privileges to that role/user only to execute a procedure. This procedure controls and provides access
to the underlying data structures (see
Figure 23.2). The procedure is owned by a separate Oracle
RDBMS account, which in turn is granted the actual update privileges on those tables needed to
perform the transaction. The procedure therefore becomes the gatekeeper for the transfer transaction.
The only way a program (whether an Oracle Forms application or a Pro*C executable) can execute
the transfer is through the procedure, so the transaction integrity can be guaranteed.
Figure 23.2: Transaction integrity with a PL/SQL code layer
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In order for a stored procedure or package to compile (which occurs at the time of creation or
replacement), the owner of that program must explicitly be granted all the necessary privileges to any
objects referenced by the program. These privileges may not be granted simply to a role. If, for
example, procedure disp_customer issues a SELECT statement against the customer table, then the
owner of disp_customer must be granted a minimum of SELECT privileges on that table with an
explicit command:
GRANT privilege ON customer TO procedure_owner;
Requiring direct grants to individual Oracle users sometimes causes difficulty in environments where
grants are controlled carefully and efficiently through the use of roles. After all, the whole idea of the
role is to allow DBAs to move away from the onerous task of directly granting privileges to a myriad
of individual users. Yet every one of those users must execute the stored procedures underlying an
application. What's a DBA to do?
In some Oracle shops, a single account (user), which I'll call STOROBJ, is created in the production
environment. This user owns all stored objects and has update privileges on all tables, as is
appropriate. Other people who use the applications might have SELECT privileges on a number of
tables, and perhaps even update privileges on certain tables that are maintained through Oracle Forms
applications. But all complex transactions are bundled into stored procedures and stored functions,

working with stored objects, you should create synonyms, as follows:
SQL> CREATE PUBLIC SYNONYM xfer_funds FOR storobj.
xfer_funds;
Now I can call the transfer procedure without making reference to its owner:
xfer_funds (:old_acct, :new_acct, :xfer_amt, :xfer_stat);
While it is possible to create a synonym for a standalone procedure, it is not possible to create a
synonym for a packaged procedure or function. You can, however, create a synonym for the package
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
itself. In effect, synonyms can be used to avoid having to provide the schema name of an object; you
cannot use it to avoid specifying the package name in which a program is defined.
Previous: 23.1 Executing
Stored Code
Oracle PL/SQL
Programming, 2nd Edition
Next: 23.3 Module
Validation and Dependency
Management
23.1 Executing Stored Code
Book Index
23.3 Module Validation and
Dependency Management
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.2 Transaction
Integrity and Execute
Authority
Chapter 23

the database.
23.3.1 Interdependencies of Stored Objects
A stored object must be VALID in order for its pcode to be loaded into the shared pool and executed
by the host program. As noted above, if the compile succeeds at create/replace time, then the status is
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
set to VALID. This status may, however, depend on other objects. Consider the following function:
FUNCTION full_name (employee_id_in IN NUMBER) RETURN
VARCHAR2
IS
first_and_last VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name
INTO first_and_last
FROM employee
WHERE employee_id = employee_id_in;
RETURN first_and_last;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
Suppose that on Monday I save this function to the database. It compiles successfully and its status is
set to VALID. The PL/SQL compiler also adds a record in the DEPENDENCY$ table to indicate that
full_name is dependent on the employee table. Then on Tuesday, the DBA team adds another column
to the employee table. The Oracle Server automatically checks the dependencies for the employee
table and sets the status of all dependent objects to INVALID. This is a recursive process. Once
full_name is set to INVALID, then any modules calling full_name are also set to INVALID.[
1]
[1] An important exception to this "chain reaction" occurs with packaged modules. If,
for example, full_name was defined within a package called, say, "employee", then

FROM user_objects
WHERE object_type IN
('PACKAGE', 'PACKAGE BODY', 'FUNCTION',
'PROCEDURE')
AND status = 'INVALID';
Output from this query might look like:
ALTER PACKAGE PSGLOBAL COMPILE;
ALTER FUNCTION FULL_NAME COMPILE;
You could also force recompilation of a module by using the ALTER_COMPILE procedure in the
DBMS_DDL package (see
Appendix A, What's on the Companion Disk?, for more details), as
follows:
DBMS_DDL.ALTER_COMPILE ('package', 'SCOTT', 'FULL_NAME');
It is easy, when you use stored objects, to take for granted automatic management of these objects by
the database. You might scarcely notice the status of your objects and the recompilations that take
place behind the scenes.
That's cool. That's the whole idea. As you move your applications into production, however, you
would be well served to remember how the database works with your stored objects. That way, when
something goes wrong (very slow execution time under certain circumstances, for example), you
have some idea of how to fix it.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.2 Transaction
Integrity and Execute
Authority
Oracle PL/SQL
Programming, 2nd Edition
Next: 23.4 Remote
Procedure Calls
23.2 Transaction Integrity
and Execute Authority

program is much more vulnerable to breakage. The very nature of a remote module call indicates that
the code can be moved to a different remote database. Never let a programmer hardcode such
information in a program.
When you call a remote module and it raises an exception, that exception is passed back as raised to
the calling program -- for all but user-defined exceptions. If your remote module raises a
NO_DATA_FOUND exception, your exception handler traps that error. If, on the other hand, your
module uses RAISE_APPLICATION_ERROR to raise a custom exception (error numbers between -
20000 and -20999), then that specific exception is lost in the translation back to the calling program.
Instead, error ORA-06510 is always returned to the local program.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.3 Module
Validation and Dependency
Management
Oracle PL/SQL
Programming, 2nd Edition
Next: 23.5 Managing
Stored Objects with
SQL*Plus
23.3 Module Validation and
Dependency Management
Book Index
23.5 Managing Stored
Objects with SQL*Plus
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.4 Remote
Procedure Calls

functions, and package bodies is kept in the SOURCE$ table (owned by SYS). Views into the
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SOURCE$ table are:
ALL_SOURCE
Source for all objects to which you have access, and therefore EXECUTE privilege on that
object
USER_SOURCE
Source for all objects you have created
The statements executed by a trigger are stored in the ACTION column of the TRIGGER$ table
(owned by SYS). You can view all triggers you created by accessing USER_TRIGGERS, or all
triggers to which you have access by viewing the contents of ALL_TRIGGERS.
Even though you store your modules in the database, you do not maintain your source code for these
modules directly in their data dictionary tables. Instead, you must always keep the source code in text
files. If you need to make a change to an object, make the changes in the source code and then
replace the object in the database.
In order to distinguish regular SQL*Plus scripts from scripts that maintain stored objects, I employ a
different file extension for them, as shown in the following table:
File Type Extension
General SQL*Plus script <file>.sql
Testing script <file>.tst
Stored procedure <file>.sp
Stored function <file>.sf
Stored package body <file>spb
Stored package specification <file>.sps
23.5.2 Tips for Storing Code in Files
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.