21.7.1 Example: Retrieving the Time Zone

While it's trivial in Oracle to read the time on the system clock (by selecting SYSDATE from DUAL), Oracle provides no clue about the time zone in which the clock resides. I've seen how Oracle's silence on this matter can be a problem in a replicated environment where the Oracle servers were separated by thousands of miles. Without knowing the time zone, how can I write a conflict resolution routine that compares transaction execution time from different servers? Yes, I could set all the servers to a common time zone, but personally, I find that a painful solution at best.

External procedures to the rescue!

A useful application would return at least the name of the time zone in which the server is currently executing, allowing us to perform the time transformations -- using, for example, Oracle's built-in NEW_TIME function.

Let's look first at the PL/SQL side of this external procedure. We are going to call our library timezone_utl. It doesn't exist yet, but we can tell Oracle to create the library anyway (for illustrative purposes):

/* filename on companion disk: tz_utl.sql /*
CREATE OR REPLACE LIBRARY timezone_utl_l
AS
   '/oracle/local/lib/libtz_utl.so';

Now we are going to create a procedure we call timezone. Again, we implement the external procedure in a package, and use RESTRICT_REFERENCES to tell Oracle that it does not affect database or package states:

CREATE OR REPLACE
PACKAGE timezone_utl
IS
    PROCEDURE timezone
       (local_timezone OUT VARCHAR2);
    PRAGMA RESTRICT_REFERENCES (timezone, WNDS, RNDS, WNPS, RNPS);
END timezone_utl;

Notice that we have chosen to retrieve the time zone into an OUT VARCHAR2 parameter. We will therefore include an explicit PARAMETERS clause, and it will include INDICATOR and MAXLEN properties. The routine is going to return a NULL if there is any problem.

CREATE OR REPLACE
PACKAGE BODY timezone_utl
IS
    PROCEDURE timezone
       (local_timezone OUT VARCHAR2)
    IS EXTERNAL
       LIBRARY timezone_utl_l
       NAME "get_timezone"
       LANGUAGE C
       PARAMETERS
          (local_timezone STRING,
           local_timezone MAXLEN sb4,
           local_timezone INDICATOR sb2);
END timezone_utl;

In our C module we write a function, get_timezone, that retrieves the current time zone string from the operating system. From the perspective of the C programmer, this function fills a caller-provided output buffer with the current time zone. If meaningful, the function can also return the alternate "daylight savings" time zone, since it is provided automatically by the localtime function.[9]

[9] The localtime and strftime functions are available in the C runtime libraries included with a number of standard implementations, such as SVID 3, POSIX, BSD 4.3, and ISO 9899.

/* Filename on companion disk: tz_utl.c */
#include <stdio.h>
#include <time.h>
#include <oci.h>

/* Tested with Solaris 2.5.1 and Oracle 8.0.3.  */

void
get_timezone (char *pTimezone,
              sb4 *pMaxLenTimezone, /* not including NULL terminator */
              sb2 *pIndTimezone)
{
    if ( *pMaxLenTimezone > 0 )
    {
        time_t      tmpTime;
        struct tm   *pTimeInfo;
        char        timezoneBuff[BUFSIZ];
        size_t      lenCopied;

        /* Get the current time and extract the timezone string from it */
        tmpTime = time (0);
        pTimeInfo = localtime (&tmpTime);
        lenCopied = strftime (timezoneBuff,
                              sizeof(timezoneBuff),
                              "%Z",
                              pTimeInfo);

        /* If the timezone string fits, copy it into the output parameter */
        if ( (lenCopied > 0) && (lenCopied <= (size_
            t)*pMaxLenTimezone) )
        {
            /* Copy the string and return the length */
            memcpy (pTimezone, timezoneBuff, lenCopied);

            /* Null-terminate the returned string. Note that the
            ** null-terminator byte is not included in MAXLEN */
            pTimezone[lenCopied] = '\0';

            *pIndTimezone = OCI_IND_NOTNULL;
        }
        else
        {
            /* it doesn't fit, so return a NULL */
            *pIndTimezone = OCI_IND_NULL;
        }
    }
    else
    {
        /* the output parameter is too small, return a NULL */
        *pIndTimezone = OCI_IND_NULL;
    }
}

When this C function returns, the length parameter referenced by pLenTimezone will indicate the size of the returned string. If there is an error getting the timezone string, or if the maximum size of the buffer is too small for the timezone value, the indicator variable will be set to OCI_IND_NULL, indicating a NULL.

Compiling and linking this function as a shared library requires some special options. Here is the (slightly edited) log from a UNIX make command that illustrates the necessary include libraries and compiler options (the backslashes are continuation characters added here only for clarity). As mentioned before, this example is from Solaris 2.5.1 and Oracle 8.0.3.

gcc  -I/oracle/product/8.0.3/rdbms/demo \
     -I/oracle/product/8.0.3/network/public \
     -I/oracle/product/8.0.3/plsql/public \
     -fPIC -Wall -c tz_utl.c
gcc  -shared -o libtz_utl.so tz_utl.o

Briefly, these options have the following meanings:

-fPIC

Produce "position-independent code"

-Wall

Issue all "-W" compiler warnings

-c

Compile the source file (to produce a .so file), but do not link it

-shared

Produce a shared object file from a static library

-o libtz_utl.so

Write the output file to libtz_utl.so

A complete makefile for all of the examples appears on the companion diskette (Makefile).

To test our routine, we can run the following PL/SQL block in SQL*Plus:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
   local_time   DATE;
   local_tz     VARCHAR2(3);
   target_time  DATE;
   target_tz    VARCHAR2(3) := 'GMT';
BEGIN
   /* Call the external procedure to discover the local time zone */
   timezone_utl.timezone (local_tz);

   IF ( local_tz IS NOT NULL ) THEN
     DBMS_OUTPUT.PUT_LINE ('timezone returned = "'
        || local_tz || '"');

      /* Convert the current time to the target time zone */
      local_time  := SYSDATE;
      target_time := NEW_TIME (local_time, local_tz, target_tz);

      /* Display original and converted times with their time zones */
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (local_time,
         'DD-MON-YYYY HH24:MI:SS') || ' ' || local_tz );
      DBMS_OUTPUT.PUT_LINE ('=');
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (target_time,
         'DD-MON-YYYY HH24:MI:SS') || ' ' || target_tz );
   ELSE
      DBMS_OUTPUT.PUT_LINE ('local_tz returned NULL');
   END IF;

END;

This block produced the following (correct) result:

timezone returned = "PDT"
08-JUL-1997 17:07:15 PDT
=
09-JUL-1997 00:07:15 GMT

21.7.2 Example: Sending Email

The last few versions of the Oracle server have included a built-in PL/SQL package which allows you to send email via Interoffice (née Oracle Mail). Unfortunately, this DBMS_MAIL package is of no use to the large number of Oracle shops that standardize on some other email server.

Using an external procedure, however, you can easily write a PL/SQL package that will call an operating system command such as mailx under UNIX. While it may not be the most elegant approach, it has one demonstrable benefit: it works!

Again, we'll look at the PL/SQL side first. In this case, we intend to create a separate shared object file for our library:

/* Filename on companion disk: mail_utl.sql */
CREATE OR REPLACE LIBRARY mail_utl_l
AS
   '/oracle/local/lib/libmail_utl.so';

The package specification contains a single function, send_mail, which accepts only four arguments: the sender, recipient(s), subject, and message body:

CREATE OR REPLACE
PACKAGE mail_utl
IS
    FUNCTION send_mail
       (return_address  IN VARCHAR2 DEFAULT NULL,
        recipient_list  IN VARCHAR2,
        subject_line    IN VARCHAR2 DEFAULT NULL,
        message_text    IN VARCHAR2 DEFAULT NULL)
    RETURN PLS_INTEGER;
    PRAGMA RESTRICT_REFERENCES (send_mail, WNDS, RNDS, WNPS, RNPS);
END mail_utl;

Before coding the body, let's dream up some interesting new requirements. First, if the return address, subject line, or message text is null, let's have the C function use some reasonable defaults. Second, if the recipient list is null, we want to raise a programmer-defined exception.

To support the requirement of communicating nulls properly, the PARAMETERS clause shown below includes indicator variables for each of the parameters. To raise an exception, however, we must send "context" information to the function. We do so by including the WITH CONTEXT clause and including the CONTEXT keyword as the first argument in the PARAMETERS clause:

CREATE OR REPLACE
PACKAGE BODY mail_utl
IS
    FUNCTION send_mail
       (return_address  IN VARCHAR2,
        recipient_list  IN VARCHAR2,
        subject_line    IN VARCHAR2,
        message_text    IN VARCHAR2)
    RETURN PLS_INTEGER
    IS EXTERNAL
       LIBRARY mail_utl_l
       NAME "send_mail"
       LANGUAGE C
       WITH CONTEXT   -- passes context so we can raise exception
       PARAMETERS
          (CONTEXT,   -- keyword giving location of CONTEXT parameter
           return_address STRING,
           return_address INDICATOR,
           recipient_list STRING,
           recipient_list INDICATOR,
           subject_line   STRING  ,
           subject_line   INDICATOR,
           message_text   STRING,
           message_text   INDICATOR,
           RETURN SB4);
END mail_utl;

The corresponding C function sends mail by opening a pipe to the UNIX /bin/mailx program. If there are any errors during the call to /bin/mailx, the function will return any error code returned by the operating system; otherwise it simply returns a value of zero, indicating normal, successful completion.

/* Filename on companion disk: mail_utl.c */
#include <stdlib.h>
#include <stdio.h>
#include <errno.h>
#include <oci.h>

/* Tested with Solaris 2.5.1 and Oracle 8.0.3. */

sb4
send_mail (OCIExtProcContext *pWithContext,
           char  *pReturnAddress,
           sb2   indReturnAddress,
           char  *pRecipientList,
           sb2   indRecipientList,
           char  *pSubject,
           sb2   indSubject,
           char  *pMessage,
           sb2   indMessage)
{
    char mailCmd[BUFSIZ];
    char returnAddrOption[BUFSIZ];
    sb4 errorStatus;
    FILE *pMailPipe;

    /* If there are NULL input parameters, try to "fill in the blanks"
    || and mail the message anyway.
    */
    if (indRecipientList == OCI_IND_NULL)
    {
        /* Raise an unnamed programmer-defined exception since a
        || recipients list is required to send a mail message.
        */
        if (OCIExtProcRaiseExcpWithMsg (pWithContext,
                                        (int)20101,
                                        "a recipient list is required",
                                        0) == OCIEXTPROC_SUCCESS)
        {
            /* Return immediately */
            return -1;
        }
        else
        {
    /* Error raising exception, abort this 'extproc' process. */
            abort();
        }
    }

   /* Test for null arguments and supply defaults where appropriate. */
    if (indMessage == OCI_IND_NULL)
    {
        pMessage = "<No Message>";
    }

    if (indSubject == OCI_IND_NULL)
    {
        pSubject = "<No Subject Given>";
    }

    if (indReturnAddress == OCI_IND_NULL)
    {
        /* No return address option requested */
        returnAddrOption[0] = '\0';
    }
    else
    {
        sprintf(returnAddrOption,
                "-r '%s'",
                pReturnAddress);
    }

    /* Now format the mail command line */
    sprintf(mailCmd,
            "/bin/mailx %s -s '%s' %s",
            returnAddrOption,
            pSubject,
            pRecipientList);

    /* Reset the last system error value */
    errno = 0;

    /* Open a pipe to the mail program */
    if ( (pMailPipe = popen (mailCmd, "w")) != NULL )
    {
        if ( fwrite (pMessage, strlen (pMessage), 1, pMailPipe)
             == strlen (pMessage) )
        {
            /* Now close the pipe and return the termination status */
            errorStatus = pclose(pMailPipe);
        }
        else
        {
            /* We couldn't write the message buffer, return the error */
            errorStatus = errno;
            (void) pclose (pMailPipe);
        }
    }
    else
    {
        /* We failed to open a pipe to the command, return the error */
        errorStatus = errno;
    }
    return errorStatus;
}

Compiling and linking this function into a shared object file is similar to the time zone example. Here, the OCI shared library is also included, and a separate call to ld is required:

gcc  -I/oracle/product/8.0.3/rdbms/demo \
     -I/oracle/product/8.0.3/network/public \
     -I/oracle/product/8.0.3/plsql/public \
     -fPIC -Wall -c mail_utl.c
ld -G -h libmail_utl.so -o libmail_utl.so mail_utl.o \
   /oracle/product/8.0.3/lib/libextp.a

Options have the following meaning:

ld

The link command on Solaris

-G

Produce a shared object

-h libmail_utl.so

Record libmail_utl.so as name used by runtime dynamic linker

To test our routine, we can run the following PL/SQL block in SQL*Plus:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
   exit_status  PLS_INTEGER;
BEGIN
   /* This call will succeed and "fill-in" the other NULL parameters */
   exit_status := mail_utl.send_mail (recipient_list => 'fred');
   DBMS_OUTPUT.PUT_LINE ('send_mail exit_status = ' || exit_status);

   /* This call will fail since the recipient list is NULL. Our unnamed
   || programmer-defined exception will be raised as a result.
   */
   exit_status := mail_utl.send_mail (recipient_list => NULL,
      subject_line   => 'Mail from PL/SQL!');
   DBMS_OUTPUT.PUT_LINE ('send_mail exit_status = ' || exit_status);
END;

When we run this test block, the first call should succeed, and we should get a message about exit_status being zero:

send_mail exit_status = 0

The next call fails with a user-defined exception -- an exception generated by the external procedure:

DECLARE
*
ERROR at line 1:
ORA-20101: a recipient list is required
ORA-06512: at "USER8.MAIL_UTL", line 0
ORA-06512: at line 10

It's fortunate that Oracle gives us this ability to raise exceptions, so that our external procedures can participate fully in Oracle's SQL and PL/SQL error-handling model. Be sure to take advantage of it!