Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 12
PL/SQL Input/Output

This chapter describes how to use Oracle-supplied packages that allow PL/SQL to communicate with external processes, sessions, and files.

The packages are:

- `DBMS_PIPE`, to send and receive information between sessions, asynchronously.
- `DBMS_OUTPUT`, to send messages from a PL/SQL program to other PL/SQL programs in the same session, or to a display window running SQL*Plus.
- `UTL_FILE`, which allows a PL/SQL program to read information from a disk file, and write information to a file.

## Database Pipes

The `DBMS_PIPE` package allows two or more sessions in the same instance to communicate. Oracle pipes are similar in concept to the pipes used in UNIX, but Oracle pipes are not implemented using the operating system pipe mechanisms. Information sent through Oracle pipes is buffered in the system global area (SGA). All information in pipes is lost when the instance is shut down.

Depending upon your security requirements, you may choose to use either a public pipe or a private pipe.

WARNING: Pipes are independent of transactions. Be careful using pipes when transaction control can be affected.

.

### Summary

Table 12-1 summarizes the procedures you can call in the `DBMS_PIPE` package.

##### Table 12-1 DBMS_PIPE Package Functions and Procedures

Function/Procedure | Description | Refer to
--- | --- | ---
`CREATE_PIPE` | Explicitly create a pipe (necessary for private pipes). | page 12-4
`PACK_MESSAGE` | Build message in local buffer. | page 12-6
`SEND_MESSAGE` | Send message on named pipe. Implicitly create a public pipe if named pipe does not exist. | page 12-7
`RECEIVE_MESSAGE` | Copy message from named pipe into local buffer. | page 12-9
`NEXT_ITEM_TYPE` | Return datatype of next item in buffer. | page 12-11
`UNPACK_MESSAGE` | Access next item in buffer. | page 12-11
`REMOVE_PIPE` | Remove the named pipe. | page 12-12
`PURGE` | Purge contents of named pipe. | page 12-13
`RESET_BUFFER` | Purge contents of local buffer. | page 12-13
`UNIQUE_` `SESSION_NAME` | Return unique session name. | page 12-13

### Creating the DBMS_PIPE Package

To create the `DBMS_PIPE` package, submit the `DBMSPIPE`.`SQL` and `PRVTPIPE`.`PLB` scripts when connected as the user `SYS`. These scripts are run automatically by the `CATPROC`.`SQL` script. See "Privileges Required to Execute a Procedure" on page 10-40 for information on granting the necessary privileges to users who will be executing this package.

### Public Pipes

You can create a public pipe either implicitly or explicitly. For implicit public pipes, the pipe is automatically created when referenced for the first time, and it disappears when it no longer contains data. Because the pipe descriptor is stored in the SGA, there is some space usage overhead until the empty pipe is aged out of the cache.

You can create an explicit public pipe by calling the `CREATE_PIPE` function with the `PRIVATE` flag set to `FALSE`. You must deallocate explicitly-created pipes by calling the `REMOVE_PIPE` function.

The domain of a public pipe is the schema in which it was created, either explicitly or implicitly.

#### Writing and Reading

Each public pipe works asynchronously. Any number of schema users can write to a public pipe, as long as they have `EXECUTE` permission on the `DBMS_PIPE` package, and know the name of the public pipe.

Any schema user with the appropriate privileges and knowledge can read information from a public pipe. However, once buffered information is read by one user, it is emptied from the buffer, and is not available for other readers of the same pipe.

The sending session builds a message using one or more calls to the `PACK_MESSAGE` procedure. This procedure adds the message to the session's local message buffer. The information in this buffer is sent by calling the `SEND_MESSAGE` procedure, designating the pipe name to be used to send the message. When `SEND_MESSAGE` is called, all messages that have been stacked in the local buffer are sent.

A process that wants to receive a message calls the `RECEIVE_MESSAGE` procedure, designating the pipe name from which to receive the message. The process then calls the `UNPACK_MESSAGE` procedure to access each of the items in the message.

### Private Pipes

You must explicitly create a private pipe by calling the `CREATE_PIPE` function. Once created, the private pipe persists in shared memory until you explicitly deallocate it by calling the `REMOVE_PIPE` function. A private pipe is also deallocated when the database instance is shut down.

You cannot create a private pipe if an implicit pipe exists in memory and has the same name as the private pipe you are trying to create. In this case `CREATE_PIPE` returns an error.

Access to a private pipe is restricted to the following:

- sessions running under the same userid as the creator of the pipe.
- stored subprograms executing in the same userid privilege domain as the pipe creator.
- users connected as `SYSDBA` or `INTERNAL.`

An attempt by any other user to send or receive messages on the pipe, or to remove the pipe, results in an immediate error. Any attempt by another user to create a pipe with the same name also causes an error.

As with public pipes, you must first build your message using calls to `PACK_MESSAGE` before calling `SEND_MESSAGE`. Similarly you must call `RECEIVE_MESSAGE` to retrieve the message before accessing the items in the message by calling `UNPACK_MESSAGE`.

### Errors

`DBMS_PIPE` package routines can return the following errors:

    ORA-23321: Pipename may not be null
    ORA-23322: Insufficient privilege to access pipe

`ORA-23321` can be returned by `CREATE_PIPE`, or any subprogram that takes a pipe name as a parameter. `ORA-23322` can be returned by any subprogram that references a private pipe in its parameter list.

### CREATE_PIPE

Call `CREATE_PIPE` to explicitly create a public or private pipe. If the `PRIVATE` flag is `TRUE`, the pipe creator is assigned as the owner of the private pipe. Explicitly created pipes can only be removed by calling `REMOVE_PIPE`, or by shutting down the instance.

WARNING: Do not use a pipe name beginning with ORA$; these names are reserved for use by Oracle Corporation.

#### Syntax

The parameters for the `CREATE_PIPE` function are shown in Table 12-2 and the possible return values and their meanings are described in Table 12-3. The syntax for this function is

    DBMS_PIPE.CREATE_PIPE(pipename    IN VARCHAR2,
                          maxpipesize IN INTEGER DEFAULT 8192,
                          private     IN BOOLEAN DEFAULT TRUE)
    RETURN INTEGER;

`

##### Table 12-2 DBMS_PIPE.CREATE_PIPE Function Parameters

Parameter | Description
--- | ---
    pipename
| Specify a name for the pipe that you are creating. You will need to use this name when you call `SEND_MESSAGE` and `RECEIVE_MESSAGE`. This name must be unique across the instance.
    maxpipesize
| Specify the maximum size allowed for the pipe, in bytes. The total size of all of the messages on the pipe cannot exceed this amount. The message is blocked if it exceeds this maximum. The default `MAXPIPESIZE` is 8192 bytes. The `MAXPIPESIZE` for a pipe becomes a part of the characteristics of the pipe and persists for the life of the pipe. Callers of `SEND_MESSAGE` with larger values cause the `MAXPIPESIZE` to be increased. Callers with a smaller value simply use the existing, larger value.
    private
| Use the default, `TRUE`, to create a private pipe. Public pipes can be implicitly created when you call `SEND_MESSAGE`.

` `

##### Table 12-3 DBMS_PIPE.CREATE_PIPE Function Return Values

Return Value or Error | Description
--- | ---
    0
| Indicates the pipe was successfully created. If the pipe already exists and the user attempting to create it is authorized to use it, Oracle returns 0, indicating success, and any data already in the pipe remains. If a user connected as `SYSDBA`/`SYSOPER` re-creates a pipe, Oracle returns status 0, but the ownership of the pipe remains unchanged.
    ORA-23322
| Indicates a failure due to naming conflict. If a pipe with the same name exists and was created by a different user, Oracle signals error `ORA-2332`2, indicating the naming conflict.

`

### PACK_MESSAGE Procedures

To send a message, first make one or more calls to `PACK_MESSAGE` to build your message in the local message buffer. Then call `SEND_MESSAGE` to send the message in the local buffer on the named pipe.

The `PACK_MESSAGE` procedure is overloaded to accept items of type `VARCHAR2,` `NUMBER`, or `DATE`. In addition to the data bytes, each item in the buffer requires one byte to indicate its type, and two bytes to store its length. One additional byte is needed to terminate the message. If the message buffer exceeds 4096 bytes, Oracle raises exception `ORA-6558`.

When you call `SEND_MESSAGE` to send this message, you must indicate the name of the pipe on which you want to send the message. If this pipe already exists, you must have sufficient privileges to access this pipe. If the pipe does not already exist, it is created automatically.

WARNING: Do not use a pipe name beginning with `ORA$`; these names are reserved for use by Oracle Corporation.

#### Syntax

The syntax for the `PACK_MESSAGE` procedures is shown below. Note that the `PACK_MESSAGE` procedure itself is overloaded to accept items of type `VARCHAR2`, `NCHAR`, `NUMBER`, or `DATE`. There are two additional procedures to pack `RAW` and `ROWID` items.

    DBMS_PIPE.PACK_MESSAGE       (item  IN  VARCHAR2);
    DBMS_PIPE.PACK_MESSAGE       (item  IN  NCHAR);
    DBMS_PIPE.PACK_MESSAGE       (item  IN  NUMBER);
    DBMS_PIPE.PACK_MESSAGE       (item  IN  DATE);
    DBMS_PIPE.PACK_MESSAGE_RAW   (item  IN  RAW);
    DBMS_PIPE.PACK_MESSAGE_ROWID (item  IN  ROWID);

### SEND_MESSAGE

The parameters for the `SEND_MESSAGE` function are shown in Table 12-4 and the possible return values and their meanings are described in Table 12-5. The syntax for this function is shown below.

    DBMS_PIPE.SEND_MESSAGE(pipename     IN VARCHAR2,
                           timeout      IN INTEGER DEFAULT MAXWAIT
                           maxpipesize  IN INTEGER DEFAULT 8192)
    RETURN INTEGER;

`

##### Table 12-4 DBMS_PIPE.SEND_MESSAGE Function Parameters

Parameter | Description
--- | ---
    pipename
| Specify the name of the pipe on which you want to place the message. If you are using an explicit pipe, this is the name that you specified when you called `CREATE_PIPE`.
    timeout
| Specify the timeout period in seconds. This is the time to wait while attempting to place a message on the pipe; the return values are explained below. The default value is the constant `MAXWAIT`, which is defined as 86400000 (1000 days).
    maxpipesize
| Specify the maximum size allowed for the pipe, in bytes. The total size of all of the messages on the pipe cannot exceed this amount. The message is blocked if it exceeds this maximum. The default `MAXPIPESIZE` is 8192 bytes. The `MAXPIPESIZE` for a pipe becomes a part of the characteristics of the pipe and persists for the life of the pipe. Callers of `SEND_MESSAGE` with larger values cause the `MAXPIPESIZE` to be increased. Callers with a smaller value simply use the existing, larger value. Specifying `MAXPIPESIZE` as part of the `SEND_MESSAGE` procedure eliminates the need for a separate call to open the pipe. If you created the pipe explicitly, you can use the optional `MAXPIPESIZE` parameter to override the creation pipe size specification.

` `

##### Table 12-5 DBMS_PIPE.SEND_MESSAGE Function Return Values

Return Value or Error | Description
--- | ---
    0
| Indicates the pipe was successfully created. If the pipe already exists and the user attempting to create it is authorized to use it, Oracle returns 0, indicating success, and any data already in the pipe remains. If a user connected as `SYSDBA`/`SYSOPER` re-creates a pipe, Oracle returns status 0, but the ownership of the pipe remains unchanged.
    1
| Indicates the pipe has timed out. This procedure can timeout either because it cannot get a lock on the pipe, or because the pipe remains too full to be used. If the pipe was implicitly created and is empty, it is removed.
    3
| Indicates an interrupt has occurred. If the pipe was implicitly created and is empty, it is removed.
    ORA-23322
| Indicates insufficient privileges to write to the pipe. If a pipe with the same name exists and was created by a different user, Oracle signals error `ORA-23322`, indicating the naming conflict.

`

### RECEIVE_MESSAGE

To receive a message from a pipe, first call `RECEIVE_MESSAGE` to copy the message into the local message buffer. When you receive a message, it is removed from the pipe; that is, a message can only be received once. For implicitly created pipes, the pipe is removed after the last record is removed from the pipe.

If the pipe that you specify when you call `RECEIVE_MESSAGE` does not already exist, Oracle implicitly creates the pipe and then waits to receive the message. If the message does not arrive within a designated timeout interval, the call returns and the pipe is removed.

After receiving the message, you must make one or more calls to `UNPACK_MESSAGE` to access the individual items in the message. The `UNPACK_MESSAGE` procedure is overloaded to unpack items of type `DATE`, `NUMBER`, `VARCHAR`2, and there are two additional procedures to unpack `RAW` and `ROWID` items. If you do not know the type of data that you are attempting to unpack, you can call `NEXT_ITEM_TYPE` to determine the type of the next item in the buffer.

#### Syntax

The parameters for the `RECEIVE_MESSAGE` function are shown in Table 12-6 and the possible return values and their meanings are described in Table 12-7. The syntax for this function is shown below.

    DBMS_PIPE.RECEIVE_MESSAGE(pipename     IN VARCHAR2,
                              timeout      IN INTEGER
                                           DEFAULT maxwait)
    RETURN INTEGER;

`

##### Table 12-6 DBMS_PIPE.RECEIVE_MESSAGE Function Parameters

Parameter | Description
--- | ---
    pipename
| Specify the name of the pipe on which you want to receive a message. Names beginning with `ORA$` are reserved for use by Oracle.
    timeout
| Specify the timeout period in seconds. This is the time to wait to receive a message on the pipe. The default value is the constant `MAXWAIT`, which is defined as 86400000 (1000 days). A timeout of 0 allows you to read without blocking.

` `

##### Table 12-7 DBMS_PIPE.RECEIVE_MESSAGE Function Return Values

Return Value or Error | Description
--- | ---
    0
| Indicates the message was received successfully.
    1
| Indicates the pipe has timed out. If the pipe was implicitly created and is empty, it is removed.
    2
| Indicates the record in the pipe is too large for the buffer. (This should not happen.)
    3
| Indicates an interrupt has occurred.
    ORA-23322
| Indicates the user has insufficient privileges to read from the pipe.

`

### NEXT_ITEM_TYPE

After you have called `RECEIVE_MESSAGE` to place pipe information in a local buffer, you can call `NEXT_ITEM_TYPE` to determine the datatype of the next item in the local message buffer. When `NEXT_ITEM_TYPE` returns 0, the local buffer is empty.

#### Syntax

The possible return values and their meanings for the `NEXT_ITEM_TYPE` function are described in Table 12-8. The syntax for this function is shown below.

    DBMS_PIPE.NEXT_ITEM_TYPE RETURN INTEGER;

`

##### Table 12-8 DBMS_PIPE.NEXT_ITEM_TYPE Function Return Values

Return Value | Description
--- | ---
    0
| no more items
    6
| `NUMBER`
    9
| `VARCHAR2`
    12
| `DATE`

`

### UNPACK_MESSAGE Procedures

After you have called `RECEIVE_MESSAGE` to place pipe information in a local buffer, you call `UNPACK_MESSAGE` to retrieve items from the buffer.

#### Syntax

The syntax for the `UNPACK_MESSAGE` procedures is shown below. Note that the `UNPACK_MESSAGE` procedure is overloaded to return items of type `VARCHAR2`, `NCHAR`, `NUMBER`, or `DATE`. There are two additional procedures to unpack `RAW` and `ROWID` items.

    DBMS_PIPE.UNPACK_MESSAGE       (item  OUT VARCHAR2);
    DBMS_PIPE.UNPACK_MESSAGE       (item  OUT NCHAR);
    DBMS_PIPE.UNPACK_MESSAGE       (item  OUT NUMBER);
    DBMS_PIPE.UNPACK_MESSAGE       (item  OUT DATE);
    DBMS_PIPE.UNPACK_MESSAGE_RAW   (item  OUT RAW);
    DBMS_PIPE.UNPACK_MESSAGE_ROWID (item  OUT ROWID);

If the message buffer contains no more items, or if the item received is not of the same type as that requested, the `ORA-2000` exception is raised.

### REMOVE_PIPE

Pipes created implicitly by `SEND_MESSAGE` are automatically removed when empty.

Pipes created explicitly by `CREATE_PIPE` are removed only by calling `REMOVE_PIPE` or when the instance is shut down. All unconsumed records in the pipe are removed before the pipe is deleted. This is similar to calling `PURGE` on an implicitly created pipe.

#### Syntax

The `REMOVE_PIPE` function accepts only one parameter-the name of the pipe that you want to remove. The possible return values and their meanings are described in Table 12-9. The syntax for this function is

    DBMS_PIPE.REMOVE_PIPE(pipename  IN  VARCHAR2)
    RETURN INTEGER;

`

##### Table 12-9 DBMS_PIPE.REMOVE_PIPE Function Return Values

Return Value or Error | Description
--- | ---
    0
| Indicates the pipe was successfully removed. If the pipe does not exist, or if the pipe already exists and the user attempting to remove it is authorized to do so, Oracle returns 0, indicating success, and any data remaining in the pipe is removed.
    ORA-23322
| Indicates a failure due to insufficient privileges. If the pipe exists, but the user is not authorized to access the pipe, Oracle signals error `ORA-23322`, indicating insufficient privileges.

`

### Managing Pipes

The `DBMS_PIPE` package contains additional procedures and functions that you might find useful.

### Purging the Contents of a Pipe

Call `PURGE` to empty the contents of a pipe. An empty implicitly created pipe is aged out of the shared global area according to the least-recently-used algorithm. Thus, calling `PURGE` lets you free the memory associated with an implicitly created pipe.

Because `PURGE` calls `RECEIVE_MESSAGE`, the local buffer might be overwritten with messages as they are purged from the pipe. Also, you can receive an `ORA`-`23322`, insufficient privileges, error if you attempt to purge a pipe to which you have insufficient access rights.

    DBMS_PIPE.PURGE(pipename  IN  VARCHAR2);

### Resetting the Message Buffer

Call `RESET_BUFFER` to reset the `PACK_MESSAGE` and `UNPACK_MESSAGE` positioning indicators to 0. Because all pipes share a single buffer, you may find it useful to reset the buffer before using a new pipe. This ensures that the first time you attempt to send a message to your pipe, you do not inadvertently send an expired message remaining in the buffer.

#### Syntax

The syntax for the `RESET_BUFFER` procedure is shown below.

    DBMS_PIPE.RESET_BUFFER;

### Getting a Unique Session Name

Call `UNIQUE_SESSION_NAME` to receive a name that is unique among all of the sessions that are currently connected to a database. Multiple calls to this function from the same session always return the same value. The return value can be up to 30 bytes. You might find it useful to use this function to supply the `PIPENAME` parameter for your `SEND_MESSAGE` and `RECEIVE_MESSAGE` calls.

    DBMS_PIPE.UNIQUE_SESSION_NAME RETURN VARCHAR2;

### Example 1: Debugging

The following example shows a procedure a PL/SQL program can call to place debugging information in a pipe:

    CREATE OR REPLACE PROCEDURE debug (msg VARCHAR2) AS
        status  NUMBER;
    BEGIN
      dbms_pipe.pack_message(LENGTH(msg));
      dbms_pipe.pack_message(msg);
      status := dbms_pipe.send_message('plsql_debug');
      IF status != 0 THEN
        raise_application_error(-20099, 'Debug error');
      END IF;
    END debug;

This example shows the Pro*C code that receives messages from the `PLSQL_DEBUG` pipe in the PL/SQL example above, and displays the messages. If the Pro*C session is run in a separate window, it can be used to display any messages that are sent to the debug procedure from a PL/SQL program executing in a separate session.

    #include <stdio.h>
    #include <string.h>

    EXEC SQL BEGIN DECLARE SECTION;
       VARCHAR username[20];
       int     status;
       int     msg_length;
       char    retval[2000];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL INCLUDE SQLCA;

    void sql_error();

    main()
    {
    /* prepare username */
       strcpy(username.arr, "SCOTT/TIGER");
       username.len = strlen(username.arr);

       EXEC SQL WHENEVER SQLERROR DO sql_error();
       EXEC SQL CONNECT :username;

       printf("connected\n");

    /* start an endless loop to look for and print
       messages on the pipe */
       for (;;)
       {
          EXEC SQL EXECUTE
             DECLARE
                len INTEGER;
                typ INTEGER;
                sta INTEGER;
                chr VARCHAR2(2000);
             BEGIN
                chr := '';
                sta := dbms_pipe.receive_message('plsql_debug');
                IF sta = 0 THEN
                   dbms_pipe.unpack_message(len);
                   dbms_pipe.unpack_message(chr);
                END IF;
                :status := sta;
                :retval := chr;
                IF len IS NOT NULL THEN
                   :msg_length := len;
                ELSE
                   :msg_length := 2000;
                END IF;
             END;
          END-EXEC;
          if (status == 0)
             printf("\n%.*s\n", msg_length, retval);
          else
             printf("abnormal status, value is %d\n", status);
       }
    }

    void sql_error()
    {
       char msg[1024];
       int rlen, len;
       len = sizeof(msg);
       sqlglm(msg, &len, &rlen);
       printf("ORACLE ERROR\n");
       printf("%.*s\n", rlen, msg);
       exit(1);
    }

### Example 2: Execute System Commands

The following example shows PL/SQL and Pro*C code that can let a PL/SQL stored procedure (or anonymous block) call PL/SQL procedures to send commands over a pipe to a Pro*C program that is listening for them.

The Pro*C program just sleeps, waiting for a message to arrive on the named pipe. When a message arrives, the C program processes it, carrying out the required action, such as executing a UNIX command through the system() call, or executing a SQL command using embedded SQL.

`DAEMON`.`SQL` is the source code for the PL/SQL package. This package contains procedures that use the `DBMS_PIPE` package to send and receive message to and from the Pro*C daemon. Note that full handshaking is used. The daemon will always send a message back to the package (except in the case of the '`STOP`' command). This is valuable, since it allows the PL/SQL procedures to be sure that the Pro*C daemon is running.

You can call the `DAEMON` packaged procedures from an anonymous PL/SQL block using SQL*Plus or Enterprise Manager. For example:

    SVRMGR> variable rv number
    SVRMGR> execute :rv := DAEMON.EXECUTE_SYSTEM('ls -la');

would, on a UNIX system, cause the Pro*C daemon to execute the command system("ls -la").

Remember that the daemon needs to be running first. So you might want to run it in the background, or in another window beside the SQL*Plus or Enterprise Manager session from which you call it.

The `DAEMON`.`SQL` also uses the `DBMS_OUTPUT` package to display the results. For this example to work, you must have execute privileges on this package.

See Also: "Output from Stored Procedures and Triggers" on page 12-22.

#### DAEMON.SQL

This is the code for the PL/SQL `DAEMON` package:

    CREATE OR REPLACE PACKAGE daemon AS
      FUNCTION execute_sql(command VARCHAR2,
                           timeout NUMBER DEFAULT 10)
        RETURN NUMBER;

      FUNCTION execute_system(command VARCHAR2,
                              timeout NUMBER DEFAULT 10)
        RETURN NUMBER;

      PROCEDURE stop(timeout NUMBER DEFAULT 10);
    END daemon;
    /
    CREATE OR REPLACE PACKAGE BODY daemon AS

      FUNCTION execute_system(command VARCHAR2,
                              timeout NUMBER DEFAULT 10)
      RETURN NUMBER IS

        status       NUMBER;
        result       VARCHAR2(20);
        command_code NUMBER;
        pipe_name    VARCHAR2(30);
      BEGIN
        pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;

        DBMS_PIPE.PACK_MESSAGE('SYSTEM');
        DBMS_PIPE.PACK_MESSAGE(pipe_name);
        DBMS_PIPE.PACK_MESSAGE(command);
        status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
        IF status <> 0 THEN
          RAISE_APPLICATION_ERROR(-20010,
            'Execute_system: Error while sending.  Status = ' ||
             status);
        END IF;

        status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
        IF status <> 0 THEN
          RAISE_APPLICATION_ERROR(-20011,
            'Execute_system: Error while receiving.
             Status = ' || status);
        END IF;

        DBMS_PIPE.UNPACK_MESSAGE(result);
        IF result <> 'done' THEN
          RAISE_APPLICATION_ERROR(-20012,
            'Execute_system: Done not received.');
        END IF;

        DBMS_PIPE.UNPACK_MESSAGE(command_code);
        DBMS_OUTPUT.PUT_LINE('System command executed.  result = ' ||
                             command_code);
        RETURN command_code;
      END execute_system;

      FUNCTION execute_sql(command VARCHAR2,
                           timeout NUMBER DEFAULT 10)
      RETURN NUMBER IS

        status       NUMBER;
        result       VARCHAR2(20);
        command_code NUMBER;
        pipe_name    VARCHAR2(30);

      BEGIN
        pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;

        DBMS_PIPE.PACK_MESSAGE('SQL');
        DBMS_PIPE.PACK_MESSAGE(pipe_name);
        DBMS_PIPE.PACK_MESSAGE(command);
        status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
        IF status <> 0 THEN
          RAISE_APPLICATION_ERROR(-20020,
            'Execute_sql: Error while sending.  Status = ' || status);
        END IF;

        status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);

        IF status <> 0 THEN
          RAISE_APPLICATION_ERROR(-20021,
            'execute_sql: Error while receiving.
             Status = ' || status);
        END IF;

        DBMS_PIPE.UNPACK_MESSAGE(result);
        IF result <> 'done' THEN
          RAISE_APPLICATION_ERROR(-20022,
            'execute_sql: done not received.');
        END IF;

        DBMS_PIPE.UNPACK_MESSAGE(command_code);
        DBMS_OUTPUT.PUT_LINE
            ('SQL command executed.  sqlcode = ' || command_code);
        RETURN command_code;
      END execute_sql;

      PROCEDURE stop(timeout NUMBER DEFAULT 10) IS
        status NUMBER;
      BEGIN
        DBMS_PIPE.PACK_MESSAGE('STOP');
        status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
        IF status <> 0 THEN
          RAISE_APPLICATION_ERROR(-20030,
            'stop: error while sending.  status = ' || status);
        END IF;
      END stop;
    END daemon;

#### daemon.pc

This is the code for the Pro*C daemon. You must precompile this using the Pro*C Precompiler, Version 1.5.x or later. You must also specify the `USERID` and `SQLCHECK` options, as the example contains embedded PL/SQL code. For example:

    proc iname=daemon userid=scott/tiger sqlcheck=semantics

Then C-compile and link in the normal way.

    #include <stdio.h>
    #include <string.h>

    EXEC SQL INCLUDE SQLCA;

    EXEC SQL BEGIN DECLARE SECTION;
      char *uid = "scott/tiger";
      int status;
      VARCHAR command[20];
      VARCHAR value[2000];
      VARCHAR return_name[30];
    EXEC SQL END DECLARE SECTION;

    void
    connect_error()
    {
      char msg_buffer[512];
      int msg_length;
      int buffer_size = 512;

      EXEC SQL WHENEVER SQLERROR CONTINUE;
      sqlglm(msg_buffer, &buffer_size, &msg_length);
      printf("Daemon error while connecting:\n");
      printf("%.*s\n", msg_length, msg_buffer);
      printf("Daemon quitting.\n");
      exit(1);
    }

    void
    sql_error()
    {
      char msg_buffer[512];
      int msg_length;
      int buffer_size = 512;

      EXEC SQL WHENEVER SQLERROR CONTINUE;
      sqlglm(msg_buffer, &buffer_size, &msg_length);
      printf("Daemon error while executing:\n");
      printf("%.*s\n", msg_length, msg_buffer);
      printf("Daemon continuing.\n");
    }
    main()
    {
      EXEC SQL WHENEVER SQLERROR DO connect_error();
      EXEC SQL CONNECT :uid;
      printf("Daemon connected.\n");

      EXEC SQL WHENEVER SQLERROR DO sql_error();
      printf("Daemon waiting...\n");
      while (1) {
        EXEC SQL EXECUTE
          BEGIN
            :status := DBMS_PIPE.RECEIVE_MESSAGE('daemon');
            IF :status = 0 THEN
              DBMS_PIPE.UNPACK_MESSAGE(:command);
            END IF;
          END;
        END-EXEC;
        if (status == 0)
        {
          command.arr[command.len] = '\0';
          if (!strcmp((char *) command.arr, "STOP"))
          {
            printf("Daemon exiting.\n");
            break;
          }

          else if (!strcmp((char *) command.arr, "SYSTEM"))
          {
            EXEC SQL EXECUTE
              BEGIN
                DBMS_PIPE.UNPACK_MESSAGE(:return_name);
                DBMS_PIPE.UNPACK_MESSAGE(:value);
              END;
            END-EXEC;
            value.arr[value.len] = '\0';
            printf("Will execute system command '%s'\n", value.arr);

            status = system(value.arr);
            EXEC SQL EXECUTE
              BEGIN
                DBMS_PIPE.PACK_MESSAGE('done');
                DBMS_PIPE.PACK_MESSAGE(:status);
                :status := DBMS_PIPE.SEND_MESSAGE(:return_name);
              END;
            END-EXEC;

            if (status)
            {
              printf
               ("Daemon error while responding to system command.");
              printf("  status: %d\n", status);
            }
          }
          else if (!strcmp((char *) command.arr, "SQL")) {
            EXEC SQL EXECUTE
              BEGIN
                DBMS_PIPE.UNPACK_MESSAGE(:return_name);
                DBMS_PIPE.UNPACK_MESSAGE(:value);
              END;
            END-EXEC;
            value.arr[value.len] = '\0';
            printf("Will execute sql command '%s'\n", value.arr);

            EXEC SQL WHENEVER SQLERROR CONTINUE;
            EXEC SQL EXECUTE IMMEDIATE :value;
            status = sqlca.sqlcode;

            EXEC SQL WHENEVER SQLERROR DO sql_error();
            EXEC SQL EXECUTE
              BEGIN
                DBMS_PIPE.PACK_MESSAGE('done');
                DBMS_PIPE.PACK_MESSAGE(:status);
                :status := DBMS_PIPE.SEND_MESSAGE(:return_name);
              END;
            END-EXEC;

            if (status)
            {
              printf("Daemon error while responding to sql command.");
              printf("  status: %d\n", status);
            }
          }
          else
          {
            printf
              ("Daemon error: invalid command '%s' received.\n",
                command.arr);
          }
        }
        else
        {
          printf("Daemon error while waiting for signal.");
          printf("  status = %d\n", status);
        }
      }
      EXEC SQL COMMIT WORK RELEASE;
      exit(0);
    }

## Output from Stored Procedures and Triggers

Oracle provides a public package, `DBMS_OUTPUT`, which you can use to send messages from stored procedures, packages, and triggers. The `PUT` and `PUT_LINE` procedures in this package allow you to place information in a buffer that can be read by another trigger, procedure, or package.

Enterprise Manager or SQL*Plus can also display messages buffered by the `DBMS_OUTPUT` procedures. To do this, you must issue the command `SET` `SERVEROUTPUT` `ON` in Enterprise Manager or SQL*Plus.

In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the `GET_LINE` procedure. If you do not call `GET_LINE`, or do not display the messages on your screen in SQL*Plus or Enterprise Manager, the buffered messages are ignored. The `DBMS_OUTPUT` package is especially useful for displaying PL/SQL debugging information.

Note: Messages sent using the `DBMS_OUTPUT` are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.

### Summary

Table 12-10 shows the procedures that are callable from the `DBMS_OUTPUT` package:

##### Table 12-10 DBMS_OUTPUT Package Functions and Procedures

Function/Procedure | Description | Refer to
--- | --- | ---
    ENABLE
| enable message output | page 12-24
    DISABLE
| disable message output | page 12-24
    PUT_LINE
| place a line in the buffer | page 12-25
    PUT
| place partial line in buffer | page 12-25
    NEW_LINE
| terminate a line created with PUT | page 12-40
    GET_LINE
| retrieve one line of information from buffer | page 12-26
    GET_LINES
| retrieve array of lines from buffer | page 12-26

### Creating the DBMS_OUTPUT Package

To create the `DBMS_OUTPUT` package, submit the `DBMSOTPT`.`SQL` and `PRVTOTPT`.`PLB` scripts when connected as the user `SYS`. These scripts are run automatically by the `CATPROC`.`SQL` script.

See Also: "Privileges Required to Execute a Procedure" on page 10-40 for information on the necessary privileges for users who will be executing this package.

.

### Errors

The `DBMS_OUTPUT` package routines raise the application error -20000, and the output procedures can return the following errors:

    ORU-10027: buffer overflow
    ORU-10028: line length overflow

### ENABLE Procedure

This procedure enables calls to `PUT`, `PUT_LINE`, `NEW_LINE`, `GET_LINE`, and `GET_LINES`. Calls to these procedures are ignored if the `DBMS_OUTPUT` package is not enabled. It is not necessary to call this procedure when you use the `SERVEROUTPUT` option of Enterprise Manager or SQL*Plus.

You must specify the amount of information, in bytes, to buffer. Items are stored in the `DBMS_OUTPUT` package. If the buffer size is exceeded, you receive the following error message:

    ORA-20000, ORU-10027: buffer overflow, limit of <buffer_limit> bytes.

Multiple calls to `ENABLE` are allowed. If there are multiple calls to `ENABLE`, `BUFFER_SIZE` is the largest of the values specified. The maximum size is 1000000 and the minimum is 2000.

#### Syntax

The syntax for the `ENABLE` procedure is

    DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 2000);

### DISABLE Procedure

The DISABLE procedure disables calls to `PUT`, `PUT_LINE`, `NEW_LINE`, `GET_LINE`, and `GET_LINES`, and purges the buffer of any remaining information. As with `ENABLE`, you do not need to call this procedure if you are using the `SERVEROUTPUT` option of Enterprise Manager or SQL*Plus.

#### Syntax

The syntax for the `DISABLE` procedure is shown below.

    DBMS_OUTPUT.DISABLE;

### PUT and PUT_LINE Procedures

You can either place an entire line of information into the buffer by calling `PUT_LINE`, or you can build a line of information piece by piece by making multiple calls to `PUT`. Both of these procedures are overloaded to accept items of type `VARCHAR2`, `NUMBER`, or `DATE` to place in the buffer.

All items are converted to `VARCHAR2` as they are retrieved. If you pass an item of type `NUMBER` or `DATE`, when that item is retrieved, it is formatted with `TO_CHAR` using the default format. If you want to use a different format, you should pass in the item as `VARCHAR2` and format it explicitly.

When you call `PUT_LINE`, the item that you specify is automatically followed by an end-of-line marker. If you make calls to `PUT` to build a line, you must add your own end-of-line marker by calling `NEW_LINE`. `GET_LINE` and `GET_LINES` do not return lines that have not been terminated with a newline character.

If your line exceeds the buffer limit, you receive an error message.

Note: Output that you create using `PUT` or `PUT_LINE` is buffered in the SGA. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller. So, for example, Enterprise Manager or SQL*Plus do not display `DBMS_OUTPUT` messages until the PL/SQL program completes. In this release, there is no mechanism for flushing the `DBMS_OUTPUT` buffers within the PL/SQL program.

#### Syntax

The `PUT` and `PUT_LINE` procedure are overloaded; they can take an `IN` parameter of either `NUMBER`, `VARCHAR2`, or `DATE`. The syntax for the `PUT` and `PUT_LINE`, and the `NEW_LINE` procedures is

    DBMS_OUTPUT.PUT     (item IN NUMBER);
    DBMS_OUTPUT.PUT     (item IN VARCHAR2);
    DBMS_OUTPUT.PUT     (item IN DATE);
    DBMS_OUTPUT.PUT_LINE(item IN NUMBER);
    DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);
    DBMS_OUTPUT.PUT_LINE(item IN DATE);
    DBMS_OUTPUT.NEW_LINE;

### GET_LINE and GET_LINES Procedures

You can choose to retrieve a single line from the buffer, or an array of lines. Call the `GET_LINE` procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the `GET_LINES` procedure to retrieve an array of lines from the buffer. You can choose to automatically display this information if you are using Enterprise Manager or SQL*Plus by using the special `SET` `SERVEROUTPUT` `ON` command.

After calling `GET_LINE` or `GET_LINES`, any lines not retrieved before the next call to `PUT`, `PUT_LINE`, or `NEW_LINE` are discarded to avoid confusing them with the next message.

#### Syntax

The parameters for the `GET_LINE` procedure are described in Table 12-11. The syntax for this procedure is shown below.

    DBMS_OUTPUT.GET_LINE(line   OUT VARCHAR2,
                         status OUT INTEGER);

`

##### Table 12-11 DBMS_OUTPUT.GET_LINE Procedure Parameters

Parameter | Description
--- | ---
    line
| Returns a single line of buffered information, excluding a final newline character. The maximum length of this parameter is 255 bytes.
    status
| If the call completes successfully, the status returns as 0. If there are no more lines in the buffer, the status is 1.

`

The parameters for the `GET_LINES` procedure are described in Table 12-12. The syntax for this procedure is

    DBMS_OUTPUT.GET_LINES(lines       OUT  CHARARR,
                          numlines IN OUT  INTEGER);

where `CHARARR` is a table of `VARCHAR2(255)`, defined as a type in the `DBMS_OUTPUT` package specification.

##### Table 12-12 DBMS_OUTPUT.GET_LINE Procedure Parameters

Parameter | Description
--- | ---
    lines
| Returns an array of lines of buffered information. The maximum length of each line in the array is 255 bytes.
    numlines
| Specify the number of lines you want to retrieve from the buffer. After retrieving the specified number of lines, the procedure returns the number of lines actually retrieved. If this number is less than the number of lines requested, there are no more lines in the buffer.

### Examples Using the DBMS_OUTPUT Package

The `DBMS_OUTPUT` package is commonly used to debug stored procedures and triggers, as shown in example 1. This package can also be used to allow a user to retrieve information about an object and format this output, as shown in example 2.

##### Example 1

An example of a function that queries the employee table and returns the total salary for a specified department follows. The function includes several calls to the `PUT_LINE` procedure:

    CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
       CURSOR emp_cursor IS
          SELECT sal, comm FROM emp WHERE deptno = dnum;
       total_wages    NUMBER(11, 2) := 0;
       counter        NUMBER(10) := 1;
    BEGIN
       FOR emp_record IN emp_cursor LOOP
          emp_record.comm := NVL(emp_record.comm, 0);
          total_wages := total_wages + emp_record.sal
             + emp_record.comm;
          DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
             '; Wages = '|| TO_CHAR(total_wages));  /* Debug line */
          counter := counter + 1; /* Increment debug counter */
       END LOOP;
       /* Debug line */
       DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
         TO_CHAR(total_wages));
       RETURN total_wages;
    END dept_salary;

Assume the EMP table contains the following rows:

    EMPNO          SAL     COMM     DEPT
    -----        ------- -------- -------
    1002           1500      500      20
    1203           1000               30
    1289           1000               10
    1347           1000      250      20

Assume you execute the following statements in the Enterprise Manager SQL Worksheet input pane:

    SET SERVEROUTPUT ON
    VARIABLE salary NUMBER;
    EXECUTE :salary := dept_salary(20);

You would then see the following information displayed in the output pane:

    Loop number = 1; Wages = 2000
    Loop number = 2; Wages = 3250
    Total wages = 3250

    PL/SQL procedure successfully executed.

##### Example 2

This example assumes that the user has used the `EXPLAIN` `PLAN` command to retrieve information about the execution plan for a statement and store it in `PLAN_TABLE`, and that the user has assigned a statement `ID` to this statement. The example `EXPLAIN_OUT` procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement.

     /****************************************************************/
    /* Create EXPLAIN_OUT procedure. User must pass STATEMENT_ID to */
    /* to procedure, to uniquely identify statement.                */
    /****************************************************************/
    CREATE OR REPLACE PROCEDURE explain_out
       (statement_id IN VARCHAR2) AS

       -- Retrieve information from PLAN_TABLE into cursor
       -- EXPLAIN_ROWS.
       CURSOR explain_rows IS
          SELECT level, id, position, operation, options,
             object_name
          FROM plan_table
          WHERE statement_id = explain_out.statement_id
          CONNECT BY PRIOR id = parent_id
             AND statement_id = explain_out.statement_id
          START WITH id = 0
           ORDER BY id;

    BEGIN
       -- Loop through information retrieved from PLAN_TABLE
       FOR line IN explain_rows LOOP

          -- At start of output, include heading with estimated cost.
          IF line.id = 0 THEN
             DBMS_OUTPUT.PUT_LINE ('Plan for statement '
                || statement_id
                || ', estimated cost = ' || line.position);
          END IF;

          -- Output formatted information. LEVEL is used to
          -- determine indention level.
          DBMS_OUTPUT.PUT_LINE (lpad(' ',2*(line.level-1)) ||
             line.operation || ' ' || line.options || ' ' ||
             line.object_name);
       END LOOP;
    END;

## PL/SQL File I/O

The release 7.3 Oracle Server adds file input/output capabilities to PL/SQL. This is done through the supplied package `UTL_FILE`.

The file I/O capabilities are similar to those of the standard operating system stream file I/O (`OPEN`, `GET`, `PUT`, `CLOSE`), with some limitations. For example, you call the `FOPEN` function to return a file handle, which you then use in subsequent calls to `GET_LINE` or `PUT` to perform stream I/O to a file. When you are done performing I/O on the file, you call `FCLOSE` to complete any output and to free any resources associated with the file.

### Summary

Table 12-13 summarizes the procedures you can call in the `UTL_FILE` package.

##### Table 12-13 UTL_FILE Procedures

Function/Procedure | Description | Refer to
--- | --- | ---
    FOPEN
| Open a file for input or output. Create an output file if it does not exist. | page 12-34
    IS_OPEN
| Determine if a file handle refers to an open file. | page 12-36
    FCLOSE
| Close a file. | page 12-37
    FCLOSE_ALL
| Close all open file handles. | page 12-38
    GET_LINE
| Read a line of text from an open file. | page 12-38
    PUT
| Write a line to a file. Do not append a line terminator. | page 12-39
    PUT_LINE
| Write a line to a file. Append an OS-specific line terminator. | page 12-41
    PUTF
| A `PUT` procedure with formatting. | page 12-41
    NEW_LINE
| Write one or more OS-specific line terminators to a file. | page 12-40
    FFLUSH
| Physically write all pending output to a file. | page 12-43

### Security

The PL/SQL file I/O feature is available for both client side and server side PL/SQL. The client implementation is subject to normal operating system file permission checking, and so does not need any additional security constraints. But the server implementation might be running in a privileged mode, and so will need additional security restrictions that limit the power of this feature.

Note: The `UTL_FILE` package is similar to the client-side `TEXT_IO` package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between `UTL_FILE` and `TEXT_IO`. In PL/SQL file I/O, errors are returned to the caller using PL/SQL exceptions.

#### Server Security

Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (`INIT`.`ORA`).

You specify the accessible directories for the `UTL_FILE` functions in the initialization file using the `UTL_FILE_DIR` parameter, as follows:

    UTL_FILE_DIR = <directory name>

For example, if the initialization file for the instance contains the line

    UTL_FILE_DIR = /usr/jsmith/my_app

then the directory /usr/jsmith/my_app is accessible to the `FOPEN` function. Note that a directory named /usr/jsmith/My_App would not be accessible on case-sensitive operating systems.

The parameter specification

    UTL_FILE_DIR = *

has a special meaning. This entry in effect turns off directory access checking, and makes any directory accessible to the `UTL_FILE` functions.

WARNING: The '*' option should be used with great caution. For obvious security reasons, Oracle does not recommend that you use this option in production systems. Also, do not include '.' (the current directory for UNIX) in the accessible directories list. To ensure security on file systems that allow symbolic links, users must not be allowed WRITE permission to directories accessible by PL/SQL file I./O functions. The symbolic links and PL/SQL file I/O could be used to circumvent normal operating system permission checking, and allow users read/write access to directories to which they would not otherwise have access.

#### File Ownership and Protections

On UNIX systems, a file created by the `FOPEN` function has as its owner the owner of the shadow process running the instance. In the normal case, this owner is oracle. Files created using `FOPEN` are always writable and readable using the `UTL_FILE` routines, but non-privileged users who need to read these files outside of PL/SQL might have to get their system administrator to give them access.

#### Examples (UNIX-Specific)

If the parameter initialization file contains only

    UTL_FILE_DIR=/appl/gl/log
    UTL_FILE_DIR=/appl/gl/out

then the following file locations and filenames are valid:

    FILE LOCATION             FILENAME
    /appl/gl/log              L10324.log
    /appl/gl/out              O10324.out

but the following file locations and filename are invalid:

    FILE LOCATION             FILENAME
    /appl/gl/log/backup       L10324.log          # subdirectory
    /APPL/gl/log              L10324.log          # uppercase
    /appl/gl/log              backup/L10324.log   #dir in name
    /usr/tmp                  T10324.tmp       # not in INIT.ORA

WARNING: There are no user-level file permissions. All file locations specified by the `UTL_FILE_DIR` parameters are valid, for both reading and writing, for all users of the file I/O procedures. This can override operating system file permissions.

### Declared Types

The specification for the `UTL_FILE` package declares one PL/SQL type: `FILE_TYPE`. The declaration is

    TYPE file_type IS RECORD (id BINARY_INTEGER);

The contents of `FILE_TYPE` are private to the `UTL_FILE` package. Users of the package should not reference or change components of this record.

### Exceptions

The specification for the `UTL_FILE` package declares seven exceptions. These exceptions are raised to indicate error conditions. The exceptions are shown in Table 12-14.

##### Table 12-14 UTL_FILE Package Exceptions

Exception Name | Description
    INVALID_PATH
| File location or filename was invalid.
    INVALID_MODE
| The `open_mode` parameter in FOPEN was invalid.
    INVALID_FILEHANDLE
| The file handle was invalid.
    INVALID_OPERATION
| The file could not be opened or operated on as requested.
    READ_ERROR
| An operating system error occurred during the read operation.
    WRITE_ERROR
| An operating system error occurred during the write operation.
    INTERNAL_ERROR
| An unspecified error in PL/SQL.

In addition to these package exceptions, procedures in the `UTL_FILE` package can also raise predefined PL/SQL exceptions such as `NO_DATA_FOUND` or `VALUE_ERROR`.

#### Functions and Procedures

The remainder of this section describes the individual functions and procedures that make up the `UTL_FILE` package.

### FOPEN

`FOPEN` opens a file for input or output. The file location must be an accessible directory, as defined in the instance's initialization parameter `UTL_FILE_DIR`. The complete directory path must already exist; it is not created by `FOPEN`. `FOPEN` returns a file handle, which must be used in all subsequent I/O operations on the file.

The parameters for this procedure are described in Table 12-15, and the syntax is shown below.

#### Syntax

    FUNCTION FOPEN(location  IN VARCHAR2,
                   filename  IN VARCHAR2,
                   open_mode IN VARCHAR2)
        RETURN UTL_FILE.FILE_TYPE;

##### Table 12-15 FOPEN Function Parameters

Parameters | Description
    location
| The operating system-specific string that specifies the directory or area in which to open the file.
    filename
| The name of the file, including extension (file type), without any directory path information. (Under the UNIX operating system, the filename cannot be terminated with a '/'.)
    open_mode
| A string that specifies how the file is to be opened (either upper- or lowercase letters can be used). The supported values, and the `UTL_FILE` package procedures that can be used with them are: 'r' read text (`GET_LINE`) 'w' write text (`PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH`) 'a' append text (`PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH`)

Note: If you open a file that does not exist using the 'a' value for `OPEN_MODE`, the file is created in write ('w') mode.

#### Return Value

`FOPEN` returns a file handle, which must be passed to all subsequent procedures that operate on that file. The specific contents of the file handle are private to the `UTL_FILE` package, and individual components should not be referenced or changed by the `UTL_FILE` user.

Note: The file location and file name parameters are supplied to the `FOPEN` function as separate strings, so that the file location can be checked against the list of accessible directories as specified in the initialization file. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name in the initialization file. Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file name parameters.

#### Exceptions

FOPEN can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_PATH`
- `UTL_FILE`.`INVALID_MODE`
- `UTL_FILE`.`INVALID_OPERATION`

### IS_OPEN

`IS_OPEN` tests a file handle to see if it identifies an open file. `IS_OPEN` reports only whether a file handle represents a file that has been opened, but not yet closed. It does not guarantee that there will be no operating system errors when you attempt to use the file handle.

The parameter for this function is described in Table 12-16, and the syntax is shown below.

#### Syntax

    FUNCTION IS_OPEN(file_handle  IN FILE_TYPE)
        RETURN BOOLEAN;

`

##### Table 12-16 IS_OPEN Function Parameters

Parameter | Description
    file_handle
| An active file handle returned by an `FOPEN` call.

`

#### Return Value

`TRUE` or `FALSE`.

#### Exceptions

`IS_OPEN` does not raise any exceptions.

### FCLOSE

`FCLOSE` closes an open file identified by a file handle. You could receive a `WRITE_ERROR` exception when closing a file, as there might be buffered data yet to be written when `FCLOSE` executes.

The parameters for this procedure are described in Table 12-17, and the syntax is shown below.

#### Syntax

    PROCEDURE FCLOSE (file_handle IN OUT FILE_TYPE);

`

##### Table 12-17 FCLOSE Procedure Parameters

Parameter | Description
    file_handle
| An active file handle returned by an `FOPEN` call.

`

#### Exceptions

`FCLOSE` can raise the following exceptions:

- `UTL_FILE`.`WRITE_ERROR`
- `UTL_FILE`.`INVALID_FILEHANDLE`

### FCLOSE_ALL

`FCLOSE_ALL` closes all open file handles for the session. This can be used as an emergency cleanup procedure, for example when a PL/SQL program exits on an exception.

`

Note: `FCLOSE_ALL` does not alter the state of the open file handles held by the user. This means that an `IS_OPEN` test on a file handle after an `FCLOSE_ALL` call still returns `TRUE`, even though the file has been closed. No further read or write operations can be performed on a file that was open before an `FCLOSE_ALL`.

`

#### Syntax

    PROCEDURE FCLOSE_ALL;

#### Exception

`FCLOSE_ALL` can raise the exception:

- `UTL_FILE`.`WRITE_ERROR`

### GET_LINE

`GET_LINE` reads a line of text from the open file identified by the file handle, and places the text in the output buffer parameter. Text is read up to but not including the line terminator, or up to the end of the file.

If the line does not fit in the buffer, a `VALUE_ERROR` exception is raised. If no text was read due to "end of file," the `NO_DATA_FOUND` exception is raised.

Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.

The maximum size of an input record is 1022 bytes.

The parameters for this procedure are described in Table 12-18, and the syntax is shown below.

#### Syntax

    PROCEDURE GET_LINE(file_handle     IN  FILE_TYPE,
                       buffer          OUT VARCHAR2);

`

##### Table 12-18 GET_LINE Procedure Parameters

Parameters | Description
    file_handle
| An active file handle returned by an `FOPEN` call. The file must be open for reading (mode 'r'), otherwise an `INVALID_OPERATION` exception is raised.
    buffer
| The data buffer to receive the line read from the file.

`

#### Exceptions

`GET_LINE` can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_FILEHANDLE`
- `UTL_FILE`.`INVALID_OPERATION`
- `UTL_FILE`.`READ_ERROR`
- `NO_DATA_FOUND`
- `VALUE_ERROR`

### PUT

`PUT` writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. No line terminator is appended by `PUT`; use `NEW_LINE` to terminate the line or use `PUT_LINE` to write a complete line with a line terminator.

The parameters for this procedure are described in Table 12-19, and the syntax is shown below.

#### Syntax

    PROCEDURE PUT(file_handle  IN FILE_TYPE,
                  buffer       IN VARCHAR2);

`

##### Table 12-19 PUT Procedure Parameters

Parameters | Description
    file_handle
| An active file handle returned by an `FOPEN` call.
    buffer
| The buffer that contains the text to be written to the file. You must have opened the file using mode 'w' or mode 'a', otherwise an `INVALID_OPERATION` exception is raised.

`

#### Exceptions

`PUT` can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_FILEHANDLE`
- `UTL_FILE`.`INVALID_OPERATION`
- `UTL_FILE`.`WRITE_ERROR`

### NEW_LINE

`NEW_LINE` writes one or more line terminators to the file identified by the input file handle. This procedure is separate from `PUT` because the line terminator is a platform-specific character or sequence of characters.

The parameters for this procedure are described in Table 12-20, and the syntax is shown below.

#### Syntax

    PROCEDURE NEW_LINE (file_handle  IN FILE_TYPE,
                        lines        IN NATURAL := 1);

`

##### Table 12-20 NEW_LINE Procedure Parameters

Parameters | Description
    file_handle
| An active file handle returned by an `FOPEN` call.
    lines
| The number of line terminators to be written to the file.

`

#### Exceptions

`NEW_LINE` can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_FILEHANDLE`
- `UTL_FILE`.`INVALID_OPERATION`
- `UTL_FILE`.`WRITE_ERROR`

### PUT_LINE

`PUT_LINE` writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. `PUT_LINE` terminates the line with the platform-specific line terminator character or characters.

The maximum size for an output record is 1023 bytes.

The parameters for this procedure are described in Table 12-21, and the syntax is shown below.

#### Syntax

    PROCEDURE PUT_LINE(file_handle  IN FILE_TYPE,
                       buffer       IN VARCHAR2);

`

##### Table 12-21 PUT_LINE Procedure Parameters

Parameters | Description
    file_handle
| An active file handle returned by an `FOPEN` call.
    buffer
| The text buffer that contains the lines to be written to the file.

`

#### Exceptions

`PUT_LINE` can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_FILEHANDLE`
- `UTL_FILE`.`INVALID_OPERATION`
- `UTL_FILE`.`WRITE_ERROR`

### PUTF

`PUTF` is a formatted `PUT` procedure. It works like a limited printf(). The format string can contain any text, but the character sequences '%s' and '\n' have special meaning.

:

%s | Substitute this sequence with the string value of the next argument in the argument list (see the "Syntax" section below).
\n | Substitute with the appropriate platform-specific line terminator.

The parameters for this procedure are described in Table 12-22, and the syntax is shown below.

#### Syntax

    PROCEDURE PUTF(file_handle  IN FILE_TYPE,
                   format       IN VARCHAR2,
                   [arg1        IN VARCHAR2,
                    . . .
                    arg5        IN VARCHAR2]);

`

##### Table 12-22 PUTF Procedure Parameters

Parameters | Description
    file_handle
| An active file handle returned by an `FOPEN` call.
    format
| The format string that can contain text as well as the formatting characters '\n' and '%s'.
    arg1..arg5
| From one to five optional argument strings. Argument strings are substituted, in order, for the '%s' formatters in the format string. If there are more formatters in the format parameter string than there are arguments, an empty string is substituted for each '%s' for which there is no argument.

`

#### Example

The following example writes the lines

    Hello, world!
    I come from Zork with greetings for all earthlings.

    my_world  varchar2(4) := 'Zork';
    ...
    PUTF(my_handle, 'Hello, world!\nI come from %s with %s.\n',
                    my_world,
                    'greetings for all earthlings');

If there are more %s formatters in the format parameter than there are arguments, an empty string is substituted for each %s for which there is no matching argument.

#### Exceptions

`PUTF` can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_FILEHANDLE`
- `UTL_FILE`.`INVALID_OPERATION`
- `UTL_FILE`.`WRITE_ERROR`

### FFLUSH

`FFLUSH` physically writes all pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The `FFLUSH` procedure forces any buffered data to be written to the file.

Flushing is useful when the file must be read while still open. For example, debugging messages can be flushed to the file so that they can be read immediately.

The parameter for this procedure is described in Table 12-23, and the syntax is shown below.

#### Syntax

    PROCEDURE FFLUSH (file_handle  IN FILE_TYPE);

`

##### Table 12-23 FFLUSH Procedure Parameters

Parameters | Description
    file_handle
| An active file handle returned by an `FOPEN` call.

`

#### Exceptions

`FFLUSH` can raise any of the following exceptions:

- `UTL_FILE`.`INVALID_FILEHANDLE`
- `UTL_FILE`.`INVALID_OPERATION`
- `UTL_FILE`.`WRITE_ERROR`

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index