7.2. Identifying PL/SQL Objects

PL/SQL is Oracle's Procedural Language extension to SQL. This Oracle proprietary language was derived from Ada and has evolved to include a robust feature set, including sequential and conditional controls, looping constructs, exception handing, records, and collections, as well as object-oriented features such as methods, overloading, upcasting, and type inheritance.

Full knowledge of the PL/SQL language is well beyond the scope of the OCA/OCP exams, and more developers than DBAs create PL/SQL programs. But a significant number of database features are delivered as PL/SQL programs, and knowledge of how to identify and work with these programs is crucial to your effectiveness. In this section, you will learn what kinds of PL/SQL programs are available, when each is appropriate, and what configuration options are applicable to working with PL/SQL programs.

The exam covers five types of named PL/SQL programs, which are usually stored in the database: functions, procedures, packages, package bodies, and triggers. Each of these program types is covered in the following sections. The name and source code for each stored PL/SQL program is available from the data dictionary views DBA_SOURCE and DBA_TRIGGERS, although some packages are supplied "wrapped," which means that the source code is a binary form. You can wrap your programs as well with the wrap utility.

7.2.1. Working with Functions

Functions are PL/SQL programs that execute zero or more statements and return a value through a RETURN statement. Functions can also receive or return zero or more values through their parameters. Oracle provides several built-in functions such as the commonly used SYSDATE, COUNT, and SUBSTR functions. There are over 200 SQL functions that come with your Oracle10g database and hundreds of PL/SQL functions. See the Oracle Database SQL Reference 10g manual (part B10759-01) for a complete list of these SQL functions and the PL/SQL Packages and Types Reference 10g manual (part B10802-01) for the functions available with the built-in PL/SQL packages. Because functions have a return value, a datatype is associated with them. Functions can be invoked anywhere an expression of the same datatype is allowed. Here are some examples:

• As a default value

  DECLARE
    today DATE DEFAULT SYSDATE;

  
  

• In an assignment

  today := SYSDATE;

  
  

• In a Boolean expression

  IF TO_CHAR(SYSDATE,'Day') = 'Monday'

  
  

• In a SQL expression

  SELECT COUNT(*)
  FROM employees
  WHERE hire_date > SYSDATE-30;

  
  

• In the parameter list of another procedure or function

  SELECT TRUNC(SYSDATE)

  
  

  Create a function with the CREATE FUNCTION statement, like this:

  CREATE OR REPLACE FUNCTION is_weekend(
    check_date IN DATE DEFAULT SYSDATE)
    RETURN VARCHAR2 AS
  BEGIN
     CASE TO_CHAR(check_date,'DY')
      WHEN 'SAT' THEN
         RETURN 'YES';
      WHEN 'SUN' THEN
         RETURN 'YES';
     ELSE
         RETURN 'NO';
     END CASE;
  END;

  
  

Functions, like all named PL/SQL, have the OR REPLACE keywords available in the CREATE statement. When present, OR REPLACE tells the database to not raise an exception if the object already exists. This behavior differs from a DROP and CREATE, in that privileges are not lost during a REPLACE operation and any objects that reference this object will not become invalid.

7.2.2. Working with Procedures

Procedures are PL/SQL programs that execute one or more statements. Procedures can receive and return values only through their parameter lists. Unlike functions, only a few built-in procedures, such as RAISE_APPLICATION_ERROR are built into the PL/SQL language.

You create a procedure with the CREATE PROCEDURE statement, like this:

CREATE OR REPLACE PROCEDURE archive_orders
   (cust_id   IN NUMBER
    ,retention IN NUMBER) IS
BEGIN
   DELETE orders
    WHERE customer = cust_id
   AND   order_date < SYSDATE - retention;

   INSERT INTO maint_log
     (action, action_date, who) VALUES
     ('archive orders '||retention||' for '||cust_id
     ,SYSDATE ,USER);
END;

The keyword IS, in the third line, is synonymous with the keyword AS, seen in the third line of the last example function in the previous section. Both are syntactically valid for all named SQL.

You invoke a procedure as a stand-alone statement within a PL/SQL program by using the CALL or EXEC commands. Here is an example:

EXEC DBMS_OUTPUT.PUT_LINE('Hello world!'),
Hello world!

PL/SQL procedure successfully completed.

CALL  DBMS_OUTPUT.PUT_LINE('Hello world!'),
Hello world!

Call completed.

7.2.3. Working with Packages

A package is a container for functions, procedures, and data structures, such as records, cursors, variables and constants. A package has a publicly visible portion, called the specification (or spec for short) and a private portion called the package body. The package spec describes the programs and data structures that can be accessed from other programs. The package body contains the implementation of the procedures and functions. The package spec is identified in the data dictionary as the type PACKAGE, and the package body is identified as the type PACKAGE BODY.

To create a package spec, use the CREATE PACKAGE statement. In the following, the package spec table_util contains one function and one procedure:

CREATE OR REPLACE PACKAGE table_util IS
  FUNCTION version RETURN VARCHAR2;
  PROCEDURE truncate (table_name IN VARCHAR2);
END table_util;

Privileges on a package are granted at the package-spec level. The EXECUTE privilege on a package allows the grantee to execute any program or use any data structure declared in the package specification. You cannot grant the EXECUTE privilege on only some of the programs declared in the spec.

A package body depends on a package spec having the same name. The package body can only be created after the spec. The package body implements the programs that were declared in the package spec and can optionally contain private programs and data accessible only from within the package body.

To create a package body, use the CREATE PACKAGE BODY statement:

CREATE OR REPLACE PACKAGE BODY table_util IS

Here is an example of a private variable that can be referenced only in the package body:

version_string VARCHAR2(8) := '1.0.0';

Here is the code for the version function:

FUNCTION version RETURN VARCHAR2 IS
  BEGIN
     RETURN version_string;
  END;

Here is the code for the truncate procedure:

PROCEDURE truncate (table_name IN VARCHAR2) IS
  BEGIN
     IF  UPPER(table_name) = 'ORDER_STAGE'
      OR UPPER(table_name) = 'SALES_ROLLUP'
      THEN
        EXECUTE IMMEDIATE 'truncate table ' ||
           UPPER(table_name);
      ELSE
        RAISE_APPLICATION_ERROR(-20010

,'Invalid table for truncate: '|| table_name);
     END IF;
  END;
END table_util;

The package name following the END statement is optional, but encouraged, as it improves readability.

7.2.4. Working with Triggering Events and Managing Triggers

Triggers are PL/SQL programs that are invoked in response to an event in the database. Three sets of events can be hooked, allowing you to integrate your business logic with the database in an event-driven manner. Triggers can be created on DML events, DDL events, and database events. These three trigger event classes provide developers and you, the DBA, with a robust toolkit with which to design, build, and troubleshoot systems.

We will look at each of these events in more detail in the following sections. We will also discuss how to enable and disable triggers.

7.2.4.1. DML Event Triggers

DML triggers are invoked, or "fired," when the specified DML events occur. If the keywords FOR EACH ROW are included in the trigger definition, the trigger fires once for each row that is changed. If these keywords are missing, the trigger fires once for each statement that causes the specified change. If the DML event list includes the UPDATE event, the trigger can be further restricted to fire only when updates of specific columns occur.

The following example creates a trigger that fires before any insert and before an update to the hire_date column of the employee table:

CREATE OR REPLACE TRIGGER employee_trg
 BEFORE INSERT OR UPDATE OF hire_date
 ON employees FOR EACH ROW
BEGIN
   log_update(USER,SYSTIMESTAMP);
   IF INSERTING THEN -- if fired due to insert
     :NEW.create_user :=  USER;
     :NEW.create_ts    :=  SYSTIMESTAMP;
   ELSEIF UPDATING THEN -- if fired due to update
      IF :OLD.hire_date <> :NEW.hire_date THEN
         RAISE_APPLICATION_ERROR(-20013,
           'update of hire_date not allowed'),
     END IF;
   END IF;
END;

This trigger will fire once for each row affected, because the keywords FOR EACH ROW are included. When the triggering event is an INSERT, two columns are forced to the specific values returned by USER and SYSTIMESTAMP. DML triggers cannot be created on SYS-owned objects. Table 7.1 shows the DML triggering events.

Table 7.1. DML Trigger Events

Event	When It Fires
INSERT	When a row is added to a table or a view.
UPDATE	When an UPDATE statement changes a row in a table or view. Update triggers can also specify an OF clause to limit the scope of changes that fire this type of trigger.
DELETE	When a row is removed from a table or a view.

Multiple triggers on a table fire in the following order:

• Before statement triggers

• Before row triggers

• After row triggers

• After statement triggers

7.2.4.2. DDL Event Triggers

DDL triggers fire either for DDL changes to a specific schema or to all schemas in the database. The keywords ON DATABASE specify that the trigger will fire for the specified event on any schema in the database.

The following is an example of a trigger that fires for a DDL event in only one schema:

CREATE OR REPLACE TRIGGER NoGrantToPublic
BEFORE GRANT ON engineering.SCHEMA
DECLARE
   grantee_list    dbms_standard.ora_name_list_t;
   counter         BINARY_INTEGER;
BEGIN
   -- get the list of grantees
   counter := GRANTEE(grantee_list);
   FOR loop_counter IN
     grantee_list.FIRST..grantee_list.LAST
   LOOP
      -- if PUBLIC is on the grantee list, stop the action

IF REGEXP_LIKE(grantee_list(loop_counter)
            ,'public','i') THEN
         RAISE_APPLICATION_ERROR(-20113
           ,'No grant to PUBLIC allowed for '
            ||DICTIONARY_OBJ_OWNER||'.'
            ||DICTIONARY_OBJ_NAME);
      END IF;
   END LOOP;
END;

In the preceding example, the DDL event is a GRANT statement issued by user engineering. The code examines the grantee list, and if it finds the special user/role PUBLIC, an exception is raised, causing the grant to fail. Table 7.2 shows the DDL triggering events.

Table 7.2. DDL Trigger Events

Event	When It Fires
ALTER	When an ALTER statement changes a database object.
ANALYZE	When the database gathers or deletes statistics or validates the structure of an object.
ASSOCIATE STATISTICS	When the database associates a statistic with a database object with an ASSOCIATE STATISTICS statement.
AUDIT	When the database records an audit action (except FGA).
COMMENT	When a comment on a table or column is modified.
CREATE	When the database object is created.
DDL	In conjunction with any of the following: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP GRANT, NOAUDIT, RENAME, REVOKE, or TRUNCATE.
DISASSOCIATE STATISTICS	When a database disassociates a statistic type from a database object with a DISASSOCIATE STATISTICS statement.
DROP	When a DROP statement removes an object from the database.
GRANT	When a GRANT statement assigns a privilege.
NOAUDIT	When a NOAUDIT statement changes database auditing.
RENAME	When a RENAME statement changes an object name.
REVOKE	When a REVOKE statement rescinds a privilege.
TRUNCATE	When a TRUNCATE statement purges a table.

7.2.4.3. Database Event Triggers

Database event triggers fire when the specified database-level event occurs. Most of these triggers are available only before or after the database event, but not both.

The following example creates an after-server error trigger that sends an e-mail notification when an ORA-01555 error occurs:

CREATE OR REPLACE TRIGGER Email_on_1555_Err
AFTER SERVERERROR ON DATABASE
DECLARE
   mail_conn         UTL_SMTP.connection;
   smtp_relay        VARCHAR2(32) := 'mailserver';
   recipient_address VARCHAR2(64) := '[email protected]';
   sender_address    VARCHAR2(64) := '[email protected]';
   mail_port         NUMBER := 25;
   msg                VARCHAR2(200);
BEGIN
    IF USER = 'SYSTEM' THEN
       -- Ignore this error
      NULL;
    ELSIF IS_SERVERERROR (1555) THEN
     -- compose the message
     msg := 'Subject: ORA-1555 error';
     msg := msg||'Snapshot too old err at '||systimestamp;
     -- send email notice
     mail_conn := UTL_SMTP.open_connection(smtp_relay
           ,mail_port);
     UTL_SMTP.HELO(mail_conn, smtp_relay);
     UTL_SMTP.MAIL(mail_conn, sender_address);
     UTL_SMTP.RCPT(mail_conn, recipient_address);
      UTL_SMTP.DATA(mail_conn, msg);
     UTL_SMTP.QUIT(mail_conn);
   END IF;
END;

Table 7.3 shows the database triggering events.

Table 7.3. Database Trigger Events

Event	When It Fires
LOGON	When a database session is established—only AFTER trigger is allowed
LOGOFF	When a database session ends normally—only BEFORE trigger is allowed
STARTUP	When the database is opened—only AFTER trigger is allowed
SHUTDOWN	When the database is closed—only BEFORE trigger is allowed
SERVERERROR	When a database exception is raised—only AFTER trigger is allowed
SUSPEND	When a server error causes a transaction to be suspended

7.2.4.4. Enabling and Disabling Triggers

The database automatically enables a trigger when you create it. After creating a trigger, you can disable it (temporarily prevent it from firing) or re-enable it. You can disable and enable triggers by name with an ALTER TRIGGER statement. Here are two examples:

ALTER TRIGGER after_ora60 DISABLE;

ALTER TRIGGER load_packages ENABLE;

Alternatively, you can enable and disable multiple DML triggers with an ALTER TABLE statement, like this:

ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE employees ENABLE ALL TRIGGERS;

You can query the STATUS column of the DBA_TRIGGERS or USER_TRIGGERS view to find out whether a trigger is enabled or disabled.

7.2.5. Using and Administering PL/SQL Programs

Oracle10g comes bundled with hundreds of built-in packages that give you significant capabilities for administering your database. Many features in the database are implemented through one or more of these built-in packages. To use the job scheduler, collect and manage optimizer statistics, implement fine-grained auditing, send e-mail from the database, and use Data Pump or Log Miner, you must engage the built-in packages. As you gain experience, you will use these built-in packages more extensively.

To view the names and parameter lists for stored programs (except triggers), use the SQL*Plus DESCRIBE command like this:

describe dbms_monitor
-- some output is deleted for brevity

PROCEDURE SESSION_TRACE_DISABLE
 Argument Name   Type                 In/Out Default?
 --------------- ------------------- ------ --------
 SESSION_ID      BINARY_INTEGER      IN      DEFAULT
 SERIAL_NUM      BINARY_INTEGER      IN      DEFAULT

PROCEDURE SESSION_TRACE_ENABLE
 Argument Name   Type                 In/Out Default?
 --------------- ------------------- ------ --------
 SESSION_ID      BINARY_INTEGER      IN      DEFAULT
 SERIAL_NUM      BINARY_INTEGER      IN      DEFAULT
 WAITS           BOOLEAN             IN      DEFAULT
 BINDS           BOOLEAN             IN      DEFAULT

You can see in this output from DESCRIBE that the packaged procedure DBMS_MONITOR contains several procedures, including SESSION_TRACE_DISABLE and SESSION_TRACE_ENABLE. Furthermore, you can see the names, datatypes, and in/out mode for each parameter (SESSION_ ID, SERIAL_NUM, and so on).

A PL/SQL program is invalidated whenever a dependent object is changed through the ALTER command. The database automatically recompiles the package body the next time it is called, but you can choose to compile invalid PL/SQL programs yourself and thus eliminate a costly recompile during regular system processing. To explicitly compile a named SQL program, use the ALTER ... COMPILE statement, like this:

ALTER PROCEDURE archive_orders COMPILE;
ALTER FUNCTION is_weekend COMPILE;
ALTER PACKAGE table_util COMPILE BODY;

Other objects, such as views or types, are similarly compiled.