Re: How to interact with PL/SQL

From: Gert Rijs <gem_at_wirehub.nl>
Date: 1997/03/07
Message-ID: <01bc2b2b$7db91180$d5105d8d@gertrijs>#1/1

Dieter Oberkofler <dtr_at_leadingbits.via.at> wrote in article <3320e194.2547823_at_news.via.at>...
> Hi to all,
>
> I'm searching for a solution to a common problem that appears when
> developing GUI applications that use extensively PL/SQL code.
>
> Until now I developed crucial and very data intensive parts of my
> application in PL/SQL and just called the appropriate procedures or
> functions according to the user requests in my GUI based application.
> The PL/SQL code I executed was never to long and returned to the
> caller in just a few seconds.
> When the PL/SQL code gets bigger and more time consuming it starts to
> be very unpleasant for the user just to wait until the PL/SQL code
> returns to the caller and the operation has been performed without any
> progress information of what's going on in the PL/SQL code.
>
> I'm currently searching for "the best" solution to keep some
> interaction alive between the application (developed in C++ and only
> using OO-wrapped OCI calls) and the PL/SQL code and figured out that
> there aren't so many possible solutions and that I have problems in
> implementing them. Here the possible solution I found until now:
>
> (1) Use DBMS_OUTPUT
> -------------------
> How can I retrieve the text generated by DBMS_OUTPUT in a OCI based
> application?

you can use oci to get the output:
create a cursor (cda) for the statement

   "begin dbms_output.get_line(:line, :rv); end;" bind a char [255] to :line and a short to :rv. keep executing this cursor until :rv (your short) is non-zero.

> How can I force DBMS_OUTPUT not to send all the lines after ending the
> procedure (this is what happens in SQL*Plus)?
>

I don't think you can... (only when your procedure is done)

> (2) Use DBMS_PIPE
> -----------------
> Seems to be hard to implement and there are no examples supplied by
> Oracle who to do this in OCI.
>

it's not hard using oci
before executing your pl/sql create the pipe: (i believe this is optional)
declare

   nr number;
begin

   nr := dbms_pipe.create_pipe("mypipe", 4096, FALSE); end;

now execute your pl/sql (in a seperate thread or using non-blocking calls (don't know how, from wich oci version)).

the receiving pl/sql you could use in your oci program: declare

   rc number;
   buf varchar2(2000);
begin

   rc := dbms_pipe.receive_message("mypipe", 0);    if rc <> 0 then

      return; /* pipe is empty */
   end if;
   while true loop

      dbms_pipe.unpack_message(buf);
         /* bind :proctext to
            a host variable */
      :proctext := :proctext || buf;

   end loop;
   exception when others then

     if sqlcode = -6556 then
        null; /* end of data for this message */
     else
        raise; /* something weird happened */
     end if;

end;

when your pl/sql returns to your program remove the pipe: declare

   nr number;
begin

   nr := dbms_pipe.remove_pipe("mypipe"); end;

> (3) Use native pipe to communicate between the server and the client
> --------------------------------------------------------------------
> This is operating system dependent and therefore not to advice.
>
> If someone else has the same problem and want's to share his
> information or if someone has "the solution" for this kind of problem
> please let me know.
>
Received on Fri Mar 07 1997 - 00:00:00 CST