Re: How to interact with PL/SQL
Date: 1997/03/07
Message-ID: <01bc2b2b$7db91180$d5105d8d_at_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 CET