Re: PL/SQL output/wrappers ...
Date: 1998/01/14
Message-ID: <34c93735.209634498_at_inet16>#1/1
On Wed, 14 Jan 1998 10:39:31 +0800, Trevor Phillips <phillips_at_central.murdoch.edu.au> wrote:
>Hi!
>
>Does anyone know a way to capture output from a PL/SQL procedure?? (Pref. a
>simple way).
>
>I'm writing something which will interface to PL/SQL written for the Oracle
>Web Server, and can't seem to figure out how to actually get what the PL/SQL
>procedure returns. Is there any easy way to do this?? I can't modify the
>procedures themselves, so I need PL/SQL code to act as a wrapper or similar...
>
You can do something like this. Assuming you have a procedure 'foo' you call from the web normally:
create or replace procedure foo
as
begin
htp.p( 'Hello World' );
end;
and you want to get its output without using a web browser, you can:
SQL> set serveroutput on size 1000000
SQL> declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
owa.init_cgi_env( 0, nm, vl );
foo;
owa_util.showpage;
end;
/
What I've done is
- used owa.init_cgi_env to set up a 'dummy' cgi environment (that is empty) for the web procedure. This is in case the web procedures accesses the cgi_env (which it might in many ways). If it accessed the environment and we didn't call owa.init_cgi_env, it would raise an exception and fail.
- called the procedure foo, I could pass inputs whatever to it...
- called owa_util.showpage. this routine simply dumps the page into the dbms_output buffer so that sqlplus/svrmgr will dump it.
You can even setup the environment for the pl/sql routine if it accesses it and needs it setup, for example:
QL> declare
2 nm owa.vc_arr; 3 vl owa.vc_arr; 4 begin 5 nm(1) := 'SERVER_PORT'; 6 vl(1) := 80; 7 nm(2) := 'PATH_INFO'; 8 vl(2) := '/foo'; 9 nm(3) := 'SCRIPT_NAME'; 10 vl(3) := '/dcd/owa'; 11 11 owa.init_cgi_env( 3, nm, vl ); 12 foo; 13 owa_util.showpage;
14 end;
15 /
Hello World
SERVER_PORT = 80<BR>
PATH_INFO = /foo<BR>
SCRIPT_NAME = /dcd/owa<BR>
PL/SQL procedure successfully completed.
So, in this case, the procedure foo accessed and saw values in the cgi environment....
Someone else has already followed up with c code that shows you how to use oci to get the dbms-output stuff if you aren't using sqlplus/svrmgr to run this. It would be the same -- you would call dbms_output.get_line after calling owa_util.showpage.
>Also, are there any good PL/SQL discussion groups out there, or is this as
>good as it gets? ^_^
>
>Thanks...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 14 1998 - 00:00:00 CET