Oracle | DBMS

Oracle DBMS_PIPE Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Allow sessions to pass information between them through named SGA memory "pipes"

AUTHID DEFINER

Constants

Name	Data Type	Value
maxwait	INTEGER	86400000 (1000 days)

Dependencies

DBMS_ALERT	DBMS_SNAPSHOT_KKXRCA	JVMRJBC
DBMS_CAPTURE_ADM_INTERNAL	DBMS_SYS_ERROR	JVMRJBCINV
DBMS_DEBUG	DRIXMD	PBREAK
DBMS_GSM_UTILITY	DRVTMT	PBRPH
DBMS_I_INDEX_UTL

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-20004	Credential <schema_name>.<credential_name>
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 CTXSYS and GSMADMIN_INTERNAL users, and the EXECUTE_CATALOG_ROLE, and SYSBACKUP roles.

Source {ORACLE_HOME}/rdbms/admin/dbmspipe.sql

Subprograms

CREATE_PIPE	PACK_MESSAGE_ROWID	SET_CREDENTIAL_NAME
GET_CREDENTIAL_NAME	PURGE	SET_LOCATION_URI
GET_LOCATION_URI	RECEIVE_MESSAGE	UNIQUE_SESSION_NAME
NEXT_ITEM_TYPE	REMOVE_PIPE	UNPACK_MESSAGE
PACK_MESSAGE	RESET_BUFFER	UNPACK_MESSAGE_RAW
PACK_MESSAGE_RAW	SEND_MESSAGE	UNPACK_MESSAGE_ROWID

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;

GET_CREDENTIAL_NAME (new 23ai)

Returns the default credential name set the Cloud backend dbms_pipe.get_credential_name RETURN VARCHAR2;

TBD

GET_LOCATION_URI (new 23ai)

Returns the default location URI set for the Cloud backend dbms_pipe.get_location_uri RETURN VARCHAR2;

TBD

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 DBMS_PIPE at page bottom

Pack a message of numeric data type
Overload 2 dbms_pipe.pack_message(item IN NUMBER);

See DBMS_PIPE at page bottom

Pack a message of DATE data type
Overload 3 dbms_pipe.pack_message(item IN DATE);

See DBMS_PIPE at page bottom

PACK_MESSAGE_RAW

Pack a message of RAW data type dbms_pipe.pack_message_raw(item IN RAW);

See DBMS_PIPE at page bottom

PACK_MESSAGE_ROWID

Pack a message of ROWID data type dbms_pipe.pack_message_raw(item IN ROWID);

See DBMS_PIPE at page bottom

PURGE (2 new 23ai overloads)

Empty the named pipe of all messages

Overload 1 dbms_pipe.purge(pipename IN VARCHAR2);

exec dbms_pipe.purge('UWPIPE');

Overload 2 dbms_pipe.purge( pipename IN VARCHAR2, credential_name IN VARCHAR2 DEFAULT NULL, location_uri IN VARCHAR2);

TBD

Overload 3 dbms_pipe.purge( pipename IN VARCHAR2, directory_name IN VARCHAR2);

TBD

RECEIVE_MESSAGE (2 new 23ai overloads)

Receive a message from the named pipe and copy the message into the local message buffer

Overload 1 dbms_pipe.receive_message( pipename IN VARCHAR2, timeout IN INTEGER DEFAULT maxwait, cache_func IN VARCHAR2 DEFAULT NULL) RETURN INTEGER;

See DBMS_PIPE at page bottom

Overload 2 dbms_pipe.receive_message( pipename IN VARCHAR2, timeout IN INTEGER DEFAULT maxwait, credential_name IN VARCHAR2 DEFAULT NULL, location_uri IN VARCHAR2) RETURN INTEGER;

TBD

Overload 3 dbms_pipe.receive_message( pipename IN VARCHAR2, timeout IN INTEGER DEFAULT maxwait, directory_name IN VARCHAR2) RETURN INTEGER;

TBD

REMOVE_PIPE

Remove a pipe dbms_pipe.remove_pipe(pipename IN VARCHAR2) RETURN INTEGER;

See DBMS_PIPE at page 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 (2 new 23ai overloads)

Send a message on the named pipe. The message is contained in the local message buffer which was filled with calls to 'pack_message'.

Overload 1 dbms_pipe.send_message( pipename IN VARCHAR2, timeout IN INTEGER DEFAULT maxwait, maxpipesize IN INTEGER DEFAULT 65536, singleton IN BOOLEAN DEFAULT FALSE, shelflife IN INTEGER DEFAULT 0) RETURN INTEGER;

See demo below

Overload 2 dbms_pipe.send_message( pipename IN VARCHAR2, timeout IN INTEGER DEFAULT maxwait, credential_name IN VARCHAR2 DEFAULT NULL, location_uri IN VARCHAR2) RETURN INTEGER;

TBD

Overload 3 dbms_pipe.send_message( pipename IN VARCHAR2, timeout IN INTEGER DEFAULT maxwait, directory_name IN VARCHAR2) RETURN INTEGER;

TBD

SET_CREDENTIAL_NAME (new 23ai)

Sets the credential name that will be used for a Cloud backend dbms_pipe.set_credential_name(credential_name IN VARCHAR2);

TBD

SET_LOCATION_URI (new 23ai)

Sets the location URI that will be used by default for a Cloud backend dbms_pipe.set_location_uri(location_uri IN VARCHAR2);

TBD

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

See DBMS_PIPE at page bottom

UNPACK_MESSAGE_ROWID

Unpack a message of ROWID data type dbms_pipe.unpack_message_rowid(item OUT ROWID);

See DBMS_PIPE at page bottom

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