Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.
   
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter