We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


dbms_pipe in Oracle--Urgent

pupil asked
Medium Priority
Last Modified: 2007-12-19
Can someone please give me an example of dbms_pipe packeage in Oracle
Watch Question

Database Developer
Unlock this solution and get a sample of our free trial.
(No credit card required)
I found a great one from Oracle web site:

  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
  which will cause a message to be sent or received.  You do
  to pack an item into a static buffer (which will then be sent with  
  the "send_message" call), and
  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
  and removed using  
  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  
  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 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.
  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
    <loop forever>
          begin dbms_stock_server.get_request(:stocksymbol); end;
          <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_price(:error, :price); end;
  A client would do:
        begin :price := stock_request('YOURCOMPANY'); end;
  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);
     create  or replace package body dbms_stock_server is
       returnpipe      varchar2(30);
       procedure returnerror(reason varchar2) is
         s integer;
         s := dbms_pipe.send_message(returnpipe);
         if s <> 0 then
           raise_application_error(-20000, 'Error:' || to_char(s) ||
             ' sending on pipe');
         end if;
       procedure get_request(symbol out varchar2) is
         protocol_version varchar2(10);
         s                   integer;
         service             varchar2(30);
         s := dbms_pipe.receive_message('stock_service');
         if s <> 0 then
           raise_application_error(-20000, 'Error:' || to_char(s) ||
             'reading pipe');
         end if;
         if protocol_version <> '1' then
           raise_application_error(-20000, 'Bad protocol: ' ||  
         end if;
         if service != 'getprice' then
           returnerror('Service ' || service || ' not supported');
         end if;
       procedure return_price(errormsg in varchar2, price in varchar2) is
         s integer;
         if errormsg is null then
         end if;
         s := dbms_pipe.send_message(returnpipe);
         if s <> 0 then
           raise_application_error(-20000, 'Error:'||to_char(s)||
             ' sending on pipe');
         end if;
   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);
       dbms_pipe.pack_message('1');  -- protocol version
       dbms_pipe.pack_message(dbms_pipe.unique_session_name); -- return pipe
       s := dbms_pipe.send_message('stock_service');
       if s <> 0 then
         raise_application_error(-20000, 'Error:'||to_char(s)||
           ' sending on pipe');
       end if;
       s := dbms_pipe.receive_message(dbms_pipe.unique_session_name);
       if s <> 0 then
         raise_application_error(-20000, 'Error:'||to_char(s)||
           ' receiving on pipe');
       end if;
       if errormsg <> 'SUCCESS' then
         raise_application_error(-20000, errormsg);
       end if;
       return price;
   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.
  maxwait   constant integer := 86400000; /* 1000 days */  
  This is the maximum time to wait attempting to send or receive a message

Hope this helps
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.