Re: ora-6502 with basic webserver apps

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 14 Sep 1998 18:07:02 GMT
Message-ID: <36025a5e.20752901@192.86.155.100>

A copy of this was sent to Stephen Mulcahy <smulcahy_at_smsat.ilo.dec.com> (if that email address didn't require changing) On 14 Sep 1998 16:20:03 GMT, you wrote:

>Hi,
>
>I'm currently working on some webserver plsql script and I've come across
>a small problem. I only noticed this one when I started attempting to
>exec some of my scripts from sqlplus (using showpage to dump the output
>to the screen and sanity check it).
>
>The following simple test script will illustrate my problem...
>
>create or replace procedure foo as
>
>begin
> htp.p(owa_util.get_cgi_env('SERVER_NAME'));
> htp.p('Foo');
>end foo;
>/
>
>Now, when I execute this through the webserver .. it runs ok, however
>If I execute it from the sqlplus prompt, I get the following,
>
>begin foo; end;
>
>*
>ERROR at line 1:
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "WWW_USER.OWA_UTIL", line 155
>ORA-06512: at "WWW_USER.FOO", line 3
>ORA-06512: at line 1
>
>Now, I have the owa_util package loaded into www_user's schema. I'm not
>clear on whether this is a problem caused by me not using the get_cgi_env
>function properly or if there is some problem with the owa_util package.
>Has anyone else seen this problem? While my scripts run I don't think
>I should leave this problem hanging around.

The owa_util cgi-environment functions do not work UNLESS the cgi environment has be initialized. The error message is due to the internal owa_util package state (state of variables) not being set by anything. Consider the following example that demonstrates the error:

SQL> exec owa_util.print_cgi_env
begin owa_util.print_cgi_env; end;

*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "OWS2.OWA_UTIL", line 172
ORA-06512: at line 1

So, we get a numeric or value error. The exact reason is because of code in owa_util that looks like:

      ....
      for i in 1..owa.num_cgi_vars
      loop
      ....

if owa.num_cgi_vars is NULL, this loop correctly throws that exception (how do you loop to NULL).

So, how to fix so that a web routine can be tested in sqlplus? Just run a block such as:

SQL> declare

  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'FOO';
  6          vl(1) := 'BAR';
  7          owa.init_cgi_env( 1, nm, vl );

  8 end;
  9 /
PL/SQL procedure successfully completed.

That calls the initialization routine which is normally called by the pl/sql cartridge itself. Now we can safely:

SQL> exec owa_util.print_cgi_env
PL/SQL procedure successfully completed.

and then to see the results of our routine execute:

SQL> 
SQL> set heading  off
SQL> set feedback off
SQL> set trimspool on
SQL> set linesize 255
SQL> set serveroutput on size 1000000
SQL> spool abc

SQL> exec owa_util.showpage
FOO = BAR<BR>

>
>Thanks for your help,
>
>-stephen
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
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 Mon Sep 14 1998 - 13:07:02 CDT