Oracle PL/SQL Tutorial/System Packages/DBMS PIPE

DBMS_PIPE.CREATE_PIPE

   <source lang="sql">

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></source>

DBMS_PIPE.PACK_MESSAGE

   <source lang="sql">

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></source>

DBMS_PIPE.RECEIVE_MESSAGE

   <source lang="sql">

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.</source>

DBMS_PIPE.RECEIVE_MESSAGE with timeout

   <source lang="sql">

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></source>

DBMS_PIPE.REMOVE_PIPE

   <source lang="sql">

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></source>

DBMS_PIPE.RESET_BUFFER

   <source lang="sql">

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  /</source>
   
  

DBMS_PIPE.SEND_MESSAGE

   <source lang="sql">

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></source>

DBMS_PIPE.SEND_MESSAGE with timeout

   <source lang="sql">

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></source>

DBMS_PIPE.UNPACK_MESSAGE and record type

   <source lang="sql">

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  /</source>
   
  

Use dbms_pipe package

   <source lang="sql">

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  /</source>
   
  

Use DBMS_PIPE to pack message and send message

   <source lang="sql">

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></source>