If DBMS_OUTPUT is disabled (default setting), then calls to the PUT_LINE and PUT programs are ignored; the buffer remains empty. To enable DBMS_OUTPUT, you will generally execute a command in the host environment. For example, in SQL*Plus, you will issue this command:

SET SERVEROUTPUT ON

Oracle offers a variety of options for the SERVEROUTPUT command; you should check the documentation for the latest features. Here are some things you can do (you can combine the different options into a single command):

Third-party products such as Toad and PL/SQL Developer offer their own graphical interfaces to enabling and disabling DBMS_OUTPUT.

Call DBMS_OUTPUT.PUT_LINE or DBMS_OUTPUT.PUT to put information into the buffer. PUT_LINE adds a newline marker after its text. PUT places text in the buffer without a newline marker. When using DBMS_OUTPUT.PUT, you should make a call to DBMS_OUTPUT.NEW_LINE to append a newline marker to the content that you previously “put.”

If your data can be implicitly converted to a VARCHAR2 string, then you can pass it in your call to the PUT and PUT_LINE programs. Here are some examples:

BEGIN
   DBMS_OUTPUT.put_line ('Steven'),
   DBMS_OUTPUT.put_line (100);
   DBMS_OUTPUT.put_line (SYSDATE);
END;
/

Unfortunately, DBMS_OUTPUT does not yet know what to do with a variety of common PL/SQL types, most notably Booleans. You may therefore want to consider writing a small utility to make it easier to display Boolean values, such as the following procedure, which displays a string and then the Boolean:

/* File on web: plsb.sp */
CREATE OR REPLACE PROCEDURE plsb (str IN VARCHAR2, bool IN BOOLEAN)
IS
BEGIN
   IF bool
   THEN
      DBMS_OUTPUT.PUT_LINE (str || ' - TRUE'),
   ELSIF NOT bool
   THEN
      DBMS_OUTPUT.PUT_LINE (str || ' - FALSE'),
   ELSE
      DBMS_OUTPUT.PUT_LINE (str || ' - NULL'),
   END IF;
END plsb;
/

In Oracle Database 10g Release 2, the maximum size string that you can pass to DBMS_OUTPUT.PUT_LINE has been raised to 32K from the previous limit of 255.

If you pass a value larger than the maximum allowed, Oracle will raise an exception (either VALUE_ERROR or ORU-10028: line length overflow, limit of NNN chars per line). This is not much of a concern with a 32K limit, but prior to Oracle Database 10g Release 2, many of us encountered situations where we needed to display longer strings.

If you would like to avoid this problem, you might want to use an encapsulation of DBMS_OUTPUT.PUT_LINE that automatically wraps long strings. The following files, available on the book’s web site, offer variations on this theme.

The typical usage of DBMS_OUTPUT is very basic: you call DBMS_OUTPUT.PUT_LINE and view the results on the screen. Behind the scenes, the SQL*Plus host environment calls the appropriate programs in the DBMS_OUTPUT package to extract the contents of the buffer and then display it.

If you need to obtain the contents of the DBMS_OUTPUT buffer, you can call the GET_LINE and GET_LINES procedures.

The GET_LINE procedure retrieves one line of information from the buffer (up to the maximum supported by DBMS_OUTPUT), and returns a status value of 0 if successful. Here’s an example that uses this program to extract the next line from the buffer into a local PL/SQL variable:

FUNCTION get_next_line RETURN VARCHAR2
IS
   return_value VARCHAR2(255);
   get_status INTEGER;
BEGIN
   DBMS_OUTPUT.GET_LINE (return_value, get_status);
   IF get_status = 0
   THEN
      RETURN return_value;
   ELSE
      RETURN NULL;
   END IF;
END;

The GET_LINES procedure retrieves multiple lines from the buffer with one call. It reads the buffer into a PL/SQL collection of strings (maximum length 255 or 32K, depending on your version of Oracle). You specify the number of lines you want to read, and it returns those. Here is a generic program that transfers the contents of the DBMS_OUTPUT buffer into a database log table:

/* File on web: move_buffer_to_log.sp */
CREATE OR REPLACE PROCEDURE move_buffer_to_log
IS
   l_buffer      DBMS_OUTPUT.chararr;
   l_num_lines   PLS_INTEGER;
BEGIN
   LOOP
      l_num_lines := 100;
      DBMS_OUTPUT.get_lines (l_buffer, l_num_lines);

      EXIT WHEN l_buffer.COUNT = 0;

      FORALL indx IN l_buffer.FIRST .. l_buffer.LAST
         INSERT INTO logtab
                     (text
                     )
              VALUES (l_buffer (indx)
                     );
   END LOOP;
END;
/

When you call FOPEN to open a file, you must specify both the location and the name of the file in separate arguments. This file location is then checked against the list of accessible directories.

Here’s the format of the parameter for file access in the database initialization file:

UTL_FILE_DIR =directory

Include a parameter for UTL_FILE_DIR for each directory you want to make accessible for UTL_FILE operations. The following entries, for example, enable four different directories in Unix:

UTL_FILE_DIR = /tmp
UTL_FILE_DIR = /ora_apps/hr/time_reporting
UTL_FILE_DIR = /ora_apps/hr/time_reporting/log
UTL_FILE_DIR = /users/test_area

To bypass server security and allow read/write access to all directories, you can use this special syntax:

UTL_FILE_DIR = *

You should not use this option on production systems. In a development system, this entry certainly makes it easier for developers to get up and running on UTL_FILE and test their code. However, you should allow access to only a few specific directories when you move the application to production.

Prior to Oracle9i Database Release 2, whenever you opened a file, you needed to specify the location of the file, as in:

BEGIN
   file_id :=
     UTL_FILE.fopen (
       '/accts/data',
       'trans.dat', 'R'),

Such a hardcoding of values is always to be avoided, however. What if the location of the accounts data changes? How many programs will I have to go fix to make sure everyone is looking in the right place? How many times will I have to make such changes?

A much better approach is to declare a variable or constant and assign it the value of the location. If you do this in a package, the constant can be referenced by any program in a schema with the EXECUTE privilege on that package. Here is an example, followed by a recoding of the earlier FOPEN call:

CREATE PACKAGE accts_pkg
IS
   c_data_location
      CONSTANT VARCHAR2(30) :=
        '/accts/data';
   ...
END accts_pkg;
/
 
BEGIN
   file_id :=
     UTL_FILE.fopen (
       accts_pkg.c_data_location,
       'trans.dat', 'R'),

That’s great. But even better is to use a schema-level object that you can define in the database: a directory. This particular type of object is also used when working with BFILEs, so you can in effect “consolidate” file location references in both DBMS_LOB and UTL_FILE by shifting to directories.

To create a directory, you will need the CREATE ANY DIRECTORY privilege . You then define a new directory as shown in this example:

CREATE OR REPLACE DIRECTORY DEVELOPMENT_DIR AS '/dev/source';

CREATE OR REPLACE DIRECTORY TEST_DIR AS '/test/source';

Here are some things to keep in mind about directories and UTL_FILE:

One advantage of building utilities like those found in fileIO is that you can easily add sophisticated handling of the case of the directory to avoid “formatting errors,” such as forgetting to specify the directory name in uppercase.

Before you can read or write a file, you must open it. The UTL_FILE.FOPEN function opens the specified file and returns a file handle you can then use to manipulate the file. Here’s the header for the function:

FUNCTION UTL_FILE.fopen (location     IN VARCHAR2
   , filename     IN VARCHAR2
   , open_mode    IN VARCHAR2
   , max_linesize IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;

Parameters are summarized in the following table:

You can open the file in one of three modes:

Keep the following points in mind as you attempt to open files:

The following example shows how to declare a file handle and then open a configuration file for that handle in read-only mode:

DECLARE
       config_file UTL_FILE.FILE_TYPE;
BEGIN
       config_file := UTL_FILE.FOPEN (
        '/maint/admin', 'config.txt', 'R'),

Notice that I did not provide a maximum line size when I opened this file. That parameter is, in fact, optional. If you do not provide it, the maximum length of a line you can read from or write to the file is approximately 1,024. Given this limitation, you should always include the max_linesize argument as shown below:

DECLARE
       config_file UTL_FILE.FILE_TYPE;
BEGIN
       config_file := UTL_FILE.FOPEN (
        '/maint/admin', 'config.txt', 'R', max_linesize => 32767);

The IS_OPEN function returns TRUE if the specified handle points to a file that is already open. Otherwise, it returns false. The header for the function is,

FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;

where file is the file to be checked.

Within the context of UTL_FILE, it is important to know what this means. The IS_ OPEN function does not perform any operating system checks on the status of the file. In actuality, it merely checks to see if the id field of the file handle record is not NULL. If you don’t play around with these records and their contents, this id field is set to a non-NULL value only when you call FOPEN. It is set back to NULL when you call FCLOSE.

Use the UTL_FILE.FCLOSE and UTL_FILE.FCLOSE_ALL procedures to close a specific file and all open files in your session, respectively.

Use FCLOSE to close an open file. The header for this procedure is:

PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);

where file is the file handle.

Notice that the argument to UTL_FILE.FCLOSE is an IN OUT parameter because the procedure sets the id field of the record to NULL after the file is closed.

If there is buffered data that has not yet been written to the file when you try to close it, UTL_FILE will raise the WRITE_ERROR exception.

FCLOSE_ALL closes all the opened files. The header for this procedure follows:

PROCEDURE UTL_FILE.FCLOSE_ALL;

This procedure will come in handy when you have opened a variety of files and want to make sure that none of them are left open when your program terminates.

In programs in which files have been opened, you may wish to call FCLOSE_ALL in the exception handlers of those programs. If there is an abnormal termination of the program, files will then still be closed.

EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.FCLOSE_ALL;
      ... other clean up activities ...
END;

When you close your files with the FCLOSE_ALL procedure, none of your file handles will be marked as closed (the id field, in other words, will still be non-NULL). The result is that any calls to IS_OPEN for those file handles will still return TRUE. You will not, however, be able to perform any read or write operations on those files (unless you reopen them).

The UTL_FILE.GET_LINE procedure reads a line of data from the specified file, if it is open, into the provided line buffer. Here’s the header for the procedure:

PROCEDURE UTL_FILE.GET_LINE
   (file IN UTL_FILE.FILE_TYPE,
    buffer OUT VARCHAR2);

Parameters are summarized in the following table:

The variable specified for the buffer parameter must be large enough to hold all the data up to the next carriage return or end-of-file condition in the file. If not, PL/SQL will raise the VALUE_ERROR exception. The line terminator character is not included in the string passed into the buffer.

Here is an example that uses GET_LINE:

DECLARE
   strbuffer VARCHAR2(32767);
   mynum NUMBER;
BEGIN
   fileID := UTL_FILE.FOPEN (
     'TEMP_DIR', 'numlist.txt', 'R', max_linesize => 32767);
   UTL_FILE.GET_LINE (fileID, strbuffer);
   mynum := TO_NUMBER (strbuffer);
END;
/

Because GET_LINE reads data only into a string variable, you will have to perform your own conversions to local variables of the appropriate datatype if your file holds numbers or dates.

DECLARE
   l_file   UTL_FILE.file_type;
   l_line   VARCHAR2 (32767);
BEGIN
   l_file := UTL_FILE.fopen ('TEMP', 'names.txt', 'R'),
 
   LOOP
      UTL_FILE.get_line (l_file, l_line);
      process_line (l_line);
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      UTL_FILE.fclose (names_file);
END;

/* File on web: getnext.sp */
PROCEDURE get_nextline (
   file_in IN UTL_FILE.FILE_TYPE
 , line_out OUT VARCHAR2
 , eof_out OUT BOOLEAN)
IS
BEGIN
   UTL_FILE.GET_LINE (file_in, line_out);
   eof_out := FALSE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      line_out := NULL;
      eof_out  := TRUE;
END;

DECLARE
   l_file   UTL_FILE.file_type;
   l_line   VARCHAR2 (32767);
   l_eof    BOOLEAN;
BEGIN
   l_file := UTL_FILE.fopen ('TEMP', 'names.txt', 'R'),
 
   LOOP
      get_nextline (l_file, l_line, l_eof);
      EXIT WHEN l_eof;
      process_line (l_line);
   END LOOP;

   UTL_FILE.fclose (l_file);
END;

In contrast to the simplicity of reading from a file, UTL_FILE offers a number of different procedures you can use to write to a file:

You can use these procedures only if you have opened your file with modes Wor A; if you opened the file for read-only, the runtime engine raises the UTL_ FILE.INVALID_OPERATION exception.

Let’s take a closer look at UTL_FILE.PUT_LINE. This procedure writes data to a file and then immediately appends a newline character after the text. Here’s the header for PUT_LINE:

PROCEDURE UTL_FILE.PUT_LINE (file IN UTL_FILE.FILE_TYPE,
   ,buffer IN VARCHAR2,
   ,autoflush IN BOOLEAN DEFAULT FALSE)

Parameters are summarized in the following table:

Before you can call UTL_FILE.PUT_LINE, you must have already opened the file.

Here is an example that uses PUT_LINE to dump the names of all our employees to a file:

PROCEDURE names_to_file
IS
   fileid   UTL_FILE.file_type;
BEGIN
   fileid := UTL_FILE.fopen ('TEMP', 'names.dat', 'W'),
 
   FOR emprec IN (SELECT *
                    FROM employee)
   LOOP
      UTL_FILE.put_line (
         fileid
       , emprec.first_name || ' ' || emprec.last_name);
   END LOOP;
 
   UTL_FILE.fclose (fileid);
END names_to_file;

A call to PUT_LINE is equivalent to a call to PUT followed by a call to NEW_LINE. It is also equivalent to a call to PUTF with a format string of “%s ” (see the description of PUTF in the next section).

PROCEDURE UTL_FILE.putf
    (file IN FILE_TYPE
    ,format IN VARCHAR2
    ,arg1 IN VARCHAR2 DEFAULT NULL
    ,arg2 IN VARCHAR2 DEFAULT NULL
    ,arg3 IN VARCHAR2 DEFAULT NULL
    ,arg4 IN VARCHAR2 DEFAULT NULL
    ,arg5 IN VARCHAR2 DEFAULT NULL);

Employee: Steven Feuerstein
Soc Sec #: 123-45-5678
Salary: $1000

UTL_FILE.putf
   (file_handle, 'Employee: %s
Soc Sec #: %s
Salary: %s',
    'Steven Feuerstein',
    '123-45-5678',
    TO_CHAR (:employee.salary, '$9999'));

UTL_FILE.FCOPY lets you easily copy the contents of one source file to another destination file. The following snippet, for example, uses UTL_FILE.FCOPY to perform a backup by copying a single file from the development directory to the archive directory:

DECLARE
   file_suffix   VARCHAR2 (100)
            := TO_CHAR (SYSDATE, 'YYYYMMDDHHMISS'),
BEGIN
   -- Copy the entire file...
   UTL_FILE.fcopy (
      src_location       => 'DEVELOPMENT_DIR',
      src_filename       => 'archive.zip',
      dest_location      => 'ARCHIVE_DIR',
      dest_filename      =>    'archive'
                            || file_suffix
                            || '.zip'
   );
END;

You can also use FCOPY to copy just a portion of a file. The program offers two additional parameters that allow you to specify the starting and ending line numbers you want to copy from the file. Suppose that I have a text file containing the names of the winners of a monthly PL/SQL quiz that started in January 2000. I would like to transfer all the names in 2001 to another file. I can do that by taking advantage of the fifth and sixth arguments of the FCOPY procedure as shown below:

DECLARE
   c_start_year PLS_INTEGER := 2000;
   c_year_of_interest PLS_INTEGER := 2001;
   l_start PLS_INTEGER;
   l_end PLS_INTEGER;
BEGIN
   l_start := (c_year_of_interest - c_start_year)*12 + 1;
   l_end := l_start + 11;
 
   UTL_FILE.fcopy (
      src_location       => 'WINNERS_DIR',
      src_filename       => 'names.txt',
      dest_location      => 'WINNERS_DIR',
      dest_filename      => 'names2001.txt',
      start_line         => l_start,
      end_line           => l_end
   );
END;

A useful encapsulation to UTL_FILE.FCOPY allows me to specify start and end strings instead of line numbers. We will leave the implementation of such a utility as an exercise for the reader (see the infile.sf file on the book’s web site for an implementation of an “INSTR for files” that might give you some ideas on implementation).

Beginning with Oracle9i Database Release 2, you can remove files using UTL_FILE.FREMOVE. The header for this program is:

PROCEDURE UTL_FILE.fremove (location IN VARCHAR2,
   filename IN VARCHAR2);

That’s simple enough. You provide the location and name of the file, and UTL_FILE attempts to delete it. What if UTL_FILE encounters a problem? You might see one of the following exceptions then raised:

In other words, UTL_FILE will raise an exception if you try to remove a file that doesn’t exist or if you do not have the privileges needed to remove the file. Many file-removal programs in other languages (for example, File.delete in Java) return a status code to inform you of the outcome of the removal attempt. If you prefer this approach, you can use (or copy) the fileIO.fremove program found in the fileio.pkg file on the book’s web site.

I can combine copy and remove operations into a single step by calling the UTL_FILE.RENAME procedure. This handy utility allows me to either rename a file in the same directory or to rename a file to another name and location (in effect, moving that file).

Here is the header for FRENAME:

PROCEDURE UTL_FILE.frename (src_location   IN VARCHAR2,
   src_filename   IN VARCHAR2,
   dest_location  IN VARCHAR2,
   dest_filename  IN VARCHAR2,
   overwrite      IN BOOLEAN DEFAULT FALSE);

This program may raise one of the following exceptions:

You will find an interesting application of FRENAME in the fileIO package—the chgext procedure. This program changes the extension of the specified file.

Sometimes we need to get information about the file in question: How big is this file? Does a file even exist? What is the block size of my file? Such questions are not mysteries that can only be solved with the help of an operating system command (or, in the case of the file length, the DBMS_LOB package), as they were in early Oracle releases. UTL_FILE.FGETATTR provides us with that information in a single native procedure call.

Here is the header for FGETATTR:

PROCEDURE UTL_FILE.fgetattr (location    IN VARCHAR2,
   filename    IN VARCHAR2,
   fexists     OUT BOOLEAN,
   file_length OUT NUMBER,
   block_size  OUT BINARY_INTEGER);

Thus, to use this program, we must declare three different variables to hold the Boolean flag (does the file exist?), the length of the file, and the block size. Here is a sample usage:

DECLARE
   l_fexists      BOOLEAN;
   l_file_length  PLS_INTEGER;
   _block_size    PLS_INTEGER;
BEGIN
   UTL_FILE.fgetattr (
      location    => DEVELOPMENT_DIR,
      filename    => 'bigpkg.pkg',
      fexists     => l_fexists,
      file_length => l_file_length,
      block_size  => l_block_size
   );
   ...
END;

This interface is a bit awkward. Suppose we just want to find out the length of this file? We still have to declare all those variables, obtain the length, and then work with that value. Perhaps the best way to take advantage of FGETATTR is to build some of your own functions on top of this built-in that answer a single question, such as:

FUNCTION fileIO.flength (
   location_in   IN   VARCHAR2,
   file_in       IN   VARCHAR2
   )
   RETURN PLS_INTEGER;

or:

FUNCTION fileIO.fexists (
   location_in IN VARCHAR2,
   file_in IN VARCHAR2
   )
      RETURN BOOLEAN;

As a result, you do not have to declare unneeded variables, and you can write simpler, cleaner code.

Unfortunately, not all versions of Oracle provide email-from-PL/SQL that works out of the box. Let’s take a look at the setup requirements for different versions:

In the previous section, the first example showed how to send a plaintext message if you have UTL_MAIL at your disposal. If, however, you are using UTL_SMTP, your program will have to communicate with the mail server at a lower programmatic level: opening the connection, composing the headers, sending the body of the message, and (ideally) examining the return codes. To give you a flavor of what this looks like, Figure 21-1 shows a sample conversation between a mail server and a PL/SQL mail client I’ve named send_mail_via_utl_smtp.

Here is the code for this simple stored procedure:

/* File on web: send_mail_via_utl_smtp.sp */
1    CREATE OR REPLACE PROCEDURE send_mail_via_utl_smtp
2      ( sender IN VARCHAR2
3       ,recipient IN VARCHAR2
4       ,subject IN VARCHAR2 DEFAULT NULL
5       ,message IN VARCHAR2
6       ,mailhost IN VARCHAR2 DEFAULT 'mailhost'
7       )
8    IS
9      mail_conn UTL_SMTP.connection;
10      crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
11      smtp_tcpip_port CONSTANT PLS_INTEGER := 25;
12    BEGIN
13      mail_conn := UTL_SMTP.OPEN_CONNECTION(mailhost, smtp_tcpip_port);
14      UTL_SMTP.HELO(mail_conn, mailhost);
15      UTL_SMTP.MAIL(mail_conn, sender);
16      UTL_SMTP.RCPT(mail_conn, recipient);
17      UTL_SMTP.DATA(mail_conn, SUBSTR(
18         'Date: ' || TO_CHAR(SYSTIMESTAMP, 'Dy, dd Mon YYYY HH24:MI:SS TZHTZM')
19         || crlf || 'From: ' || sender || crlf
20         || 'Subject: ' || subject || crlf
21         || 'To: ' || recipient || crlf
22         || message
23       , 1, 32767));
24     
25      UTL_SMTP.QUIT(mail_conn);
26    END;

Figure 21-1. A “conversation” between the PL/SQL mail client and SMTP server

The following table explains a few concepts of this code:

If you look at lines 17-23, you’ll see that this procedure cannot send a message whose “DATA” part exceeds 32,767 bytes, which is the limit of PL/SQL variables. It’s possible to send longer emails using UTL_SMTP, but you will need to stream the data using multiple calls to UTL_SMTP.WRITE_DATA, as shown later.

If I were to invoke the previous procedure like this:

CALL send_mail_via_utl_smtp('[email protected]',
   '[email protected]', 'mail demo', NULL);

the “normally” visible headers of the email, as generated by lines 17-21, will show up something like this:

Date: Wed, 23 Mar 2005 17:14:30 -0600
From: [email protected]
Subject: mail demo
To: [email protected]

Most humans (and many antispam programs) prefer to see real names in the headers, in a the form such as:

Date: Wed, 23 Mar 2005 17:14:30 -0600
From:Bob Swordfish <[email protected]>
Subject: mail demo
To: "Scott Tiger, Esq." <[email protected]>

There is, of course, more than one way to make this change; perhaps the most elegant would be to add some parsing to the sender and recipient parameters. This is what Oracle has done in UTL_MAIL. So, for example, I can call UTL_MAIL.SEND with addresses of the form:

["]Friendly name["] <email_address>

as in:

BEGIN
   UTL_MAIL.send('Bob Swordfish <[email protected]>',
      '"Scott Tiger, Esq." <[email protected]>',
      subject=>'mail demo'),
END;

However, you need to realize that Oracle’s package also adds character set information, so the previous code generates an email header that looks something like this:

Date: Tue, 10 May 2005 17:40:37 -0500 (CDT)
From: =?ISO-8859-1?Q?Bob=20Swordfish=20<[email protected]>?=
To: =?ISO-8859-1?Q?"Scott=20Tiger,=20Esq."=20<[email protected]>?=
Subject: =?ISO-8859-1?Q?mail=20demo?=

While that looks odd to most ASCII speakers, it is completely acceptable in Internet-standards-land; an intelligent mail client should interpret (rather than display) the character set information anyway.

One quick and dirty modification of the send_mail_via_utl_smtp procedure would simply be to add parameters for the friendly names (or change the existing parameters to record structures).

UTL_MAIL is pretty handy, but if you want to send a text message larger than 32,767 bytes, it won’t help you. One way around this limitation would be to modify our send_mail_via_utl_smtp procedure so that the “message” parameter is a CLOB datatype. Take a look at the other changes required:

/* File on web: send_clob.sp */
CREATE OR REPLACE PROCEDURE send_clob
   ( sender IN VARCHAR2
    ,recipient IN VARCHAR2
    ,subject IN VARCHAR2 DEFAULT NULL
    ,message INCLOB
    ,mailhost IN VARCHAR2 DEFAULT 'mailhost'
    )
IS
   mail_conn  UTL_SMTP.connection;
   crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
   smtp_tcpip_port CONSTANT PLS_INTEGER := 25;
   pos PLS_INTEGER := 1;
   bytes_o_data CONSTANT PLS_INTEGER := 32767;
   offset PLS_INTEGER := bytes_o_data;
   msg_length CONSTANT PLS_INTEGER := DBMS_LOB.getlength(message);
BEGIN
   mail_conn := UTL_SMTP.open_connection(mailhost, smtp_tcpip_port);
   UTL_SMTP.helo(mail_conn, mailhost);
   UTL_SMTP.mail(mail_conn, sender);
   UTL_SMTP.rcpt(mail_conn, recipient);
 
   UTL_SMTP.open_data(mail_conn);
   UTL_SMTP.write_data(mail_conn,
       'Date: ' || TO_CHAR(SYSTIMESTAMP, 'Dy, dd Mon YYYY HH24:MI:SS TZHTZM') || crlf
       || 'From: ' || sender || crlf
       || 'Subject: ' || subject || crlf
       || 'To: ' || recipient || crlf || crlf);
 
   WHILE pos < msg_length
   LOOP
      utl_smtp.write_data(mail_conn, DBMS_LOB.SUBSTR(message, offset, pos));
      pos  := pos + offset;
      offset := LEAST(bytes_o_data, msg_length - offset);
   END LOOP;
   UTL_SMTP.close_data(mail_conn);
 
   UTL_SMTP.QUIT(mail_conn);
END;

Using open_data, write_data, and close_data allows you to transmit an arbitrary number of bytes to the mail server (up to whatever limit the server imposes on email size). Note the one big assumption that this code is making: that the CLOB has been properly split into lines of the correct length.

Let’s next take a look at how to attach a file to an email.

The original email standard required all messages to be composed of seven-bit U.S. ASCII characters.^[*] But we all know that emails can include attachments—such as viruses and word-processing documents—and these kinds of files are normally binary, not text. How can an ASCII message transmit a binary file? The answer, in general, is that attachments are transmitted using mail extensions known as MIME^[†] in combination with a binary-to-ASCII translation scheme such as base64. To see MIME in action, let’s take a look at an email that transmits a tiny binary file:

Date: Fri, 01 Apr 2005 10:16:51 -0600
From: Bob Swordfish <[email protected]>
MIME-Version: 1.0
To: Scott Tiger <[email protected]>
Subject: Attachment demo
Content-Type: multipart/mixed;
 boundary="------------060903040208010603090401"
 
This is a multi-part message in MIME format.
--------------060903040208010603090401
Content-Type: text/plain; charset=us-ascii; format=fixed
Content-Transfer-Encoding: 7bit
 
Dear Scott:
 
I'm sending a gzipped file containing the text of the first
paragraph. Hope you like it.
 
Bob
--------------060903040208010603090401
Content-Type: application/x-gzip; name="hugo.txt.gz"
Content-Transfer-Encoding: base64
Content-Disposition: inline; filename="hugo.txt.gz"
 
H4sICDh/TUICA2xlc21pcy50eHQAPY5BDoJAEATvvqI/AJGDxjMaowcesbKNOwmZITsshhf7
DdGD105Vpe+K5tQc0Jm6sGScU8gjvbrmoG8Tr1qhLtSCbs3CEa/gaMWTTbABF3kqa9z42+dE
RXhYmeHcpHmtBlmIoBEpREyZLpERtjB/aUSxns5/Ci7ac/u0P9a7Dw4FECSdAAAA
--------------060903040208010603090401--

Although a lot of the text can be boilerplated, it’s still a lot of details to have to deal with when you generate the email. Fortunately, if you just want to send a “small” attachment (less than 32K), and you have Oracle Database 10g or later, UTL_MAIL comes to the rescue. In this next example, we’ll use UTL_MAIL.SEND_ATTACH_VARCHAR2, which sends attachments that are expressed as text.

The previous message and file can be sent as follows:

DECLARE
   b64 VARCHAR2(512) := 'H4sICDh/TUICA2xlc21...'; -- etc., as above
   txt VARCHAR2(512) := 'Dear Scott: ...'; -- etc., as above
BEGIN
   UTL_MAIL.send_attach_varchar2(
      sender => '[email protected]'
     ,recipients => '[email protected]'
     ,message => txt
     ,subject => 'Attachment demo'
     ,att_mime_type => 'application/x-gzip'
     ,attachment => b64
     ,att_inline => TRUE
     ,att_filename => 'hugo.txt.gz'
   );
END;

Here are the new parameters:

The MIME type isn’t just something you make up; it’s loosely governed, like so many things on the Internet, by the Internet Assigned Numbers Authority (IANA) . Table 21-1 displays some common MIME types.

For more information on all MIME types and links to the RFCs that will help you create the MIME headers visit IANA’s web page: http://www.iana.org/assignments/media-types/.

Returning to PL/SQL business, you may have noticed that there was quite a bit of hand-waving earlier to attach a base64-encoded file to an email. Let’s take a closer look at the exact steps required to convert a binary file into something you can send to an inbox.

To have Oracle convert a small binary file to something that can be emailed, you can read the contents of the file into a RAW variable, and use UTL_MAIL.SEND_ATTACH_RAW. This causes Oracle to convert the binary data to base64 and properly construct the MIME directives. If the file you want to send is in /tmp/hugo.txt.gz (and is less than 32K in size), you might specify:

CREATE OR REPLACE DIRECTORY tmpdir AS '/tmp';
 
DECLARE
   the_file BFILE := BFILENAME('TMPDIR', 'hugo.txt.gz'),
   rawbuf RAW(32767);
   amt PLS_INTEGER :=  32767;
   offset PLS_INTEGER := 1;
BEGIN
   DBMS_LOB.fileopen(the_file, DBMS_LOB.file_readonly);
   DBMS_LOB.read(the_file, amt, offset, rawbuf);
   UTL_MAIL.send_attach_raw 
(
      sender => '[email protected]'
     ,recipients => '[email protected]'
     ,subject => 'Attachment demo'
     ,message => 'Dear Scott...'
     ,att_mime_type => 'application/x-gzip'
     ,attachment => rawbuf
     ,att_inline => TRUE
     ,att_filename => 'hugo.txt.gz'
   );
 
   DBMS_LOB.close(the_file);
END;

If you don’t have UTL_MAIL, follow the instructions in the next section.

To send a larger attachment, you can use the trusty UTL_SMTP package; if the attachment is not text, you can perform a base64 conversion with Oracle’s built-in UTL_ENCODE package . Here is an example procedure that sends a BFILE along with a short text message:

/* File on web: send_bfile.sp */
1    CREATE OR REPLACE PROCEDURE send_bfile
2      ( sender IN VARCHAR2
3       ,recipient IN VARCHAR2
4       ,subject IN VARCHAR2 DEFAULT NULL
5       ,message IN VARCHAR2 DEFAULT NULL
6       ,att_bfile IN OUT BFILE
7       ,att_mime_type IN VARCHAR2
8       ,mailhost IN VARCHAR2 DEFAULT 'mailhost'
9       )
10    IS
11      crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
12      smtp_tcpip_port CONSTANT PLS_INTEGER := 25;
13      bytes_per_read CONSTANT PLS_INTEGER := 23829;
14      boundary CONSTANT VARCHAR2(78) := '-------5e9i1BxFQrgl9cOgs90-------';
15      encapsulation_boundary CONSTANT VARCHAR2(78) := '--' || boundary;
16      final_boundary CONSTANT VARCHAR2(78) := '--' || boundary || '--';
17     
18      mail_conn  UTL_SMTP.connection;
19      pos PLS_INTEGER := 1;
20      file_length PLS_INTEGER;
21     
22      diralias VARCHAR2(30);
23      bfile_filename VARCHAR2(512);
24      lines_in_bigbuf PLS_INTEGER := 0;
25     
26      PROCEDURE writedata (str IN VARCHAR2, crlfs IN PLS_INTEGER DEFAULT 1)
27      IS
28      BEGIN
29         UTL_SMTP.write_data(mail_conn, str || RPAD(crlf, 2 * crlfs, crlf));
30      END;
31     
32    BEGIN
33      DBMS_LOB.fileopen(att_bfile, DBMS_LOB.LOB_READONLY);
34      file_length := DBMS_LOB.getlength(att_bfile);
35     
36      mail_conn := UTL_SMTP.open_connection(mailhost, smtp_tcpip_port);
37      UTL_SMTP.helo(mail_conn, mailhost);
38      UTL_SMTP.mail(mail_conn, sender);
39      UTL_SMTP.rcpt(mail_conn, recipient);
40     
41      UTL_SMTP.open_data(mail_conn);
42      writedata('Date: ' || TO_CHAR(SYSTIMESTAMP,
43                   'Dy, dd Mon YYYY HH24:MI:SS TZHTZM') || crlf
44          || 'MIME-Version: 1.0' || crlf
45          || 'From: ' || sender || crlf
46          || 'Subject: ' || subject || crlf
47          || 'To: ' || recipient || crlf
48          || 'Content-Type: multipart/mixed; boundary="' || boundary || '"', 2
49     
50      writedata(encapsulation_boundary);
51      writedata('Content-Type: text/plain; charset=ISO-8859-1; format=flowed')
52      writedata('Content-Transfer-Encoding: 7bit', 2);
53      writedata(message, 2);
54     
55      DBMS_LOB.filegetname(att_bfile, diralias, bfile_filename);
56      writedata(encapsulation_boundary);
57      writedata('Content-Type: '
58         || att_mime_type || '; name="' || bfile_filename || '"'),
59      writedata('Content-Transfer-Encoding: base64'),
60      writedata('Content-Disposition: attachment; filename="'
61         || bfile_filename || '"', 2);
62     
63      WHILE pos < file_length
64      LOOP
65         writedata(UTL_RAW.cast_to_varchar2(
66                     UTL_ENCODE.base64_encode 
(
67                        DBMS_LOB.substr(att_bfile, bytes_per_read, pos))), 0);
68         pos := pos + bytes_per_read;
69      END LOOP;
70     
71      writedata(crlf || crlf || final_boundary);
72     
73      UTL_SMTP.close_data(mail_conn);
74      UTL_SMTP.QUIT(mail_conn);
75      DBMS_LOB.CLOSE(att_bfile);
76    END;

Let’s take a look at a few highlights:

I know what you’re thinking: I, too, used to think sending email was easy. And this procedure doesn’t even provide much flexibility; it lets you send one text part and attach one file. But it provides a starting point you can extend for your own application’s needs.

One more point about crafting well-formed emails: rather than reading yourself to sleep with the RFCs, you may prefer to pull out the email client you use every day, send yourself an email of the form you are trying to generate, and then view the underlying “source text” of the message. It worked for me; I did that many times while writing this section of the book!

One of the first procedures that Oracle ever released in the UTL_HTTP package retrieves a web page into consecutive elements of an associative array. Usage can be pretty simple:

DECLARE
   page_pieces UTL_HTTP.html_pieces; -- array of VARCHAR2(2000)
BEGIN
   page_pieces := UTL_HTTP.request_pieces(
      url => 'http://www.oreilly.com/'),
END;

I confess that I’ve never found the data retrieved in this format terribly fun to work with, because the 2,000-byte boundaries are unrelated to anything you would find on the text of the page. So if you have a parsing algorithm that needs a line-by-line approach, you will have to read and reassemble the lines. Moreover, Oracle says that it may not fill all of the (nonending) pieces to 2,000 bytes; Oracle’s algorithm does not use end-of-line boundaries as breaking points; and the maximum number of pieces is 32,767.

Even if an array-based retrieval meets your needs, you will likely encounter web sites where the above code just won’t work. For example, some sites would refuse to serve their content to such a script, because Oracle’s default HTTP “header” looks unfamiliar to the web server. In particular, the “User-Agent” header is a text string that tells the web server the browser software the client is using (or emulating), and many web sites are set up to provide content specific to certain browsers. But by default, Oracle does not send a User-Agent. A commonly used and supported header you might want to use is:

User-Agent: Mozilla/4.0

Sending this header does increase the complexity of the code you must write, because doing so means you must code at a lower level of abstraction; in particular, you must initiate a “request,” send your header, get the “response,” and retrieve the page in a loop:

DECLARE
   req UTL_HTTP.req;    -- a "request object" (actually a PL/SQL record)
   resp UTL_HTTP.resp;  -- a "response object" (also a PL/SQL record)
   buf VARCHAR2(32767); -- buffer to hold data from web page
BEGIN
   req := UTL_HTTP.begin_request('http://www.oreilly.com/',
      http_version => UTL_HTTP.http_version_1_1);
   UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0'),
   resp := UTL_HTTP.get_response(req);
 
   BEGIN
      LOOP
         UTL_HTTP.read_text(resp, buf);-- process buf here; e.g., store in array
      END LOOP;
   EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN
         NULL;
   END;
   UTL_HTTP.end_response(resp);
END;

The heart of the code above is this built-in:

PROCEDURE UTL_HTTP.read_text(r IN OUT NOCOPY UTL_HTTP.resp,
   data OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
   len IN PLS_INTEGER DEFAULT NULL);

If len is NULL, Oracle will fill the buffer up to its maximum size until reaching the end of the page, after which point the read operation raises the UTL_HTTP.end_of_body exception as above. (Yes, this goes against a coding practice that normal operations should not raise exceptions.) Each iteration through the loop, you will need to process the buffer, perhaps by appending it to a LOB.

You can also use the line-by-line retrieval using READ_LINE rather than READ_TEXT:

PROCEDURE UTL_HTTP.read_line(r IN OUT NOCOPY UTL_HTTP.resp,
   data OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
   remove_crlf IN BOOLEAN DEFAULT FALSE);

This built-in reads one line of source text at a time, optionally cutting off the end-of-line characters. The caveat with READ_LINE is that each line you fetch from the http server needs to be less than 32K in length. Such an assumption is not always a good one, so don’t use READ_LINE unless you are sure this limit won’t cause a problem.

Because reading either by “pieces” or by lines can run into various size limits, you may decide that it would make more sense to read into LOBs. Again, Oracle provides a very simple call that may meet your needs. You can retrieve an entire page at once into a single data structure using the HTTPURITYPE built-in object type:

DECLARE
   text CLOB;
BEGIN
   text := HTTPURITYPE('http://www.oreilly.com').getclob;
END;

If you are retrieving a binary file and you want to put it in a BLOB, you can use getblob():

DECLARE
   image BLOB;
BEGIN
   image :=
      HTTPURITYPE('www.oreilly.com/catalog/covers/oraclep4.s.gif').getblob;
END;

The HTTPURITYPE constructor is fairly forgiving regarding the presence of the http:// prefix (it assumes HTTP as the transport protocol).

Again, the simple solution won’t transmit a User-Agent; if you need custom headers when retrieving your LOB, you can use this:

/* File on web: url_to_clob.sql */
DECLARE
   req UTL_HTTP.req;
   resp UTL_HTTP.resp;
   buf VARCHAR2(32767);pagelob CLOB;
BEGIN
   req := UTL_HTTP.begin_request('http://www.oreilly.com/',
      http_version => UTL_HTTP.http_version_1_1);
   UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0'),
   resp := UTL_HTTP.get_response(req);
   DBMS_LOB.createtemporary(pagelob, TRUE);
   BEGIN
      LOOP
         UTL_HTTP.read_text(resp, buf);
         DBMS_LOB.writeappend(pagelob, LENGTH(buf), buf);
      END LOOP;
   EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN
         NULL;
   END;
   UTL_HTTP.end_response(resp);
 
   ...here is where you parse, store, or otherwise process the LOB
 
   DBMS_LOB.freetemporary(pagelob);
END;

Although many web sites such as Amazon and eBay use a custom HTML form for login and authentication, there are still a lot of sites (such as Oracle’s own Metalink support site) that use HTTP authentication , more precisely known as basic authentication . You will recognize such sites by your browser client’s behavior; it will pop up a modal dialog box requesting your username and password.

According to Internet standards,^[*] you should be able to bypass the dialog by inserting your username and password in the URL in the form:

http://username:password@some.site.com

If you are running 9.2.0.4 or later, both UTL_HTTP and HTTPURITYPE support this syntax. Consider the case of logging in to Oracle Metalink:

DECLARE
   webtext clob;
   user_pass VARCHAR2(64) := 'bob:swordfish'; -- replace with your own!
   url VARCHAR2(128) := 'metalink.oracle.com/metalink/plsql/ml2_gui.startup';
BEGIN
   webtext := HTTPURITYPE(user_pass || '@' || url).getclob;
END;
/

If encoding the username and password in the URL doesn’t work, try something along these lines:

...
   req := UTL_HTTP.begin_request('http://some.site.com/'),UTL_HTTP.set_authentication(req, 'bob', 'swordfish'),
   resp := UTL_HTTP.get_response(req);
...

This works as long if the site does not encrypt the login page.

Although HTTPURITYPE does not support SSL-encrypted retrievals, UTL_HTTP will do the job if you set up an Oracle Wallet . An Oracle Wallet just a catchy name for a file that contains security certificates and, optionally, public/private key pairs . It’s the former (the certificates) that you need for https retrievals. You can store one or more wallets as files in the database server’s filesystem or in an LDAP directory service; Oracle does not install any wallets by default.

To set up one of these wallet things, you’ll want to fire up Oracle’s GUI utility known as Oracle Wallet Manager , which is probably named owm on Unix hosts or will appear on your Start→Oracle... menu on Microsoft Windows. Once you’ve got Oracle Wallet Manager running, the basic steps you need to follow^[*] are:

Now try something like this:

DECLARE
   req UTL_HTTP.req;
   resp UTL_HTTP.resp;
BEGIN
   UTL_HTTP.set_wallet('file:/oracle/wallets', 'password1'),
   req := UTL_HTTP.begin_request('https://www.entrust.com/'),
   UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0'),
   resp := UTL_HTTP.get_response(req);
   UTL_HTTP.end_response(resp);
END;

If you don’t get an error message, you can reward yourself with a small jump for joy. This ought to work, because Entrust is one of the few authorities whose certificate Oracle includes by default when you create a wallet.

If you want to retrieve data from another https site whose public certificate doesn’t happen to be on Oracle’s list, you can fire up Oracle Wallet Manager again and “import” the certificate into your file, and again put it on the server. To download a certificate in a usable format, you can use Microsoft Internet Explorer and follow these steps:

Or, if you have openssl installed, you could do this:

echo '' | openssl s_client -connecthost:port

which will spew all kinds of information to stdout; just save the text between the BEGIN CERTIFICATE and END CERTIFICATE lines (inclusive) to a file. And by the way, the normal port for https is 443.

Remember that those certificates are not in an Oracle wallet until you import them via Oracle Wallet Manager. And in case that you’re wondering, a wallet can have more than one certificate, and a wallet directory can hold one or more wallets.

Sometimes, you’ll want to retrieve results from a web site as if you had filled out a form in your browser and pressed the Submit button. This section will show a few examples that use UTL_HTTP for this purpose, but many web sites are quirky and require quite a bit of fiddling about to get things working right. Some of the tools you may find useful while analyzing the behavior of your target site include:

First, let’s look some simple code you can use to query Google. As it turns out, Google’s main page uses a single HTML form:

<form action=/search name=f>

Because the method tag is omitted, it defaults to GET. The single text box on the form is named “q”:

<input maxLength=256 size=55 name=q value="">

You can encode the GET request directly in the URL as follows:

http://www.google.com/search?client=googlet&q= query

Given this information, here is the programmatic equivalent of searching for “oracle pl/sql programming” (including the double quotes) using Google:

SET DEFINE OFF
DECLARE
   url VARCHAR2(64)
      := 'http://www.google.com/search?client=googlet&q=';
   qry VARCHAR2(128) := UTL_URL.escape('"oracle pl/sql programming"'),
   result CLOB;
BEGIN
   result := HTTPURITYPE(url || qry).getclob;
END;
/

Oracle’s handy UTL_URL.ESCAPE function transforms the query by translating spaces and double quotes into their hex equivalents. If you’re curious, the escaped text from the example is:

%22oracle%20pl/sql%20programming%22

Let’s take a look at using POST in a slightly more complicated example. When I looked at the source HTML for http://www.apache.org, I found that their search form’s “action” was http://search.apache.org, that the form uses the POST method, and that their search box is named “query”. With POST, you cannot simply append the data to the URL as with GET; instead you send it to the web server in a particular form. Here is some code that POSTs a search for the string “oracle pl/sql” (relevant additions highlighted):

DECLARE
   req UTL_HTTP.req;
   resp UTL_HTTP.resp;
   qry VARCHAR2(512) := UTL_URL.escape('query=oracle pl/sql'),
BEGIN
   req := UTL_HTTP.begin_request('http://search.apache.org/', 'POST', 'HTTP 
/1.0');
   UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0'),
   UTL_HTTP.set_header(req, 'Host', 'search.apache.org'),
   UTL_HTTP.set_header(req, 'Content-Type', 'application/x-www-form-urlencoded'),
   UTL_HTTP.set_header(req, 'Content-Length', TO_CHAR(LENGTH(qry)));
   UTL_HTTP.write_text(req, qry);
   resp := UTL_HTTP.get_response(req);
 
  ...now we can retrieve the results as before (e.g., line by line)
 
   UTL_HTTP.end_response(resp);
END;
/

In a nutshell, the BEGIN_REQUEST includes the POST directive, and the code uses write_text to transmit the form data. While POST does not allow the name/value pairs to be appended to the end of the URL (like GET queries), this site allows the x-www-form-urlencoded content type , allowing name/value pairs in the qry variable that we send to the server.

UTL_HTTP.set_header(req, 'Host', 'search.apache.org'),

By the way, if you have more than one item in the form to fill out, URL encoding says that each name/value pair must be separated with an ampersand:

name1=value1&name2=value2&name3= ...

Okay, you’ve got all your GETs and POSTs working now, so you are all set to go forth and fetch...right? Possibly. It’s likely your code will sooner or later run afoul of the HTTP “redirect.” This is a special return code that web servers send, which means “sorry, you need to go over there to find what you are looking for.” We are accustomed to letting our browsers handle redirects for us silently and automatically, but it turns out that the underlying implementation can be tricky: there are at least five different kinds of redirect, each with slightly different rules about what is “legal” for the browser to do. You may encounter redirects with any web page, but for many of them you should be able to use a feature in UTL_HTTP to follow redirects. That is:

UTL_HTTP.set_follow_redirect 
 (max_redirects IN PLS_INTEGER DEFAULT 3);

Unfortunately, while testing code to retrieve a weather forecast page from the U.S. National Weather Service, I discovered that their server responds to a POST with a 302“Found” redirect. This is an odd case in the HTTP standard, which holds that clients should not follow the redirect...and Oracle’s UTL_HTTP adheres to the letter of the standard, at least in this case.

So, I have to ignore the standard to get something useful from the weather page. My final program to retrieve the weather in Sebastopol, California appears below:

/* File on web: orawx.sp */
CREATE OR REPLACE PROCEDURE orawx
AS
   req UTL_HTTP.req;
   resp UTL_HTTP.resp;
   line VARCHAR2(32767);
   formdata VARCHAR2(512) := 'inputstring=95472';   -- zip code
   newlocation VARCHAR2(1024);
BEGIN
   req := UTL_HTTP.begin_request('http://www.srh.noaa.gov/zipcity.php',
            'POST', UTL_HTTP.http_version_1_0);
   UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0'),
   UTL_HTTP.set_header(req, 'Content-Type', 'application/x-www-form-urlencoded'),
   UTL_HTTP.set_header(req, 'Content-Length', TO_CHAR(LENGTH(formdata)));
   UTL_HTTP.write_text(req, formdata);
   resp := UTL_HTTP.get_response(req);
 IF resp.status_code = UTL_HTTP.http_found
   THEN
      UTL_HTTP.get_header_by_name(resp, 'Location', newlocation);
      req := UTL_HTTP.begin_request(newlocation);
      resp := UTL_HTTP.get_response(req);
   END IF;
 
   ...process the resulting page here, as before...
 
   UTL_HTTP.end_response(resp);
END;

Figure 21-2 gives an idea of the interaction between this code and the web server.

Figure 21-2. Getting the Sebastopol weather from NOAA involves following a “302 found” redirection

I don’t know how common a problem that is, and my “fix” is not really a general-purpose solution for all redirects, but it gives you an idea of the kinds of quirks you may run into when writing this sort of code.

For better or for worse, session-level cookie support is enabled by default in recent versions of UTL_HTTP. Oracle has set a default of 20 cookies allowed per site and a total of 300 per session. To check whether this is true for your version of UTL_HTTP, use the following:

DECLARE
   enabled BOOLEAN;
   max_total PLS_INTEGER;
   max_per_site PLS_INTEGER;
BEGIN
   UTL_HTTP.get_cookie_support(enabled, max_total, max_per_site);
   IF enabled
   THEN
      DBMS_OUTPUT.PUT('Allowing ' || max_per_site || ' per site'),
      DBMS_OUTPUT.PUT_LINE(' for total of ' || max_total || ' cookies. '),
   ELSE
      DBMS_OUTPUT.PUT_LINE('Cookie support currently disabled.'),
   END IF;
END;

Cookie support is transparent; Oracle automatically stores cookies in memory and sends them back to the server when requested.

Cookies disappear when the session ends. If you’d like to make cookies persistent, you can save them into Oracle tables and then restore them when you start a new session. To do this, have a look at the sample code that Oracle provides in the UTL_HTTP section of the Packages and Types manual.

To completely disable cookie support for all your UTL_HTTP requests for the remainder of your session, use this code:

UTL_HTTP.set_cookie_support 
(FALSE);

To disable cookies for a particular request:

UTL_HTTP.set_cookie_support(req, FALSE);

To change the number of cookies from Oracle’s default values:

UTL_HTTP.set_cookie_support(TRUE,
   max_cookies =>n,
   max_cookies_per_site => m);

Oracle does not provide out-of-the-box support for retrieving data from FTP sites via PL/SQL. However, if you need to send or receive files via FTP, there is at least one open source PL/SQL solution available that you can use, written by an industrious fellow by the name of Barry Chase. His code, written with UTL_TCP and UTL_FILE (no Java required), supports FTP put, get, rename, and delete operations, and includes both binary and ASCII transport modes. Retrieved files get saved in the server’s filesystem, but it would be easy enough to add the ability to read a file into a LOB after it’s retrieved. You can find a link to Barry’s code by visiting http://plnet.org.

A number of corporations force all web traffic out through a proxy server . Oracle includes support for this configuration in UTL_HTTP. For example, if your proxy is running on port 8888 at 10.2.1.250:

DECLARE
   req UTL_HTTP.req;
   resp UTL_HTTP.resp;
BEGIN
   UTL_HTTP.set_proxy(proxy => '10.2.1.250:8888',
       no_proxy_domains => 'mycompany.com, hr.mycompany.com'),
 
   req := UTL_HTTP.begin_request('http://some-remote-site.com'),
 
   /* If your proxy requires authentication, use this: */
   UTL_HTTP.set_authentication(r => req,
      username => 'username',
      password => 'password',
      for_proxy => TRUE);
 
   resp := UTL_HTTP.get_response(req);
 
   ...etc.

I happened to test this code on a proxy server that uses Microsoft NTLM-based authentication. After an embarrassing amount of trial and error, I discovered that I had to prefix my username with the Microsoft server “domain name” plus a backslash. That is, if I normally log in to the “mis” domain as user bill with password swordfish, I must specify:

     username => 'misill', password => 'swordfish'

The DBMS_PIPE built-in package was originally designed as an efficient means of sending small bits of data between separate Oracle sessions. With the introduction of autonomous transactions, database pipes are no longer needed if they are simply being used to isolate transactions from each other. Database pipes can also be used to manually parallelize operations.

Database queuing is a way to pass messages asynchronously among Oracle sessions. There are many variations on queuing: single versus multiple producers, single versus multiple consumers, limited-life messages, priorities, and more. The latest incarnation of Oracle’s queuing features is covered in the Oracle manual called Oracle Streams Advanced Queuing User’s Guide and Reference.

The DBMS_ALERT package allows synchronous notification to multiple sessions that various database events have occurred. My impression is that this feature is rarely used today; Oracle provides other extensive features that fill a similar need but with more features.

You can read more about pipes and alerts in the chapter “Intersession Communication” in O’Reilly’s Oracle Built-in Packages. For your convenience, we have posted that chapter on our book’s web site.

As interesting a subject as low-level network programming may be to some strange folks (including yours truly), it’s just not a widely needed feature. In addition to the UTL_TCP built-in package, Oracle also supports invocation of the networking features in Java stored procedures, which you can invoke from PL/SQL.

Even if you haven’t licensed the Oracle Application Server 10g product, you still have access to an HTTP server built in to the Oracle RDBMS. Configuration of the built-in server varies according to Oracle version, but the PL/SQL programming side of it, including the OWA_UTIL, HTP, and HTF packages, has remained relatively stable.

These packages let you generate database-driven web pages directly from PL/SQL. This is a fairly extensive topic, particularly if you want to generate and process HTML forms in your web page—not to mention the fact that HTTP is a stateless protocol, so you don’t really get to set and use package-level variables from one call to the next. O’Reilly’s Learning Oracle PL/SQL provides an introduction to PL/SQL that makes heavy use of the built-in web server and provides a number of code samples. The PL/SQL coding techniques are also applicable if you happen to be using Oracle’s separate, full-blown application server product; for more information about this product, see Oracle Application Server 10g Essentials by Rick Greenwald, Robert Stackowiak, and Donald Bales (O’Reilly).