error ORA-06502

[culonbu]

Hi,

I wrote this piece of code to get current procedure name but having error 6502 when executing it.

Can any one know why is it so?

Thks





SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 14 17:20:03 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


SQL> l
1 create or replace procedure my_proc
2 is
3 l_proc varchar2(1024);
4 begin
5 l_proc := owa_util.GET_PROCEDURE ;
6 --raise_application_error(-20001,l_proc);
7* end;
SQL> /

Procedure created.

SQL> exec my_proc
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "SYS.OWA_UTIL", line 2389
ORA-06512: at "D269732.MY_PROC", line 5
ORA-06512: at line 1


SQL>

[TimHall]

Hi.

The OWA_UTIL package is part of the web toolkit, used to write web applications to run with mod_plsql. Although there are a number of functions that can be used in a different context, you should really be using them just for that purpose.

You've compiled the procedure, then run it in SQL*Plus, which is not a HTTP server and doesn't use mod_plsql, so it is unlikely to work.

I compiled the following variation on your procedure and called it from a browser via mod_plsql:

create or replace procedure my_proc
is
  l_proc varchar2(32767);
begin
  l_proc := owa_util.GET_PROCEDURE ;
  HTP.print(l_proc);
end;
/

It worked fine, giving me a web page with "MY_PROC" written on it.

This kind-of proves my assumption that this procedure only works when used in conjunction with mod_plsql and a HTTP server.

Note also, I've set the "l_proc" size to 32767. The "GET_PROCEDURE" function returns a VARCHAR2, so it can theoretically be as big as 32767. You should be careful to size your variables correctly. I'm sure 1024 is fine, but you never know when something will be changed making your code fall in a heap.

Cheers

Tim...

[culonbu]

Thks Tim

I will look into it.