6.2.1.1 Pinning packages on database startup

A common requirement for a high-performance database is to pin one or more packages into shared memory. By pinning your code, it is exempted from the least recently used algorithm and will never be aged out of the shared pool area.

Before the existence of STARTUP triggers, the DBA would have to set up a script to run after the database was started. Now, I can create a STARTUP trigger like the following:

/* Filename on companion disk: startup.trg */
CREATE OR REPLACE TRIGGER pin_code_on_startup 
  AFTER STARTUP ON DATABASE 
BEGIN
   /* Pin the default packages of PL/SQL to 
      improve runtime performance. */
   DBMS_SHARED_POOL.KEEP (
      'SYS.STANDARD', 'P');
   DBMS_SHARED_POOL.KEEP (
      'SYS.DBMS_STANDARD', 'P');
END;
/

With this trigger in place, I guarantee that all of my large code elements (including cursors, types, and triggers) and even my sequences are cached in the shared pool until the instance goes down.

If you are going to be pinning objects, you should consider building an infrastructure table to store the names of elements you want pinned. Then instead of hard-coding your pin commands in a trigger (as just shown), you could run a procedure that reads through the table and pins each element found. This approach is explained in detail in Chapter 12 of Oracle Built-in Packages.

6.2.1.2 Tracking logins to the database

Suppose that I want to keep track of logins to my database instance. To make this information available in a structured fashion, I am going to send my login information to a queue using the Oracle Advanced Queuing (AQ) facility.

These steps are illustrated in Figure 6.2. You will find all of the AQ-related steps in the aq.sql script on the companion disk; I'll concentrate on the trigger-related components here.

Figure 6.2: Logical flow of LOGON trigger

Let's start in reverse. Here is the LOGON trigger:

/* Filename on companion disk: aq.sql */
CREATE OR REPLACE TRIGGER publish_logon
AFTER LOGON ON DATABASE
BEGIN
   oraevent.put ('This is a logon');
END;
/

As you can see, there isn't much to it: every time someone logs in to the database, we'll put a message in the queue with a call to oraevent.put. So let's take a look at that procedure:

PROCEDURE oraevent.put (details_in IN VARCHAR2) 
IS
   q_opts DBMS_AQ.ENQUEUE_OPTIONS_T;
   msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
   msg_handle RAW(16);
   event_l Event_t;
BEGIN
   /* Setting visibility to IMMEDIATE will
   || force the queue to "commit" before the
   || client transaction commits.
   */
   q_opts.visibility := DBMS_AQ.IMMEDIATE;
   event_l := Event_t.make(details_in);

   DBMS_AQ.ENQUEUE(queue_name => 'aqadmin.loginQ',
      enqueue_options => q_opts,
      message_properties => msg_props,
      payload => event_l,
      msgid => msg_handle);
END;

This procedure calls DMBS_AQ.ENQUEUE to place a message, or payload, in the loginQ queue. Each message is an object of type Event_t, defined as follows:

CREATE TYPE Event_t AS OBJECT (
   eventname VARCHAR2(64),
   details VARCHAR2(512),
   username VARCHAR2(30),
   timestamp DATE,
   STATIC FUNCTION make (
      details_in IN VARCHAR2) RETURN Event_t
);
/
CREATE OR REPLACE TYPE BODY Event_t 
AS
   STATIC FUNCTION make (
      details_in IN VARCHAR2) RETURN Event_t
   IS
   BEGIN
      RETURN Event_t(
         SYSEVENT, 
         details_in,
         LOGIN_USER, 
         SYSDATE);
   END;
END;
/

The RETURN statement relies on two of the event-related functions, SYSEVENT and LOGIN_USER, to record characteristics at the time of login.

Run the aq.sql script to create all elements and then test the code by spawning a second SQL*Plus session to watch or dequeue the login messages.

6.2.1.3 Trapping system errors

The SERVERERROR event will prove to be a very handy mechanism. You can define it at the database level, which means that any error raised in any schema will be interceptable through the trigger. You can also define a trigger for this event at the schema level, limiting the scope of firing of the trigger.

Suppose that you simply want to keep track of errors raised in a particular application running on your instance. When an error occurs, you write a message to a database pipe. Another session (running asynchronously to the application users) can then wake up and dump the contents of the pipe and examine the errors.

To facilitate that process, I have created and included on the disk a package called watch. Stored in watch.pkg on the companion disk, this package allows you to watch actions and then direct a message constructed for that action to either the screen via DBMS_OUTPUT or to a pipe via DBMS_PIPE.

TIP: If you are comfortable with Oracle Advanced Queuing, you'll find that the AQ facility certainly offers a more robust architecture than the basic database pipes of DBMS_PIPE for intersession communication and logging. DBMS_PIPE, on the other hand, is easier to use and also very handy to know for a variety of development scenarios.

Using the watch package, I first create a utility procedure that I will call in my SERVERERROR triggers:

/* Filename on companion disk: serverr.trg */
CREATE OR REPLACE PROCEDURE pipe_error (
   context IN VARCHAR2, msg IN VARCHAR2)
IS
BEGIN
   /* Send the information to a pipe. */
   watch.topipe;
   
   /* Retrieve all system event attributes. */
   watch.action (context || ' trap_error', msg);
END;
/

I can then define a trigger at the database level that displays all of the attributes available from within this trigger:

CREATE OR REPLACE TRIGGER trap_error 
  AFTER SERVERERROR ON DATABASE 
BEGIN
   pipe_error ('DATABASE', 
      sysevent || '-' ||
      instance_num || '-' ||
      database_name || '-' ||
      SQLCODE || '-' ||
      server_error (1) || '-' || 
      login_user
      );
END;
/

I will also define a SERVERERROR trigger for the SCOTT schema, so we can explore the way multiple triggers of the same type fire:

CREATE OR REPLACE TRIGGER scott_trap_error 
  AFTER SERVERERROR ON SCOTT.SCHEMA 
BEGIN
   pipe_error (login_user, 
      sysevent || '-' ||
      instance_num || '-' ||
      database_name || '-' ||
      SQLCODE || '-' ||
      server_error (1) || '-' || 
      login_user
      );
END;
/

To test these triggers, I created the following script:

/* Filename on companion disk: serverr.tst */
DECLARE
   exc EXCEPTION;
   PRAGMA EXCEPTION_INIT (exc, -&1);
BEGIN
   RAISE exc;
END;
/

Now I will connect as SCOTT and run the script emulating a date-related error:

SQL> @serverr.tst 1855
*
ERROR at line 1:
ORA-01855: AM/A.M. or PM/P.M. required

If the triggers fired, the watch pipe should contain some information. I can dump the contents of the pipe with a call to watch.show:

SQL> exec watch.show
Contents of WATCH Trace:
***WATCHing at: June 1, 1999 12:54:14
   Context: SCOTT trap_error
   Message: SERVERERROR-1-ORACLE-0-1855-SCOTT
***WATCHing at: June 1, 1999 12:54:14
   Context: DATABASE trap_error
   Message: SERVERERROR-1-ORACLE-0-1855-SCOTT 

We learn a few things from this execution:

• The schema-level trigger fires before the database-level trigger.

TIP: Current behavior in the Oracle database is that a schema-level trigger will fire before a database-level trigger. This sequence is not, however, a documented feature and is subject to change in future versions of Oracle. You should design your triggers so that the trigger logic does not depend on the firing sequence.

• By the time the SERVERERROR trigger fires, the SQLCODE function does not return the error that was raised. Instead, it returns 0. You must rely on the SERVER_ERROR function to retrieve the error at the top of the error stack.

Now I will connect to the DEMO account in my Oracle 8.1.5 instance and run this script for a different error:

SQL> connect demo/demo.
SQL> @serverr.tst 1652
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by nnn in tablespace

When I take a look at my pipe contents, I have only one entry:

SQL> exec watch.show
Contents of WATCH Trace:
***WATCHing at: June 1, 1999 13:01:38
   Context: DATABASE trap_error
   Message: SERVERERROR-1-ORACLE-0-1652-DEMO

And that is because I did not create a SERVERERROR trigger in the DEMO schema; only the database-level trigger is fired.

6.2.1.4 Checking for specific errors

One other useful technique in this type of trigger is to check for a specific error and then take special action in that instance. There are two ways to do this:

• Use the trigger WHEN clause to restrict the execution of the trigger logic for a specific error.

• Use the IS_SERVERERROR function to determine if a specific error has been raised anywhere within the error call stack and then take action.

Let's see how each approach would work. First, suppose that I want to qualify my SERVERERROR trigger at the database level to fire only when the ORA-02292 error occurs ("integrity constraint (constant name) violated - child record found").

I can create my trigger as follows:

/* Filename on companion disk: serverr2.trg */
CREATE OR REPLACE TRIGGER scott_trap_parent_key 
  AFTER SERVERERROR ON DATABASE 
WHEN (SYS.SERVER_ERROR(1) = 2292)
BEGIN
   pipe_error (
      'DATABASE trap parent key', 
      'Invalid attempt to delete primary key by ' ||
      LOGIN_USER);
END;
/

My call to SYS.SERVER_ERROR(1) retrieves the error at the top of the error stack. There are two things to note in this procedure:

• If I do not qualify the call to SERVER_ERROR with its owner, SYS, I get the following error:

AFTER SERVERERROR ON DATABASE
                       *
ERROR at line 2:
ORA-00942: table or view does not exist

• I must specify the error number as a positive, not negative, integer value. Otherwise, no match will be detected.

Once the trigger is defined, I test it by trying to delete a row from the dept table whose parent key is referenced by an employee:

SQL> DELETE FROM dept WHERE deptno=10;
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - 
           child record found

I can now see what information was sent to my pipe within the trigger as follows:

SQL> exec watch.show
Contents of WATCH Trace:
***WATCHing at: June 2, 1999 16:27:09
   Context: DATABASE trap parent key trap_error
   Message: Invalid attempt to delete primary key by SCOTT

Here I will demonstrate the IS_SERVERERROR approach with a modified version of the database-level trigger:

/* Filename on companion disk: serverr.trg */
CREATE OR REPLACE TRIGGER trap_error 
  AFTER SERVERERROR ON DATABASE 
BEGIN
   /* Same tracking as before. */
   pipe_error ('DATABASE', ...);
      
   IF IS_SERVERERROR (1652) -- POSITIVE NUMBER REQUIRED!
   THEN
      /* Add a file to the tablespace... just a dummy
         entry for the book. */
      pipe_error ('DATABASE', 'Add to tablespace');
   END IF;
END;
/

And now if the ORA-01652 error is raised anywhere in the error stack from the SCOTT schema, I see three entries in the error pipe:

SQL> DECLARE
  2     exc EXCEPTION;
  3     PRAGMA EXCEPTION_INIT (exc, -1652);
  4  BEGIN
  5     RAISE exc;
  6  EXCEPTION 
  7     WHEN OTHERS THEN
  8        RAISE VALUE_ERROR;
  9  END;
 10  /
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-01652: unable to extend temp segment by nnn in tablespace

SQL> exec watch.show
Contents of WATCH Trace:
***WATCHing at: June 3, 1999 09:47:05
   Context: SCOTT trap_error
   Message: SERVERERROR-1-ORACLE-0-6502-SCOTT
***WATCHing at: June 3, 1999 09:47:05
   Context: DATABASE trap_error
   Message: SERVERERROR-1-ORACLE-0-6502-SCOTT
***WATCHing at: June 3, 1999 09:47:05
   Context: DATABASE trap_error
   Message: Add to tablespace

WARNING: When you use IS_SERVERERROR, you must pass a positive number for it to find a match on the error. So we must wonder once again: are Oracle error numbers negative or positive? It looks like the folks at Oracle have trouble giving a consistent answer to this question.