Re: Using DBMS_PIPE in Visual Basic or Java on Windows NT

On Fri, 15 Oct 1999 23:23:19 +0800, "Aik Khoon" <panadol_at_singnet.com.sg> wrote:

>Hi,
>
>I'm confused about how to use DBMS_PIPE package with regard to my problem.
>Any feedback if greatly appreciated.
>
>I am thinking of writing a trigger to send some message through this
>DBMS_PIPE (if possible)
>and then some application (either VB or Java) that is listening to this pipe
>will be informed and
>maybe start an executable or some OS events.
>
>Did I misinterpret the use of DBMS_PIPE here?
>Can someone shows a simple example on this scenario on a window nt
>environment?
>
 I do this from Perl but the same principle applies. Create a PL/SQL procedure called PipeReceive that basically does:

PROCEDURE PipeReceive(nCommand OUT NUMBER) IS
...

    rv := dbms_pipe.receive_message (PIPENAME, nTimeout);     IF rv = 0 THEN

        dbms_pipe.unpack_message(nCommand); ...

 where PIPENAME is a constant name of the pipe. The "nCommand" parameter is used to return the command value sent through the pipe. You could use a function return value instead if your language supports it.

 Call this procedure from an anonymous block of code e.g. BEGIN PipeReceive(nCommand); END;

 The sending procedure or trigger contains code like this:

PROCEDURE PipeSend(nCommand IN NUMBER, nTimeout IN NUMBER) ...

        dbms_pipe.pack_message(nCommand);
        rv := dbms_pipe.send_message (PIPENAME, nTimeout);

 Your Visual Basic or Java program calls PipeReceive and waits there until something comes through the pipe or the receive_message call times out.

 Some caveats: If your VB procedure needs to use other data from the database bear in mind that it may be called before the calling process has committed changed values, especially if it's being called from a trigger (and you can't COMMIT from a trigger). There are limits on the amount of data that can be sent through DBMS_PIPE, see the DBMS_PIPE package header for the limits.

Dave Received on Sat Oct 16 1999 - 22:26:14 CEST