Re: PL/SQL output/wrappers ...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/14
Message-ID: <34c93735.209634498@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 CST