Executing OWA Packages in SQLPLUS Throws ORA-06502/6512 Errors
--------------------------------------------------------------
Problem Description
-------------------
Executing some of the OWA pacakges in SQL*Plus throws Oracle errors the first time the procedure is called. For e.g.,
SQL> exec owa_util.mime_header('text/html',true);
BEGIN owa_util.mime_header('text/html',true); END;
* ERROR at line 1: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 315
ORA-06512: at "SYS.OWA_UTIL", line 355
ORA-06512: at line 1
Solution Description
--------------------
1. Put the following code in the login.sql
DECLARE name_arr OWA.VC_ARR;
value_arr OWA.VC_ARR;
BEGIN
OWA.INIT_CGI_ENV(0, NAME_ARR, VALUE_ARR);
END;
/
2. Save the file.
3. Exit from SQL*Plus.
4. Log back into SQL*Plus.
5. Now, execute the same package.
Explanation
-----------
The reason why this some times doesn't work in SQLPLUS is because there are some package variables that need to be initialized before you attempt to execute a PL/SQL procedure which uses the OWA packages. This is handled correctly in the web environment (PL/SQL GateWay to access the procedure e.g., 10g/9iAS mod_plsql). Adding the code in the login.sql file initializes the neccessary variables at login time and hence the packages work straight away.
WORKAROUND
----------
After error stack thrown, re-execute the same procedure call.
Partager