Re: PL/SQL & OraWeb 3.0: overloaded proc has performance hit

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/16
Message-ID: <3420e7a4.25690681@newshost>#1/1

On Tue, 16 Sep 1997 14:02:41 +0100, Steve Livingston <slivings_at_digitalriver.com> wrote:

>Using: Oracle 7.3.2.2 & Oracle Webserver 3.0
>
>I've got a main procedure that has 20 overloaded copies.
>
>This runs OK (fast) from sqlplus.
>
>But...when executed from the web...response time goes to 4 seconds!?
>
>Has anyone seen this?
>Oracle help has not heard of this problem.
>

I have not but it might be due to the massive overloading. Have you tried sql_trace and tkprof to see where the time is being spent?

>By the way...I've used overloading because I web/POST info to PL/SQL,
>but PL/SQL does not (?) have access to the post-string. So I've
>overloaded my main routine to handle all combinations of 'param=value'
>from my web forms.
>

I don't understand. If you have an HTML form, it should be able to have a dedicated action. Can you post an example of why you need only one routine with 17 variants? If you don't know the number of inputs, you could consider using pl/sql tables. This allows you to pass in a large number of form elements without having to set up a separate in variable for each and every one.

For example, consider:

   htp.formOpen( 'do_something', 'post' );

   htp.p( 'Enter field1' );
   htp.formHidden( 'p_name', 'field1' );
   htp.formText( 'p_value' );
   htp.br;

   htp.p( 'Enter field2' );
   htp.formHiddne( 'p_name', 'field2' );
   htp.formText( 'p_value' );
   htp.br;
   ....
   htp.p( 'Enter fieldN' );
   htp.formHiddne( 'p_name', 'fieldN' );

   htp.formText( 'p_value' );
   htp.br;

Then, the procedure do_something could look like:

procedure do_something( p_name in owa.vc_arr, p_value in owa.vc_arr ) is
begin

   for i in 1 .. 10000 loop
   begin

       htp.p( p_name(i) || ' = ' || p_value(i) );    exception when no_data_found then exit;    end;
   end loop;

end;

This would print out something like:

field1 = value1
field2 = value2
...
fieldN = valueN

In effect, you would be passing yourself a corelated array of names and values.

>What I really want is access to the post-string so I can parse it myself
>(a la cgi). Is this possible?
>

Not in 1.x - 3.0, no.

>Cheers,
>Steve
>

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 Received on Tue Sep 16 1997 - 00:00:00 CDT