Return
-
Title
ORA-06502: PL/SQL: numeric or value error when debugging / executing packages. -
Description
When I am debugging or executing a procedure in a package body that uses HTP it raised the following error message:
ORA-06502: PL/SQL: numeric or value error
This problem only occurs the first time I run the debugger, all subsequent executions are fine. -
Resolution
This is an Oracle Bug/Issue and not TOAD. Explanation and fix as follows:
Using the following package:
CREATE OR REPLACE PACKAGE pkg_www IS
PROCEDURE test_owa;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_www IS
PROCEDURE test_owa IS
BEGIN
htp.print ('Hello World');
END;
END;
/
Open SQL*Plus and run the following:
BEGIN
PKG_WWW.TEST_OWA;
END;
/
This will error out on the first attempt, rerun the anonymous block and it will run correctly, the reason this occurs 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, if it is failing in SQL*Plus then it will fail in TOAD as well.
There are a number of ways that you can overcome this problem:
The first is to initialize the OWA package in your packages, for example see the code below:
CREATE OR REPLACE PACKAGE BODY pkg_www IS
--OWA variables
name_arr OWA.VC_ARR;
value_arr OWA.VC_ARR;
PROCEDURE test_owa IS
BEGIN
-- Initialize the OWA package.
OWA.INIT_CGI_ENV(0, NAME_ARR, VALUE_ARR);
htp.print ('Hello World');
END;
END;
/
The second way is to do the following:
1. Put the following code in the glogin.sql, this file can be found under %ORACLE_HOME%\SQLPlus\Admin
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/debug the package.
Now to configure TOAD to use this login file do the following:
1) Go to View | Options | Startup
2) Select the File to autoload on startup browse button.
3) Drill down to the glogin.sql file
4) Ok the changes and restart TOAD
You do not necessarily need to add the above to the glogin.sql file, if you only want it to execute the anonymous block to initialize the OWA package without executing all the other commands in the glogin.sql file then create a .sql file with the anonymous block above and point navigator to this file in the preferences as described above.
If you wish to see more information on this issue then please look at Oracle's metalink, if you search for the following DocID: 202017.1