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

Cause

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 necessary variables at login time and hence the packages work straight away.

Solution:

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.

Workaround:

After error stack thrown, re-execute the same procedure call.