pupil
asked on
dbms_pipe in Oracle--Urgent
Can someone please give me an example of dbms_pipe packeage in Oracle
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
----------
OVERVIEW
----------
This package provides a DBMS "pipe" service which allows messages
to be sent between sessions.
The metaphor is similar to UNIX pipes: you can do
dbms_pipe.send_message(<pi
dbms_pipe.receive_message(
which will cause a message to be sent or received. You do
dbms_pipe.pack_message(<va
to pack an item into a static buffer (which will then be sent with
the "send_message" call), and
dbms_pipe.unpack_message(<
to get an item out of the static buffer (which is filled by the
"receive_message" call).
Pipes can be private to a user-id - which only allows session connected
under the same user-id or stored procedure owned by the user-id to read
write to the pipe. Pipes could be public - and all database users with
execute privilege on dbms_pipe and knowledge of the pipe can read or
write to the pipe.
Pipes operate independently of transactions. They also operate
asynchronously. There can be multiple readers and writers of the
same pipe.
Pipes only operate between sessions in the same instance.
Pipes can be explicitly created using
dbms_pipe.create_pipe(<pip
and removed using
dbms_pipe.remove_pipe(<pip
A pipe created using the explicit create command should be removed
using the remove function. A pipe can also be created implicitly.
Pipes automatically come into existence the first time they are
referenced. They effectively disappear when they contain no more
data (some overhead remains in the SGA until it gets aged out).
Pipes take up space in the SGA (see "maxpipesize" parameter to
"send_message").
------
USES
------
The pipe functionality has several potential applications:
o External service interface. You can provide the ability to
communicate with (user-written) services that are external to the
RDBMS. This can be done in a (effectively) multi-threaded manner
so that several instances of the service can be executing
simultaneously. Additionally, the services are available
asynchronously - the requestor of the service need not block
awaiting a reply. The requestor can check (with or without
timeout) at a later time. The service can be written in any
of the 3GL languages that ORACLE supports, not just C. See
example below.
o Independent transactions. The pipe can be used to communicate
to a separate session which can perform an operation in an
independent transaction (such as logging an attempted security
violation detected by a trigger).
o Alerters (non-transactional). You can post another process
without requiring the waiting process to poll. If an "after-row"
or "after-statement" trigger were to alert an application, then
the application would treat this alert as an indication that
the data probably changed. The application would then go read
the data to get the current value. Since this is an "after"
trigger, the application would want to do a "select for update"
to make sure it read the correct data.
o Debugging. Triggers and/or stored procedures can send debugging
information to a pipe. Another session can keep reading out
of the pipe and displaying it on the screen or writing it
out to a file.
o Concentrator. Useful for multiplexing large numbers of users
over a fewer number of network connections, or improving
performance by concentrating several user-transactions into
one dbms-transaction.
----------
SECURITY
----------
Security can be achieved by use of 'grant execute' on the dbms_pipe
package, by creating a pipe using the 'private' parameter in the create
function and by writing cover packages that only expose particular
features or pipenames to particular users or roles.
----------
EXAMPLES
----------
External service interface
--------------------------
Put the user-written 3GL code into an OCI or Precompiler program.
The program connects to the database and executes PL/SQL code to read
its request from the pipe, computes the result, and then executes
PL/SQL code to send the result on a pipe back to the requestor.
Below is an example of a stock service request.
The recommended sequence for the arguments to pass on the pipe
for all service requests is
protocol_version varchar2 - '1', 10 bytes or less
returnpipe varchar2 - 30 bytes or less
service varchar2 - 30 bytes or less
arg1 varchar2/number/date
...
argn varchar2/number/date
The recommended format for returning the result is
success varchar2 - 'SUCCESS' if OK,
otherwise error message
arg1 varchar2/number/date
...
argn varchar2/number/date
The "stock price request server" would do, using OCI or PRO* (in
pseudo-code):
<loop forever>
begin dbms_stock_server.get_requ
<figure out price based on stocksymbol (probably from some radio
signal), set error if can't find such a stock>
begin dbms_stock_server.return_p
A client would do:
begin :price := stock_request('YOURCOMPANY
The stored procedure, dbms_stock_server, which is called by the
"stock price request server" above is:
create or replace package dbms_stock_server is
procedure get_request(symbol out varchar2);
procedure return_price(errormsg in varchar2, price in varchar2);
end;
create or replace package body dbms_stock_server is
returnpipe varchar2(30);
procedure returnerror(reason varchar2) is
s integer;
begin
dbms_pipe.pack_message(rea
s := dbms_pipe.send_message(ret
if s <> 0 then
raise_application_error(-2
' sending on pipe');
end if;
end;
procedure get_request(symbol out varchar2) is
protocol_version varchar2(10);
s integer;
service varchar2(30);
begin
s := dbms_pipe.receive_message(
if s <> 0 then
raise_application_error(-2
'reading pipe');
end if;
dbms_pipe.unpack_message(p
if protocol_version <> '1' then
raise_application_error(-2
protocol_version);
end if;
dbms_pipe.unpack_message(r
dbms_pipe.unpack_message(s
if service != 'getprice' then
returnerror('Service ' || service || ' not supported');
end if;
dbms_pipe.unpack_message(s
end;
procedure return_price(errormsg in varchar2, price in varchar2) is
s integer;
begin
if errormsg is null then
dbms_pipe.pack_message('SU
dbms_pipe.pack_message(pri
else
dbms_pipe.pack_message(err
end if;
s := dbms_pipe.send_message(ret
if s <> 0 then
raise_application_error(-2
' sending on pipe');
end if;
end;
end;
The procedure called by the client is:
create or replace function stock_request (symbol varchar2)
return varchar2 is
s integer;
price varchar2(20);
errormsg varchar2(512);
begin
dbms_pipe.pack_message('1'
dbms_pipe.pack_message(dbm
dbms_pipe.pack_message('ge
dbms_pipe.pack_message(sym
s := dbms_pipe.send_message('st
if s <> 0 then
raise_application_error(-2
' sending on pipe');
end if;
s := dbms_pipe.receive_message(
if s <> 0 then
raise_application_error(-2
' receiving on pipe');
end if;
dbms_pipe.unpack_message(e
if errormsg <> 'SUCCESS' then
raise_application_error(-2
end if;
dbms_pipe.unpack_message(p
return price;
end;
You would typically only grant execute on 'dbms_stock_service' to
the stock service application server, and would only grant execute
on 'stock_request' to those users allowed to use the service.
---------------------
SPECIAL CONSTANTS
---------------------
maxwait constant integer := 86400000; /* 1000 days */
This is the maximum time to wait attempting to send or receive a message
Hope this helps