| General Information |
| Purpose |
Allow sessions to pass information between them through named SGA memory "pipes" |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmspipe.sql |
| First Available |
7.3.4 |
| Constants |
| Name |
Data Type |
Value |
| maxwait |
INTEGER |
86400000 |
|
| Dependencies |
| DBMS_ALERT |
DBMS_SNAPSHOT |
DRVTMT |
PBREAK |
| DBMS_DEBUG |
DBMS_SQLDIAG |
JVMRJBC |
PBRPH |
| DBMS_I_INDEX_UTL |
DBMS_SYS_ERROR |
MESSAGE_API |
WK_CRW |
| DBMS_JAVA |
|
|
|
|
| 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 |
|
| Security Model |
A public synonym is created for this package but execute is only granted explicitly to the EXECUTE_CATALOG_ROLE. |
| Subprograms |
|
| |
CREATE_PIPE (new 11.2.0.3 parameter)  |
| 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 Below |
Pack a message of DATE data type
Overload 3 |
dbms_pipe.pack_message(item IN DATE); |
| 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 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;
/ |
