📋Steps in this guide1/4

DBMS_PIPE - For Inter-Session Communication

This article presents a brief description of the <code>DBMS_PIPE</code> package, explaining how it can be used for non-secure inter-session mesaging.

oracle miscconfigurationintermediate

by OracleDba

14 views

Overview

The package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required. There are two types of pipes: - Implicit Pipes - These are created automatically when a message is sent with an unknown pipename using the function. Implicit pipes disappear when they are empty. - Explicit Pipes - These are created using the function. Explicitly created pipes must be removed using the function. There are two levels of security for pipes: - Public Pipes - These are accessible by any user with permission on the package. Implicit pipes are always public, but they can also be created explicitly by calling the function with the parameter set to FALSE. - Private Pipes - These are only accessible by sessions with the same ownerid as the pipe creator, stored programs owned by the pipe creator or by users connected as SYSDBA. The following example used the function to create explicit public and private pipes. The view displays information about the pipes.

Section 2

The following example uses the function to remove the pipes created previously. Messages are packed, one variable at a time, into a buffer using the procedure. Once the message is complete it is sent using the function. Messages are recieved using the function and unpacked, one variable at a time, using the procedure. The following code creates a package (message_api) that contains two procedures (send and receive) that allow messages to be sent an received on an implicit public pipe. To test the package, run the following code in one session. The session will appear to hang, waiting for a message to be read off the pipe.

Code/Command (click line numbers to comment):

DECLARE
  l_result  INTEGER;
BEGIN
  -- Explicit public pipe.
  l_result := DBMS_PIPE.create_pipe(pipename => 'explicit_public_pipe',
                                    private  => FALSE);

  -- Explicit private pipe.
  l_result := DBMS_PIPE.create_pipe(pipename => 'explicit_private_pipe');
END;
/

PL/SQL procedure successfully completed.

COLUMN name FORMAT A30
SELECT * FROM v$db_pipes;

   OWNERID NAME                           TYPE     PIPE_SIZE
---------- ------------------------------ ------- ----------
        55 EXPLICIT_PRIVATE_PIPE          PRIVATE        358
           EXPLICIT_PUBLIC_PIPE           PUBLIC         357

2 rows selected.

SQL>

DECLARE
  l_result  INTEGER;
BEGIN
  -- Explicit public pipe.
  l_result := DBMS_PIPE.remove_pipe(pipename => 'explicit_public_pipe');

  -- Explicit private pipe.
  l_result := DBMS_PIPE.remove_pipe(pipename => 'explicit_private_pipe');
END;
/

PL/SQL procedure successfully completed.

COLUMN name FORMAT A30
SELECT * FROM v$db_pipes;

no rows selected

SQL>

CREATE OR REPLACE PACKAGE message_api AS
  PROCEDURE send (p_number  IN  NUMBER,
                  p_text    IN  VARCHAR2,
                  p_date    IN  DATE DEFAULT SYSDATE);
  PROCEDURE receive;
END message_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY message_api AS
  PROCEDURE send (p_number  IN  NUMBER,
                  p_text    IN  VARCHAR2,
                  p_date    IN  DATE DEFAULT SYSDATE) AS
    l_status  NUMBER;
  BEGIN
    DBMS_PIPE.pack_message(p_number);
    DBMS_PIPE.pack_message(p_text);
    DBMS_PIPE.pack_message(p_date);

    l_status := DBMS_PIPE.send_message('message_pipe');
    IF l_status != 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'message_pipe error');
    END IF;
  END;
  
  PROCEDURE receive AS
    l_result  INTEGER;
    l_number  NUMBER;
    l_text    VARCHAR2(32767);
    l_date    DATE;
  BEGIN
    l_result := DBMS_PIPE.receive_message (
                  pipename => 'message_pipe',
                  timeout  => DBMS_PIPE.maxwait);
    
    IF l_result = 0 THEN
      -- Message received successfully.
      DBMS_PIPE.unpack_message(l_number);
      DBMS_PIPE.unpack_message(l_text);
      DBMS_PIPE.unpack_message(l_date);
      
      DBMS_OUTPUT.put_line('l_number: ' || l_number);
      DBMS_OUTPUT.put_line('l_text  : ' || l_text);
      DBMS_OUTPUT.put_line('l_date  : ' || l_date);
    ELSE
      RAISE_APPLICATION_ERROR(-20002, 'message_api.receive was unsuccessful. Return result: ' || l_result);
    END IF;              
  END receive;
END message_api;
/
SHOW ERRORS

Section 3

In another session, run the following code to send a message. The procedure call in the first session immediately returns, printing out the contents of the message, as seen below. For further information see:

Code/Command (click line numbers to comment):

CONN test/test
SET SERVEROUTPUT ON
EXEC message_api.receive;

CONN test/test
BEGIN
  message_api.send(p_number => 12345,
                   p_text   => 'This is a test.',
                   p_date   => SYSDATE);
END;
/

SQL> CONN test/test
Connected.
SQL> SET SERVEROUTPUT ON
SQL> EXEC message_api.receive;
l_number: 12345
l_text  : This is a test.
l_date  : 20-NOV-2005 13:35:57

PL/SQL procedure successfully completed.

SQL>

DBA Hub

DBMS_PIPE - For Inter-Session Communication

Overview

Section 2

Code/Command (click line numbers to comment):

Section 3

Code/Command (click line numbers to comment):

Section 4

Comments (0)