• External service interface: You can communicate with user-written services that are external to the RDBMS. This can be done effectively in a shared server process, so that several instances of the service are executing simultaneously. Additionally, the services are available asynchronously. The requestor of the service does not need to block a waiting reply. The requestor can check (with or without time out) at a later time. The service can be written in any of the 3GL languages that Oracle supports.

• Independent transactions: The pipe can communicate to a separate session which can perform an operation in an independent transaction (such as logging an attempted security violation detected by a trigger).

• Alerters (non-transactional): You can post another process without requiring the waiting process to poll. If an "after-row" or "after-statement" trigger were to alert an application, then the application would treat this alert as an indication that the data probably changed. The application would then read the data to get the current value. Because this is an "after" trigger, the application would want to do a "SELECT FOR UPDATE" to make sure it read the correct data.

• Debugging: Triggers and stored procedures can send debugging information to a pipe. Another session can keep reading out of the pipe and display it on the screen or write it to a file.

• Concentrator: This is useful for multiplexing large numbers of users over a fewer number of network connections, or improving performance by concentrating several user-transactions into one DBMS transaction.

Depending upon your security requirements, you may choose to use either public pipes or private pipes, which are described in DBMS_PIPE Operational Notes.

maxwait   constant integer := 86400000; /* 1000 days */ 

The operation of DBMS_PIPE is considered with regard to the following topics:

• Public Pipes

• Writing and Reading Pipes

• Private Pipes

Public Pipes

You may create a public pipe either implicitly or explicitly. For implicit public pipes, the pipe is automatically created when it is 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 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.

Reading and Writing Pipes

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 they know the name of the 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 function, 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 function, 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 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:

• 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

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.

Example 1: Debugging - PL/SQL

This example shows the procedure that 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;

Example 2: Debugging - Pro*C

The following Pro*C code receives messages from the PLSQL_DEBUG pipe in the previous example, and displays the messages. If the Pro*C session is run in a separate window, then 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 3: Execute System Commands

This example shows PL/SQL and Pro*C code 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 sleeps and waits for a message to arrive on the named pipe. When a message arrives, the Pro*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 always sends a message back to the package (except in the case of the STOP command). This is valuable, because 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:

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

On a UNIX system, this causes the Pro*C daemon to execute the command system("ls -la").

Remember that the daemon needs to be running first. 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.

DAEMON.SQL Example. 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 Example. 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.

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()
{
command.len = 20; /*initialize length components*/ 
value.len = 2000; 
return_name.len  = 30; 
  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);

Example 4: External Service Interface

Put the user-written 3GL code into an OCI or Precompiler program. The program connects to the database and executes PL/SQL code to read its request from the pipe, computes the result, and then executes PL/SQL code to send the result on a pipe back to the requestor.

Below is an example of a stock service request. The recommended sequence for the arguments to pass on the pipe for all service requests is:

      protocol_version      VARCHAR2        - '1', 10 bytes or less
      returnpipe            VARCHAR2        - 30 bytes or less
      service               VARCHAR2        - 30 bytes or less
      arg1                  VARCHAR2/NUMBER/DATE
         ...
      argn                  VARCHAR2/NUMBER/DATE

The recommended format for returning the result is:

      success               VARCHAR2        - 'SUCCESS' if OK,
                                              otherwise error message
      arg1                  VARCHAR2/NUMBER/DATE
         ...
      argn                  VARCHAR2/NUMBER/DATE

The "stock price request server" would do, using OCI or PRO* (in pseudo-code):

    <loop forever>
      BEGIN dbms_stock_server.get_request(:stocksymbol); END;
      <figure out price based on stocksymbol (probably from some radio
            signal), set error if can't find such a stock>
      BEGIN dbms_stock_server.return_price(:error, :price); END;

A client would do:

    BEGIN :price := stock_request('YOURCOMPANY'); end;

The stored procedure, dbms_stock_server, which is called by the preceding "stock price request server" is:

    CREATE OR REPLACE PACKAGE dbms_stock_server IS
      PROCEDURE get_request(symbol OUT VARCHAR2);
      PROCEDURE return_price(errormsg IN VARCHAR2, price IN VARCHAR2);
    END;
  
    CREATE OR REPLACE PACKAGE BODY dbms_stock_server IS
      returnpipe    VARCHAR2(30);
  
      PROCEDURE returnerror(reason VARCHAR2) IS
        s INTEGER;
      BEGIN
        dbms_pipe.pack_message(reason);
        s := dbms_pipe.send_message(returnpipe);
        IF s <> 0 THEN
          raise_application_error(-20000, 'Error:' || to_char(s) ||
            ' sending on pipe');
        END IF;
      END;
  
      PROCEDURE get_request(symbol OUT VARCHAR2) IS
        protocol_version VARCHAR2(10);
        s                INTEGER;
        service          VARCHAR2(30);
      BEGIN
        s := dbms_pipe.receive_message('stock_service');
        IF s <> 0 THEN
          raise_application_error(-20000, 'Error:' || to_char(s) ||
            'reading pipe');
        END IF;
        dbms_pipe.unpack_message(protocol_version);
        IF protocol_version <> '1' THEN
          raise_application_error(-20000, 'Bad protocol: ' || 
            protocol_version);
        END IF;
        dbms_pipe.unpack_message(returnpipe);
        dbms_pipe.unpack_message(service);
        IF service != 'getprice' THEN
          returnerror('Service ' || service || ' not supported');
        END IF;
        dbms_pipe.unpack_message(symbol);
      END;
  
      PROCEDURE return_price(errormsg in VARCHAR2, price in VARCHAR2) IS
        s INTEGER;
      BEGIN
        IF errormsg is NULL THEN
          dbms_pipe.pack_message('SUCCESS');
          dbms_pipe.pack_message(price);
        ELSE
          dbms_pipe.pack_message(errormsg);
        END IF;
        s := dbms_pipe.send_message(returnpipe);
        IF s <> 0 THEN
          raise_application_error(-20000, 'Error:'||to_char(s)||
            ' sending on pipe');
        END IF;
      END;
    END;
  

The procedure called by the client is:

    CREATE OR REPLACE FUNCTION stock_request (symbol VARCHAR2) 
        RETURN VARCHAR2 IS
      s        INTEGER;
      price    VARCHAR2(20);
      errormsg VARCHAR2(512);
    BEGIN
      dbms_pipe.pack_message('1');  -- protocol version
      dbms_pipe.pack_message(dbms_pipe.unique_session_name); -- return pipe
      dbms_pipe.pack_message('getprice');
      dbms_pipe.pack_message(symbol);
      s := dbms_pipe.send_message('stock_service');
      IF s <> 0 THEN
        raise_application_error(-20000, 'Error:'||to_char(s)||
          ' sending on pipe');
      END IF;
      s := dbms_pipe.receive_message(dbms_pipe.unique_session_name);
      IF s <> 0 THEN
        raise_application_error(-20000, 'Error:'||to_char(s)||
          ' receiving on pipe');
      END IF;
      dbms_pipe.unpack_message(errormsg);
      IF errormsg <> 'SUCCESS' THEN
        raise_application_error(-20000, errormsg);
      END IF;
      dbms_pipe.unpack_message(price);
      RETURN price;
    END;

You would typically only GRANT EXECUTE on DBMS_STOCK_SERVICE to the stock service application server, and would only GRANT EXECUTE on stock_request to those users allowed to use the service.