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?
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.
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
1 create or replace procedure my_proc
3 l_proc varchar2(1024);
5 l_proc := owa_util.GET_PROCEDURE ;
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
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:
It worked fine, giving me a web page with "MY_PROC" written on it.
create or replace procedure my_proc
l_proc := owa_util.GET_PROCEDURE ;
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.
I will look into it.
Click Here to Expand Forum to Full Width