 |
|
Alteration of Procedures and Functions
Oracle Database Tips by Donald Burleson |
To alter the logic or variables of a procedure
or function, use the CREATE OR REPLACE form of the command in the
previous subsection. The only option for the ALTER command for
functions and procedures is COMPILE. This option recompiles the
procedure or function after a modification to their referenced objects
has occurred. The format of this command is available in the SQL
Reference Manual, at the documentation website,
www.technet.oracle.com.
***If a function or procedure that has been
invalidated by a change to a table, view, or other referenced
procedure or function is called, it is automatically recompiled.
Whenever possible, explicit recompilation via the ALTER command should
be used. This will pinpoint any problems before the users find them
for you. The script in Source 7.7 will provide a list of invalid
database objects upon execution. Use this script to pinpoint which
packages, procedures, functions, or views need to be recompiled.
SOURCE 7.7 Example of a script to check on
database object status.
rem Name: inv_obj.sql
rem Purpose: Show all invalid objects in database
rem Mike Ault 7/2/96 TUSC
rem
COLUMN object_name FORMAT A30 HEADING 'Object|Name'
COLUMN owner
FORMAT a10 HEADING 'Object|Owner'
COLUMN last_time FORMAT a20 HEADING
'Last Change|Date'
SET LINES 80 FEEDBACK OFF PAGES 0 VERIFY OFF
START title80 'Invalid Database Objects'
SPOOL rep_out/&db/inv_obj
SELECT
owner,
object_name,
object_type,
TO_CHAR(last_ddl_time,'DD-MON-YY hh:mi:ss') Last_time
FROM
dba_objects
WHERE
status='INVALID'
/
PAUSE Press Enter to continue
SET LINES 80 FEEDBACK ON PAGES 22 VERIFY ON
CLEAR COLUMNS
TTITLE OFF
Dropping Procedures or Functions
Periodically, a DBA may be required to remove
a function or procedure from the database. This is accomplished with
the DROP command. The format of this command follows:
DROP
PROCEDURE|FUNCTION [schema.]function_name|procedure_name;
This will invalidate any related functions or
procedures, and they will have to be recompiled before use.
Administration of Packages
Under Oracle7, Oracle8, Oracle8i, and
Oracle9i, packages are collections of related functions, variables,
procedures, and external calls to functions and procedures. All of the
functions and procedures for a specific application can be grouped
under one or more packages and handled as units. A package is loaded
into shared memory whenever one of its parts is referenced. The
package stays in memory until the least recently used (LRU) algorithm
determines it hasn't been recently used. You, as DBA, can force an
object to stay in the SGA by ?pinning? it. (Object pinning is covered
in Chapter 13.) This use determination applies to all database users,
not just the originating user.
Packages allow public and private functions,
procedures, and variables. Public functions, procedures, and variables
are named in the package definition and are available to all users
with the right to access the package. Private procedures, functions,
and variables are not referenced in the package definition, but are
contained in the package body. Private procedures, functions, and
variables are only referenced by the package internal objects.
External functions and procedures were new with Oracle8.
As hinted at above, the package consists of
two possible parts, a definition and a body, each of which is created
separately. The package definition contains the names of all public
functions, procedures, and variables; the package body contains the
PL/SQL and SQL code for all of the public and private package objects.
In the case of a package that has no private functions, procedures, or
variables, no package body is required. However, each of the
referenced public objects must exist. Not using private objects allows
the DBA and developers to maintain the individual objects separately
instead of as a single entity. If a package has private objects, it
must have a body.
If the DBA has enforced use of script files to
create database functions and procedures, creating the package body
involves simply concatenating the various scripts together and making
minor changes to the syntax of the statements. By the use of the
DBA_SOURCE view, the DBA can use dynamic SQL to create script
listings. An example of this is shown in Source 7.8.
SOURCE 7.8 Example of a script to rebuild
function and procedure or package objects.
rem
*****************************************************
rem NAME: FPRC_RCT.sql
rem HISTORY:
rem Date Who
What
rem ???? ?????? ??????????
rem 05/22/93 Michael Ault
Created
rem FUNCTION: Build a script to re-create functions,
rem
procedures, packages, or package bodies.
rem *******************************************************
SET VERIFY OFF FEEDBACK OFF LINES 80 PAGES 0 HEADING OFF
SPOOL cre_fprc.sql
SELECT 'CREATE '||s1.type||' '||s1.owner||'.'||s1.name,
substr(s2.text,1,80)||';'
FROM
dba_source s1,
dba_source s2
WHERE
s1.type = UPPER('&object_type') AND
s1.owner = UPPER('&object_owner') AND
s1.type = s2.type AND
s1.owner = s2.owner AND
s1.name = UPPER('&object_name') AND
s1.name = s2.name
GROUP BY
s1.owner,
s1.name
ORDER BY
s2.line;
rem
SPOOL OFF
This will create one large file with all of
the types of objects that the DBA specifies for a given owner. This
script can also be used to document existing package definitions and
package bodies.
Let's now look at the processes and commands
used to administer packages.
Creation of Packages
Package creation involves up to two steps. The
first step, creation of the package definition (or header), is
required for all packages. The second step, creation of the package
body, is required only for those packages that have private
components. If the use of functions stored in a package is required
from outside the package, the functions? purity level must be
explicitly stated using the PRAGMA RESTRICT_REFERENCES call, which has
the following definition:
PRAGMA
RESTRICT_REFERENCES( function_or_procedure, rest_code );
where:
Function_or_procedure. The name of the
function or procedure to which the PRAGMA is to be applied.
Rest_code. One or more of WNDS, RNDS,
WNPS, RNPS, which function as follows:
WNDS. Writes no database state (purity
rules 2 and 4).
RNDS. Reads no database state (purity
rules 2, 3, and 4).
WNPS. Writes no package state (purity
rules 1 and 4).
RNPS. Reads no package state (purity
rules 1 and 4).
The purity rules, as enforced through the
PRAGMA calls for functions and procedures, are:
1. They cannot insert into,
update, or delete from a database table.
2. They cannot be executed
remotely or in parallel if they read or write the values of packaged
variables
3. They cannot write the
values of packaged variables unless called from a SELECT, VALUES, or
SET clause.
4. They cannot call another
function, method, or subprogram that breaks one of these rules, nor
can they reference views that break any of these rules. (Oracle
replaces references to a view with a stored SELECT operation, which
can include FUNCTION calls.)
If a stored object (method, procedure, or
function) violates its declared PRAGMA level, an error will be
returned and the object will be invalid.
The command for creating package definitions
follows:
CREATE [OR
REPLACE] PACKAGE [schema.]package_name
[invoker_rights_clause] IS|AS
PLSQL_package_spec;
where:
OR REPLACE. Used when the user wishes
to create or replace a package. If the package definition exists, it
is replaced; if it doesn't exist, it is created.
schema. The schema in which the package
is to be created. If this is not specified, the package is created in
the user's default schema.
Package_name. The name of the package
to be created.
PLSQL_package_spec. The list of
procedures, functions, or variables that make up the package. All
components listed are considered to be public in nature.
TIP: You can insert the PRAGMA
RESTRICT_REFERENCES call into Oracle-provided package headers for
functions that you need to use outside of those packages; however, it
may not always work and is not supported by Oracle.
For example:
CREATE
PACKAGE admin.employee_package
AS
FUNCTION new_emp(ename CHAR, position CHAR, supervisor NUM,
category NUM, hiredate DATE)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES( new_emp, WNDS);
FUNCTION fire_them(ename CHAR, reason VARCHAR2, term_date DATE)
RETURN DATE;
PROCEDURE new_dept(ename CHAR, dept CHAR, new_dept CHAR,
date_of_change DATE);
bad_category EXCEPTION;
bad_date EXCEPTION;
END employee_package;
This code creates the package employee_package.
The package contains the functions new_emp and fire_them, the
procedure new_dept, and the exceptions bad_category and bad_date. All
of the objects are available to whomever has privileges on
employee_package. Notice the PRAGMA call that sets the purity level of
new_emp as Writes No Database State (WNDS).
Creation of the Package Body
The package body contains all of the SQL and
PL/SQL scripts that make up the procedures, functions, exceptions, and
variables used by the package. If the package contains only public
items, a package body may not be required. The format for the CREATE
PACKAGE BODY command follows:
CREATE [OR
REPLACE] PACKAGE BODY [schema.]package_name
IS|AS PL/SQL package body;
where:
OR REPLACE. When used, if the package
body exists, it is replaced; if it doesn't exist, it is created.
schema. Specifies the schema in which
to create the package. If this is not specified, the package body is
created in the user's default schema.
PL/SQL package body. The collection of
all of the SQL and PL/SQL text required to create all of the objects
in the package.
Source 7.9 is an example of the use of the
CREATE PACKAGE BODY command. The exceptions listed in the
package definition are contained within the procedures.
SOURCE 7.9 Example of a format for package
body.
CREATE OR
REPLACE PROCEDURE BODY admin.employee_package AS
FUNCTION new_emp(ename CHAR, position CHAR, supervisor NUM,
category NUM, hiredate DATE)
RETURN NUMBER IS
emp_number number(5);
BEGIN
.
.
.
END;
FUNCTION fire_them(
ename CHAR,reason VARCHAR2,term_date DATE)
RETURN NUMBER AS
years_of_service NUMBER (4,2);
BEGIN
.
.
.
END;
PROCEDURE new_dept(ename CHAR, dept CHAR, new_dept CHAR,
date_of_change DATE)
IS
BEGIN
.
.
.
END;
END employee_package
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
|
and include the URL for the page.
