Document-ID:        17329.1
Subject:            (V4/45) EXAMPLES OF USING DBMS PIPES IN FORMS 40 AND SQLPLUS
Author:             ILAHIRI
Last Modified Date: 09 February  1995


 

EXAMPLES OF USING DBMS PIPES IN FORMS 40 AND SQLPLUS
====================================================

The DBMSPIPE.SQL package allows sessions to communicate amongst each other in
the SAME instance. A pipe is automatically started when referenced for the
first time and it disappears when it no longer contains data. Now, there is
some space usage overhead because the pipe resides in the SHARED GLOBAL AREA.
This space is recollected once the pipe is taken out of the SGA through the
Least Recently Used (LRU) algorithm.

In general, build a message using the DBMS_PIPE.PACK_MESSAGE procedure.
Then send this information that you have already packed in the buffer by using
DBMS_PIPE.SEND_MESSAGE procedure which really defines the name of the PIPE
used to send THE MESSAGE.
On the receiving end, DBMS_PIPE.RECEIVE_MESSAGE procedure needs to be invoked
and then finally DBMS_PIPE.UNPACK_MESSAGE is used to access each part of
the message.

This will be clear with the help of the following examples. EXAMPLE I is based
on SQL*PLUS and EXAMPLE II is based on two forms --  a query criteria is
entered in the calling form and then the data is queried from the called form.
The information is passed from the called to the calling form not through
passing parameters or passing GLOBAls but thru DATABASE PIPES.

STEP I: CREATION OF PROCEDURES sendMessage and sendMessage
~~~~~~
create or replace procedure sendMessage(info VARCHAR2) is
s number;
begin
  DBMS_PIPE.PACK_MESSAGE(info);
  s := DBMS_PIPE.SEND_MESSAGE('pipe_name') /* You can name it anything */
end;
/

create or replace function  receiveMessage  RETURN VARCHAR2 IS
n number;
chr VARCHAR2(200);
begin
  n := DBMS_PIPE.RECEIVE_MESSAGE('pipe_name');
  DBMS_PIPE.UNPACK_MESSAGE(chr);
  return(chr);
end;
/

EXAMPLE I: PASSING THE DATA VIA DBMS PIPE IN SQL*PLUS
~~~~~~~~~
SQL> execute sendMessage('Z');

PL/SQL procedure successfully completed.

SQL> variable a varchar2(2000);
SQL> execute :a := receiveMessage;

PL/SQL procedure successfully completed.

SQL> print a;

A
------------------------------------------------------------------------------
Z

SQL> execute sendMessage('ZZZ ZZZ');

PL/SQL procedure successfully completed.

SQL> execute :a := receiveMessage;

PL/SQL procedure successfully completed.

SQL> print a;

A
------------------------------------------------------------------------------
ZZZ ZZZ

EXAMPLE II: PASSING A QUERY CRITERIA FROM ONE FORM AND QUERYING THE DATA FROM
~~~~~~~~~~
            ANOTHER FORM BY DATABASE PIPES

CALLING FORM:
============
1. Create a form based on a default block on the demo DEPT table.
2. Create a button called 'PASS DATA' and then attach a WHEN-BUTTON-PRESSED
   trigger to the button, using the following code:

   DECLARE
      x  VARCHAR2(2000);
   BEGIN

      x := :DEPT.DNAME;
   DBMS_PIPE.PURGE('pipe_name'); /*Purge the data in the pipe if there is one
                                 */
   SendMessage(x);
   CALL_FORM('CALLED_FORM');
   END;

CALLED FORM:  Also based on the demo DEPT table:
============
1. PRE-QUERY TRIGGER
   BEGIN
        :DEPT.DNAME := ReceiveMessage;
   END;

2. WHEN-NEW-FORM-INSTANCE TRIGGER
   execute_query;

REFERENCES: ORACLE 7 SERVER APPLICATION DEVELOPER'S GUIDE APPX A pp A-20 - A-25
~~~~~~~~~~