Oracle DBMS_PIPE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Allow sessions to pass information between them through named SGA memory "pipes"
AUTHID DEFINER
Constants
Name Data Type Value
     
     
     
Data Types  
Dependencies
DBMS_ALERT DBMS_SQLDIAG MESSAGE_API
DBMS_DEBUG DBMS_SYS_ERROR PBREAK
DBMS_I_INDEX_UTL DRVTMT PBRPH
DBMS_JAVA JVMRJBC WK_CRW
DBMS_SNAPSHOT    
Documented Yes
Exceptions
Error Code Reason
ORA-06556 Generated if the buffer contains no more items, or if the item is not of the same type as that requested
ORA-06558 Generated if message buffer overflows (currently 4096 bytes). Each item in the buffer takes one byte for the type, two bytes for the length, plus the actual data. There is also one byte needed to terminate the message.
ORA-06559 Generated if the buffer contains no more items, or if the item is not of the same type as that requested
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmspipe.sql
Subprograms
 
CREATE_PIPE
Create a pipe dbms_pipe.create_pipe(
pipename    IN VARCHAR2,
maxpipesize IN INTEGER DEFAULT 8192,
private     IN BOOLEAN DEFAULT TRUE,
global      IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
desc v$db_pipes

SELECT * FROM v$db_pipes;

set serveroutput on

DECLARE
 retval INTEGER;
BEGIN
  retval := dbms_pipe.create_pipe('UWPIPE');
  dbms_output.put_line(retval);
END;
/

SELECT * FROM v$db_pipes;

DECLARE
 retval INTEGER;
BEGIN
  retval := dbms_pipe.remove_pipe('UWPIPE');
  dbms_output.put_line(retval);
END;
/

SELECT * FROM v$db_pipes;
 
NEXT_ITEM_TYPE
Returns the data type of the next item in the local message buffer

Value Description
0 No more items
6 NUMBER
9 VARCHAR2
11 ROWID
12 DATE
23 RAW
dbms_pipe.next_item_type RETURN INTEGER;
set serveroutput on

DECLARE
 retval NUMBER;
BEGIN
  retval := dbms_pipe.next_item_type;
  dbms_output.put_line(retval);
END;
/
 
PACK_MESSAGE
Pack a message of a string data type
Overload 1
dbms_pipe.pack_message(item IN VARCHAR2 CHARACTER SET ANY_CS);
See Demo Below
Pack a message of numeric data type
Overload 2
dbms_pipe.pack_message(item IN NUMBER);
See Demo Belowdbms_pipe.pack_message(item IN DATE);
Pack a message of DATE data type
Overload 3
 
See Demo Below
 
PACK_MESSAGE_RAW
Pack a message of RAW data type dbms_pipe.pack_message_raw(item IN RAW);
TBD
 
PACK_MESSAGE_ROWID
Pack a message of ROWID data type dbms_pipe.pack_message_raw(item IN ROWID);
TBD
 
PURGE
Empty the named pipe of all messages dbms_pipe.purge(pipename IN VARCHAR2);
exec dbms_pipe.purge('UWPIPE');
 
RECEIVE_MESSAGE
Receive a message from the named pipe and copy the message into the local message buffer dbms_pipe.receive_message(pipename INVARCHAR2, timeout IN INTEGER DEFAULT maxwait)
RETURN INTEGER;
See Demo Below
 
REMOVE_PIPE
Remove a pipe dbms_pipe.remove_pipe(pipename IN VARCHAR2) RETURN INTEGER;
See CREATE_PIPE Demo Above
 
RESET_BUFFER
Resets pack and unpack positioning indicators to 0. Generally this routine is not needed dbms_pipe.reset_buffer;
exec dbms_pipe.reset_buffer;
 
SEND_MESSAGE
Send a message on the named pipe. The message is contained in the local message buffer which was filled with calls to 'pack_message'. dbms_pipe.send_message(
pipename    IN VARCHAR2,
timeout     IN INTEGER DEFAULT maxwait,
maxpipesize IN INTEGER DEFAULT 8192)
RETURN INTEGER;
See Demo Below
 
UNIQUE_SESSION_NAME
Creates a name that is unique among all sessions currently connected to the database. Multiple calls to this routine from the same session will always return the same value. dbms_pipe.unique_session_name RETURN VARCHAR2;
DECLARE
 retval VARCHAR2(128);
BEGIN
  retval := dbms_pipe.unique_session_name;
  dbms_output.put_line(retval);
END;
/
 
UNPACK_MESSAGE
Unpack a message of a string data type
Overload 1
dbms_pipe.unpack_message((item OUT VARCHAR2 CHARACTER SET ANY_CS);
See Demo Below
Unpack a message of a numeric data type
Overload 2
dbms_pipe.unpack_message((item OUT NUMBER);
See Demo Below
Unpack a message of a DATE data type
Overload 3
dbms_pipe.unpack_message((item OUT DATE);
See Demo Below
 
UNPACK_MESSAGE_RAW
Unpack a message of RAW data type dbms_pipe.unpack_message_raw(item OUT RAW);
TBD
 
UNPACK_MESSAGE_ROWID
Unpack a message of ROWID data type dbms_pipe.unpack_message_rowid(item OUT ROWID);
TBD
 
DBMS_PIPE Demo
Demo Package posted to the OTN forums by Velu CREATE OR REPLACE PACKAGE message_api AUTHID DEFINER AS
 PROCEDURE send (p_number IN NUMBER, p_text IN VARCHAR2, p_date IN DATE DEFAULT SYSDATE);
 PROCEDURE receive;
END message_api;
/

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

Related Topics
DBMS_ALERT
DBMS_AQ
DBMS_AQADM
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved