Depending upon your security requirements, you may choose to use either a public pipe or a private pipe.
Attention: Pipes are independent of transactions. Be careful using pipes when transaction control can be affected.
![[*]](jump.gif)
for information on granting the necessary privileges to users who will be executing this package.
You can create an explicit public pipe by calling the CREATE_PIPE function with the PRIVATE flag set to FALSE. You must deallocate explicitly-created pipes by calling the REMOVE_PIPE function.
The domain of a public pipe is the schema in which it was created, either explicitly or implicitly.
Any schema user with the appropriate privileges and knowledge can read information from a public pipe. However, once buffered information is read by one user, it is emptied from the buffer, and is not available for other readers of the same pipe.
The sending session builds a message using one or more calls to the PACK_MESSAGE procedure. This procedure adds the message to the session's local message buffer. The information in this buffer is sent by calling the SEND_MESSAGE procedure, designating the pipe name to be used to send the message. When SEND_MESSAGE is called, all messages that have been stacked in the local buffer are sent.
A process that wants to receive a message calls the RECEIVE_MESSAGE procedure, designating the pipe name from which to receive the message. The process then calls the UNPACK_MESSAGE procedure to access each of the items in the message.
You cannot create a private pipe if an implicit pipe exists in memory and has the same name as the private pipe you are trying to create. In this case CREATE_PIPE returns an error.
Access to a private pipe is restricted to the following:
As with public pipes, you must first build your message using calls to PACK_MESSAGE before calling SEND_MESSAGE. Similarly you must call RECEIVE_MESSAGE to retrieve the message before accessing the items in the message by calling UNPACK_MESSAGE.
ORA-23321: Pipename may not be null
ORA-23322: Insufficient privilege to access pipe
ORA-23321 can be returned by CREATE_PIPE, or any subprogram that takes a pipe name as a parameter. ORA-23322 can be returned by any subprogram that references a private pipe in its parameter list.
Warning: Do not use a pipe name beginning with ORA$; these names are reserved for use by Oracle Corporation.
DBMS_PIPE.CREATE_PIPE(pipename IN VARCHAR2,
maxpipesize IN INTEGER DEFAULT 8192,
private IN BOOLEAN DEFAULT TRUE)
RETURN INTEGER;
The PACK_MESSAGE procedure is overloaded to accept items of type VARCHAR2, NUMBER, or DATE. In addition to the data bytes, each item in the buffer requires one byte to indicate its type, and two bytes to store its length. One additional byte is needed to terminate the message. If the message buffer exceeds 4096 bytes, Oracle raises exception ORA-6558.
When you call SEND_MESSAGE to send this message, you must indicate the name of the pipe on which you want to send the message. If this pipe already exists, you must have sufficient privileges to access this pipe. If the pipe does not already exist, it is created automatically.
Warning: Do not use a pipe name beginning with ORA$; these names are reserved for use by Oracle Corporation.
DBMS_PIPE.PACK_MESSAGE (item IN VARCHAR2);
DBMS_PIPE.PACK_MESSAGE (item IN NUMBER);
DBMS_PIPE.PACK_MESSAGE (item IN DATE);
DBMS_PIPE.PACK_MESSAGE_RAW (item IN RAW);
DBMS_PIPE.PACK_MESSAGE_ROWID (item IN ROWID);
DBMS_PIPE.SEND_MESSAGE(pipename IN VARCHAR2,
timeout IN INTEGER DEFAULT MAXWAIT
maxpipesize IN INTEGER DEFAULT 8192)
RETURN INTEGER;
If the pipe that you specify when you call RECEIVE_MESSAGE does not already exist, Oracle implicitly creates the pipe and then waits to receive the message. If the message does not arrive within a designated timeout interval, the call returns and the pipe is removed.
After receiving the message, you must make one or more calls to UNPACK_MESSAGE to access the individual items in the message. The UNPACK_MESSAGE procedure is overloaded to unpack items of type DATE, NUMBER, VARCHAR2, and there are two additional procedures to unpack RAW and ROWID items. If you do not know the type of data that you are attempting to unpack, you can call NEXT_ITEM_TYPE to determine the type of the next item in the buffer.
DBMS_PIPE.RECEIVE_MESSAGE(pipename IN VARCHAR2,
timeout IN INTEGER
DEFAULT maxwait)
RETURN INTEGER;
DBMS_PIPE.NEXT_ITEM_TYPE RETURN INTEGER;
| Return Value | Description |
| 0 | no more items |
| 6 | NUMBER |
| 9 | VARCHAR2 |
| 12 | DATE |
DBMS_PIPE.UNPACK_MESSAGE (item OUT VARCHAR2);
DBMS_PIPE.UNPACK_MESSAGE (item OUT NUMBER);
DBMS_PIPE.UNPACK_MESSAGE (item OUT DATE);
DBMS_PIPE.UNPACK_MESSAGE_RAW (item IN DATE);
DBMS_PIPE.UNPACK_MESSAGE_ROWID (item IN DATE);
If the message buffer contains no more items, or if the item received is not of the same type as that requested, the ORA-2000 exception is raised.
Pipes created explicitly by CREATE_PIPE are removed only by calling REMOVE_PIPE or when the instance is shut down. All unconsumed records in the pipe are removed before the pipe is deleted. This is similar to calling PURGE on an implicitly created pipe.
DBMS_PIPE.REMOVE_PIPE(pipename IN VARCHAR2) RETURN INTEGER;
Because PURGE calls RECEIVE_MESSAGE, the local buffer might be overwritten with messages as they are purged from the pipe. Also, you can receive an ORA-23322, insufficient privileges, error if you attempt to purge a pipe to which you have insufficient access rights.
DBMS_PIPE.PURGE(pipename IN VARCHAR2);
DBMS_PIPE.RESET_BUFFER;
DBMS_PIPE.UNIQUE_SESSION_NAME RETURN VARCHAR2;
CREATE OR REPLACE PROCEDURE debug (msg VARCHAR2) AS
status NUMBER;
BEGIN
dbms_pipe.pack_message(LENGTH(msg));
dbms_pipe.pack_message(msg);
status := dbms_pipe.send_message('plsql_debug');
IF status != 0 THEN
raise_application_error(-20099, 'Debug error');
END IF;
END debug;This example shows the Pro*C code that receives messages from the PLSQL_DEBUG pipe in the PL/SQL example above, and displays the messages. If the Pro*C session is run in a separate window, it can be used to display any messages that are sent to the debug procedure from a PL/SQL program executing in a separate session.
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
int status;
int msg_length;
char retval[2000];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void sql_error();
main()
{
/* prepare username */
strcpy(username.arr, "SCOTT/TIGER");
username.len = strlen(username.arr);
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username;
printf("connected\n");
/* start an endless loop to look for and print
messages on the pipe */
for (;;)
{
EXEC SQL EXECUTE
DECLARE
len INTEGER;
typ INTEGER;
sta INTEGER;
chr VARCHAR2(2000);
BEGIN
chr := '';
sta := dbms_pipe.receive_message('plsql_debug');
IF sta = 0 THEN
dbms_pipe.unpack_message(len);
dbms_pipe.unpack_message(chr);
END IF;
:status := sta;
:retval := chr;
IF len IS NOT NULL THEN
:msg_length := len;
ELSE
:msg_length := 2000;
END IF;
END;
END-EXEC;
if (status == 0)
printf("\n%.*s\n", msg_length, retval);
else
printf("abnormal status, value is %d\n", status);
}
}
void sql_error()
{
char msg[1024];
int rlen, len;
len = sizeof(msg);
sqlglm(msg, &len, &rlen);
printf("ORACLE ERROR\n");
printf("%.*s\n", rlen, msg);
exit(1);
}
The Pro*C program just sleeps, waiting for a message to arrive on the named pipe. When a message arrives, the C program processes it, carrying out the required action, such as executing a UNIX command through the system() call, or executing a SQL command using embedded SQL.
DAEMON.SQL is the source code for the PL/SQL package. This package contains procedures that use the DBMS_PIPE package to send and receive message to and from the Pro*C daemon. Note that full handshaking is used. The daemon will always send a message back to the package (except in the case of the 'STOP' command). This is valuable, since it allows the PL/SQL procedures to be sure that the Pro*C daemon is running.
You can call the DAEMON packaged procedures from an anonymous PL/SQL block using SQL*Plus or Server Manager. For example:
SVRMGR> variable rv number
SVRMGR> execute :rv := DAEMON.EXECUTE_SYSTEM('ls -la');would, on a UNIX system, cause the Pro*C daemon to execute the command system("ls -la").
Remember that the daemon needs to be running first. So you might want to run it in the background, or in another window beside the SQL*Plus or Server Manager session from which you call it.
The DAEMON.SQL also uses the DBMS_OUTPUT package (see ) to display the results. For this example to work, you must have execute privileges on this package.
CREATE OR REPLACE PACKAGE daemon AS
FUNCTION execute_sql(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER;
FUNCTION execute_system(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER;
PROCEDURE stop(timeout NUMBER DEFAULT 10);
END daemon;
/
CREATE OR REPLACE PACKAGE BODY daemon AS
FUNCTION execute_system(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER IS
status NUMBER;
result VARCHAR2(20);
command_code NUMBER;
pipe_name VARCHAR2(30);
BEGIN
pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;
DBMS_PIPE.PACK_MESSAGE('SYSTEM');
DBMS_PIPE.PACK_MESSAGE(pipe_name);
DBMS_PIPE.PACK_MESSAGE(command);
status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20010,
'Execute_system: Error while sending. Status = ' ||
status);
END IF;
status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20011,
'Execute_system: Error while receiving.
Status = ' || status);
END IF;
DBMS_PIPE.UNPACK_MESSAGE(result);
IF result <> 'done' THEN
RAISE_APPLICATION_ERROR(-20012,
'Execute_system: Done not received.');
END IF;
DBMS_PIPE.UNPACK_MESSAGE(command_code);
DBMS_OUTPUT.PUT_LINE('System command executed. result = ' ||
command_code);
RETURN command_code;
END execute_system;
FUNCTION execute_sql(command VARCHAR2,
timeout NUMBER DEFAULT 10)
RETURN NUMBER IS
status NUMBER;
result VARCHAR2(20);
command_code NUMBER;
pipe_name VARCHAR2(30);
BEGIN
pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;
DBMS_PIPE.PACK_MESSAGE('SQL');
DBMS_PIPE.PACK_MESSAGE(pipe_name);
DBMS_PIPE.PACK_MESSAGE(command);
status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20020,
'Execute_sql: Error while sending. Status = ' || status);
END IF;
status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20021,
'execute_sql: Error while receiving.
Status = ' || status);
END IF;
DBMS_PIPE.UNPACK_MESSAGE(result);
IF result <> 'done' THEN
RAISE_APPLICATION_ERROR(-20022,
'execute_sql: done not received.');
END IF;
DBMS_PIPE.UNPACK_MESSAGE(command_code);
DBMS_OUTPUT.PUT_LINE
('SQL command executed. sqlcode = ' || command_code);
RETURN command_code;
END execute_sql;
PROCEDURE stop(timeout NUMBER DEFAULT 10) IS
status NUMBER;
BEGIN
DBMS_PIPE.PACK_MESSAGE('STOP');
status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
IF status <> 0 THEN
RAISE_APPLICATION_ERROR(-20030,
'stop: error while sending. status = ' || status);
END IF;
END stop;
END daemon;
proc iname=daemon userid=scott/tiger sqlcheck=semantics
Then C-compile and link in the normal way.
#include <stdio.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char *uid = "scott/tiger";
int status;
VARCHAR command[20];
VARCHAR value[2000];
VARCHAR return_name[30];
EXEC SQL END DECLARE SECTION;
void
connect_error()
{
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL WHENEVER SQLERROR CONTINUE;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while connecting:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon quitting.\n");
exit(1);
}
void
sql_error()
{
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL WHENEVER SQLERROR CONTINUE;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while executing:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon continuing.\n");
}
main()
{
EXEC SQL WHENEVER SQLERROR DO connect_error();
EXEC SQL CONNECT :uid;
printf("Daemon connected.\n");
EXEC SQL WHENEVER SQLERROR DO sql_error();
printf("Daemon waiting...\n");
while (1) {
EXEC SQL EXECUTE
BEGIN
:status := DBMS_PIPE.RECEIVE_MESSAGE('daemon');
IF :status = 0 THEN
DBMS_PIPE.UNPACK_MESSAGE(:command);
END IF;
END;
END-EXEC;
if (status == 0)
{
command.arr[command.len] = '\0';
if (!strcmp((char *) command.arr, "STOP"))
{
printf("Daemon exiting.\n");
break;
}
else if (!strcmp((char *) command.arr, "SYSTEM"))
{
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.UNPACK_MESSAGE(:return_name);
DBMS_PIPE.UNPACK_MESSAGE(:value);
END;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute system command '%s'\n", value.arr);
status = system(value.arr);
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.PACK_MESSAGE('done');
DBMS_PIPE.PACK_MESSAGE(:status);
:status := DBMS_PIPE.SEND_MESSAGE(:return_name);
END;
END-EXEC;
if (status)
{
printf
("Daemon error while responding to system command.");
printf(" status: %d\n", status);
}
}
else if (!strcmp((char *) command.arr, "SQL")) {
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.UNPACK_MESSAGE(:return_name);
DBMS_PIPE.UNPACK_MESSAGE(:value);
END;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute sql command '%s'\n", value.arr);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL EXECUTE IMMEDIATE :value;
status = sqlca.sqlcode;
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL EXECUTE
BEGIN
DBMS_PIPE.PACK_MESSAGE('done');
DBMS_PIPE.PACK_MESSAGE(:status);
:status := DBMS_PIPE.SEND_MESSAGE(:return_name);
END;
END-EXEC;
if (status)
{
printf("Daemon error while responding to sql command.");
printf(" status: %d\n", status);
}
}
else
{
printf
("Daemon error: invalid command '%s' received.\n",
command.arr);
}
}
else
{
printf("Daemon error while waiting for signal.");
printf(" status = %d\n", status);
}
}
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}