 |
|
Alteration of Triggers
Oracle Tips by Burleson
|
As was stated in the “Creation of Database
Triggers” section preceding, the CREATE command has the OR REPLACE
option to allow a trigger to be re-created without being dropped. Also
available is the COMPILE [DEBUG] option that allows recompilation and
debug of a trigger that has become invalidated. To alter the contents
of a trigger, this create or replace option is used. A trigger has one
of two possible states: ENABLED or DISABLED. The ALTER TRIGGER command
is limited in functionality:
ALTER
TRIGGER [schema.]trigger_name ENABLE|DISABLE|COMPILE [DEBUG];
One limit on the usefulness of the ALTER TABLE
in either disabling or enabling triggers is that it is an
all-or-nothing proposition. It is better to use the ALTER TRIGGER
command, unless you want all of the triggers on the table enabled or
disabled at one time.
The DEBUG option instructs the PL/SQL compiler
to generate and store the code for use by the PL/SQL debugger.
Dropping a Trigger
Triggers are dropped using the DROP TRIGGER
command:
DROP TRIGGER
[schema.]trigger_name;
Tip: Give careful consideration to all
triggers created after Oracle8. Ask whether its function could be
better accomplished with a method. If so, use a method. Check for
trigger dependencies before dropping a trigger or significantly
altering a trigger’s actions.
Administration of Functions and Procedures
New in Oracle8 was the advance typing that
allows a PL/SQL table in functions and procedures to be
multidimensional. In contrast, in Oracle7, a PL/SQL table had to be
scalar (a single datatype). Oracle8 also offers object support and
external procedure and function calls.
Functions and procedures under Oracle7,
Oracle8, Oracle8i, and Oracle are virtually identical. The major
difference between functions and procedures is that functions always
return a single value, whereas procedures may return one, many, or no
values. This leads to a second difference: The procedure can use the
OUT and IN OUT arguments in the CREATE command, but the function
can’t. In fact, the function doesn’t have to specify the IN argument
since input to a function is required. Structurally, procedures and
functions didn’t change between versions Oracle7 and Oracle8; however,
in Oracle8i, the AUTHID clause (invoker_rights_clause) and the
DETERMINISTIC and PARALLEL_ ENABLE keywords were added, as well as the
capability to call C and Java objects. In Oracle, the capability was
added to return PL/SQL tables as either aggregates (must be returned
to a PL/SQL table) or PIPELINED (returns individual values).
Why Use Functions and Procedures?
The benefits of functions and procedures are
numerous. Functions and procedures provide a consistent means
of accessing, altering, and deleting database information. They allow
enhanced security by giving the DBA the ability to grant access to the
procedures and functions instead of to the actual tables or views. The
procedure or functions can have elevated privileges that are in effect
while the procedure or function is active but are disabled when it
completes.
Functions and procedures enhance productivity
by allowing a given process to be coded once and then referenced by
all developers. Instead of each form requiring coded triggers for data
access, the stored procedures and functions can be referenced instead.
This drives consistency down to the database level instead of
requiring it from each developer.
Performance is enhanced by allowing multiple
users to access the same shared image. Since the procedures and
functions are loaded into the cached memory area, only one I/O is
required for each procedure or function to be available to all users.
In network situations, a function or procedure can be called with a
single network call; the function or procedure can then trigger a
multitude of database actions and then return, via another single
call, the results, thus greatly reducing network traffic.
Tip: If a function or procedure affects
only one table, then, in Oracle8 and Oracle8i, perhaps it should be
made into a method. Methods should be used for any internal PL/SQL
objects that affect only one table or are used to obtain values from
only one table. Using a method instead of a procedure or function will
implement the concept of encapsulation in these cases.
Let’s look next at the administration of functions and procedures.
Creation of Functions and Procedures
Before a function or procedure can be created,
the DBA must run the CATPROC.SQL script. The CATPROC.SQL script is
usually run at database creation and, unless you upgrade, you
shouldn’t have to run it again. The user creating the function or
procedure must have the CREATE PROCEDURE privilege to create a
procedure or function in his or her own schema, or the CREATE ANY
PROCEDURE system privilege.
Any tables, clusters, or views used in the
creation of functions and procedures must have direct grants against
them issued to the developer who creates the function or procedure. If
this is not the case, the errors returned can be as informative as the
message that you can’t create a stored object with privileges granted
through a role, or as frustrating as the message, “ORA-0942—Table or
View doesn’t exist.”
A new entity known as an external function is
available under Oracle8, 8i, and 9i. The CREATE FUNCTION command is
also used to register this new entity to the database. External
functions are 3GL code functions stored in a shared library, which can
be called from either SQL or PL/SQL. To call an external function, you
must have EXECUTE privileges on the callout library where it resides
(this is an external operating system file and OS-level execute
permission, not Oracle internal level).
Functions and procedures are created using the
CREATE command. The format for each differs slightly, as shown in the
two subsequent subsections.
CREATE FUNCTION Command
The CREATE command syntax for functions is:
CREATE [OR
REPLACE] FUNCTION [schema.]function_name
[(argument [IN|OUT|IN
OUT] [NOCOPY] datatype)]
RETURN
datatype [invoker_rights_clause]
[DETERMINISTIC] [PARALLEL_ENABLE]
[[AGGREGATE|PIPELINED
USING] [schema.]Implementation_type
[PIPELINED
IS|AS] pl/sql function_body|external_call
IS|AS
Plsql_body|call_spec;
where:
argument. The name given to this
argument; this can be any valid variable name.
IN, OUT, or IN OUT. Specifies how the
argument is to be treated (strictly input, strictly output, or both).
This is optional and will default to IN if not specified.
datatype. The datatype of the argument;
this can be any valid scalar datatype.
DETERMINISTIC. Specifies the function
will always return the same value(s) from wherever it is called. If a
function is to be used in a function-based index, it must be
deterministic.
PARALLEL_ENABLE. Allows the function to
be processed using multiple parallel query servers.
AGGREGATE|PIPELINE. If the value
returned is a PL/SQL table, this tells Oracle to return as one large
value (AGGREGATE) or as individual values (PIPELINE); for more details
refer to the SQL manual.
The call_spec has the form:
LANGUAGE
java_declaration|C_declaration
The Java_declaration has the form:
JAVA NAME
'string'
The C_declaration has the form:
C [NAME
name] LIBRARY lib_name [WITH CONTEXT]
[PARAMETERS
(parameters)]
The parameters have the form:
{{parameter_name
[PROPERTY]|RETURN prop } [BY REF] [extern_datatype]|CONTEXT}
with the above repeated as many times as is
needed.
Finally, prop has the values:
INDICATOR, LENGTH, MAXLEN, CHARSETID, or CHARSETFORM
CREATE PROCEDURE Command
The command for creating a procedure is almost
identical to that for a function, except that no RETURN clause is
required. The CREATE PROCEDURE command can be used to create either
internal standalone procedures or procedures that register calls to
external procedures. For procedures, the CREATE command format is:
CREATE [OR
REPLACE] PROCEDURE [schema.]procedure_name
[(argument [IN|OUT|IN OUT] [NOCOPY] datatype)]
[invoker_rights_clause]
IS|AS
Plsql_body|call_spec;
where:
argument. The name given to this
argument; this can be any valid variable name.
IN, OUT, or IN OUT. Specifies how the
argument is to be treated (strictly input, strictly output, or both).
datatype. Te datatype of the argument;
this can be any valid scalar datatype.
The call_spec has the form:
LANGUAGE
java_declaration|C_declaration
The Java_declaration has the form:
JAVA NAME
'string'
The C_declaration has the form:
C [NAME
name] LIBRARY lib_name [WITH CONTEXT]
[PARAMETERS
(parameters)]
The parameters have the form:
{{parameter_name
[PROPERTY] | RETURN prop } [BY REF] [extern_datatype] | CONTEXT}
with the above repeated as many times as is
needed. @@@Comp: Again, fl/l.
Finally, prop has the values:
INDICATOR, LENGTH, MAXLEN, CHARSETID, or CHARSETFORM
For both procedures and functions, the command
arguments are listed below:
OR REPLACE. (Optional) Specifies that
if the procedure or function exists, replace it; if it doesn’t exist,
create it.
schema. The schema to place the
procedure or function into. If other than the user’s default schema,
the user must have the CREATE ANY PROCEDURE system privilege.
Procedure_name or function_name. The
name of the procedure or function being created.
argument(s). The argument to the
procedure or function; it may be more than one of these.
IN. Specifies that the argument must be
identified when calling the procedure or function. For functions, an
argument must always be provided.
OUT. Specifies that the procedure
passes a value for this argument back to the calling object. Not used
with functions.
IN OUT. Specifies that both the IN and
OUT features are in effect for the procedure. This is not used with
functions.
datatype. The datatype of the argument.
Precision, length, and scale cannot be specified; they are derived
from the calling object.
PL/SQL body. An embedded SQL and PL/SQL
body of statements.
IS or AS. The documentation states that
these are interchangeable, but one or the other must be specified.
However, Oracle didn’t tell this to some of their tools, so if you get
an error using one, try the other.
It is suggested that each function and
procedure be created under a single owner for a given application.
This makes administration easier and allows use of dynamic SQL to
create packages. It is also suggested that the procedure or function
be created as a text file for documentation and later easy update. The
source files for related procedures and functions should be stored
under a common directory area.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
