Oracle PL/SQL Tutorial/System Packages/DBMS PIPE

DBMS_PIPE.CREATE_PIPE

SQL>
SQL>
SQL> DECLARE
  2     lv_status_num PLS_INTEGER;
  3  BEGIN
  4     lv_status_num := DBMS_PIPE.CREATE_PIPE("TEST 1");
  5     DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num);
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

DBMS_PIPE.PACK_MESSAGE

SQL>
SQL>
SQL> DECLARE
  2    v_status  NUMBER;
  3  BEGIN
  4      DBMS_PIPE.PACK_MESSAGE("Insert");
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL>

DBMS_PIPE.RECEIVE_MESSAGE

SQL>
SQL>
SQL> DECLARE
  2     lv_status_num PLS_INTEGER;
  3  BEGIN
  4     lv_status_num := DBMS_PIPE.RECEIVE_MESSAGE("TEST 1");
  5     DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num);
  6  END;
  7  /
PL/SQL procedure successfully completed.

DBMS_PIPE.RECEIVE_MESSAGE with timeout

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION receive_message (pipe IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     stat PLS_INTEGER;
  5     retval VARCHAR2(4096);
  6  BEGIN
  7     stat := DBMS_PIPE.RECEIVE_MESSAGE (pipe, timeout => 10);
  8     IF stat = 0
  9     THEN
 10        DBMS_PIPE.UNPACK_MESSAGE (retval);
 11     END IF;
 12     RETURN retval;
 13  END;
 14  /
Warning: Function created with compilation errors.
SQL>
SQL> show error
Errors for FUNCTION RECEIVE_MESSAGE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4      PL/SQL: Statement ignored
7/12     PLS-00201: identifier "DBMS_PIPE" must be declared
10/7     PL/SQL: Statement ignored
10/7     PLS-00201: identifier "DBMS_PIPE" must be declared
SQL>
SQL>

DBMS_PIPE.REMOVE_PIPE

SQL>
SQL>
SQL> DECLARE
  2     lv_status_num PLS_INTEGER;
  3  BEGIN
  4     lv_status_num := DBMS_PIPE.REMOVE_PIPE("TEST 1");
  5     DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num);
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>

DBMS_PIPE.RESET_BUFFER

SQL>
SQL> CREATE OR REPLACE PROCEDURE forward(from_in IN VARCHAR2,to_in IN VARCHAR2,timeout_secs_IN IN INTEGER := 10) IS
  2     stat INTEGER;
  3  BEGIN
  4     DBMS_PIPE.RESET_BUFFER;
  5     DBMS_PIPE.PACK_MESSAGE("message");
  6
  7     stat := DBMS_PIPE.RECEIVE_MESSAGE (from_in, timeout=>timeout_secs_IN);
  8     IF stat = 0
  9     THEN
 10        stat := DBMS_PIPE.SEND_MESSAGE (to_in, timeout=>timeout_secs_IN);
 11     END IF;
 12  END forward;
 13  /

DBMS_PIPE.SEND_MESSAGE

SQL>
SQL> DECLARE
  2     lv_status_num PLS_INTEGER;
  3  BEGIN
  4     lv_status_num := DBMS_PIPE.SEND_MESSAGE("TEST 1");
  5     DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num);
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL>

DBMS_PIPE.SEND_MESSAGE with timeout

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE send_message (pipe IN VARCHAR2, dtval IN DATE)
  2  IS
  3     stat PLS_INTEGER;
  4  BEGIN
  5     DBMS_PIPE.PACK_MESSAGE (dtval);
  6     stat := DBMS_PIPE.SEND_MESSAGE (pipe, timeout => 60);
  7     IF stat != 0
  8     THEN
  9        DBMS_OUTPUT.PUT_LINE ("Unable to send message to " || pipe);
 10     END IF;
 11  END;
 12  /
Warning: Procedure created with compilation errors.
SQL>
SQL>

DBMS_PIPE.UNPACK_MESSAGE and record type

SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
  2  IS
  3     TYPE myRec IS RECORD (
  4        start_date DATE,
  5        trial_cost NUMBER,
  6        section_id VARCHAR2(100)
  7     );
  8     FUNCTION critical_info RETURN myRec;
  9  END;
 10  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
  2  IS
  3     thirty_days CONSTANT INTEGER := 30 * 24 * 60 * 60;
  4     FUNCTION critical_info RETURN myRec
  5     IS
  6        stat PLS_INTEGER;
  7        retval myRec;
  8     BEGIN
  9        stat := DBMS_PIPE.RECEIVE_MESSAGE ("impeachment", timeout => thirty_days);
 10        IF stat = 0
 11        THEN
 12           DBMS_PIPE.UNPACK_MESSAGE (retval.start_date);
 13           DBMS_PIPE.UNPACK_MESSAGE (retval.trial_cost);
 14           DBMS_PIPE.UNPACK_MESSAGE (retval.section_id);
 15        END IF;
 16        RETURN retval;
 17     END;
 18  END;
 19  /

Use dbms_pipe package

SQL>
SQL> create or replace procedure proc( cmd in varchar2 )
  2  as
  3      status number;
  4      resp   long;
  5      answer_pipe varchar2(255) default replace(dbms_pipe.unique_session_name,"$","_");
  6  begin
  7      dbms_pipe.pack_message( answer_pipe );
  8      dbms_pipe.pack_message( cmd );
  9
 10      status := dbms_pipe.send_message( "HOST_PIPE" );
 11      if ( status <> 0 ) then
 12        raise_application_error( -20001, "Pipe error" );
 13      end if;
 14
 15      status := dbms_pipe.receive_message( answer_pipe );
 16      if ( status <> 0 ) then
 17        raise_application_error( -20001, "Pipe error" );
 18      end if;
 19      dbms_pipe.unpack_message( resp );
 20      dbms_output.put_line( substr( resp, 1, 80 ) );
 21  end;
 22  /

Use DBMS_PIPE to pack message and send message

SQL>
SQL> DECLARE
  2     pipe_status INTEGER;
  3     mypipe VARCHAR2(128) := DBMS_PIPE.UNIQUE_SESSION_NAME;
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE ("Write to " || mypipe);
  6     DBMS_PIPE.PACK_MESSAGE (SYSDATE);
  7     pipe_status := DBMS_PIPE.SEND_MESSAGE (mypipe);
  8  END;
  9  /
Write to ORA$PIPE$002004930001
PL/SQL procedure successfully completed.
SQL>