Solved

dbms_pipe in Oracle--Urgent

Posted on 2004-04-12
2
2,577 Views
Last Modified: 2007-12-19
Can someone please give me an example of dbms_pipe packeage in Oracle
0
Comment
Question by:pupil
2 Comments
 
LVL 14

Accepted Solution

by:
leoahmad earned 500 total points
ID: 10804376
first connect as sys.
install catproc.sql found in your oracle home then run these procedures in sql*plus in alternate sessions.

create or replace procedure send_message(v_message varchar2)
is
 s integer;
begin
dbms_pipe.pack_message(v_message);
s:=dbms_pipe.send_message('DEMO_PIPE');
if s<> 0 then
raise_application_error(-20201,'Error:  '||to_char(s)||'sending on pipe');
end if ;
end;
/


create or replace procedure receive_message
is
 s integer;
v_message varchar2(50);
begin
dbms_pipe.pack_message(v_message);
s:=dbms_pipe.receive_message('DEMO_PIPE');
if s<> 0 then
raise_application_error(-20201,'Error:  '||to_char(s)||'reading pipe');
end if ;
dbms_pipe.unpack_message(v_message);
dbms_output.put_line(v_message);
end;
/

Hope this Helps

LeoAhmad
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10804406
I found a great one from Oracle web site:

----------
  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(<pipename>)
        dbms_pipe.receive_message(<pipename>)
 
  which will cause a message to be sent or received.  You do
 
        dbms_pipe.pack_message(<varchar2>|<number>|<date>)
 
  to pack an item into a static buffer (which will then be sent with  
  the "send_message" call), and
 
        dbms_pipe.unpack_message(<varchar2>|<number>|<date>)
 
  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(<pipename>)
  and removed using  
    dbms_pipe.remove_pipe(<pipename>)
  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_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);
     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(reason);
         s := dbms_pipe.send_message(returnpipe);
         if s <> 0 then
           raise_application_error(-20000, 'Error:' || to_char(s) ||
             ' 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('stock_service');
         if s <> 0 then
           raise_application_error(-20000, 'Error:' || to_char(s) ||
             'reading pipe');
         end if;
         dbms_pipe.unpack_message(protocol_version);
         if protocol_version <> '1' then
           raise_application_error(-20000, 'Bad protocol: ' ||  
             protocol_version);
         end if;
         dbms_pipe.unpack_message(returnpipe);
         dbms_pipe.unpack_message(service);
         if service != 'getprice' then
           returnerror('Service ' || service || ' not supported');
         end if;
         dbms_pipe.unpack_message(symbol);
       end;
   
       procedure return_price(errormsg in varchar2, price in varchar2) is
         s integer;
       begin
         if errormsg is null then
           dbms_pipe.pack_message('SUCCESS');
           dbms_pipe.pack_message(price);
         else
           dbms_pipe.pack_message(errormsg);
         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;
       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');  -- protocol version
       dbms_pipe.pack_message(dbms_pipe.unique_session_name); -- return pipe
       dbms_pipe.pack_message('getprice');
       dbms_pipe.pack_message(symbol);
       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;
       dbms_pipe.unpack_message(errormsg);
       if errormsg <> 'SUCCESS' then
         raise_application_error(-20000, errormsg);
       end if;
       dbms_pipe.unpack_message(price);
       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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now