Question on error "ORA-06502: PL/SQL: numeric or value error":

I created the function p_test but when I try to run it I get the following error:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 323
ORA-06512: at "JUAN.TEST_IP", line 6
ORA-06512: at line 1.

Answer:

The docs note this on the ORA-06502 error:

ORA-06502: PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Answer by Edward Stoever:

does owa_util exist and do you have access to it?

You can find out by describing owa_util

SQL> desc sys.owa_util

But it looks like you do have access to it, because you are getting an error on a specific line of that package, which means that you entered it.

The OWA_UTIL.get_cgi_env function is made for web browser accessing dynamic web content served up through an web server. I don't think it will work with 6i forms. That is why I suggested you look at THIS web page.

There is a java bean package created to get the IP address of the browser that is accessing a 6i form through a browser there. It says: Get Client Information interacts with the client machine to get the username, hostname and IP address. (ReadMe)

I have never used it, I don't even know how it works. But it is worth reading about to decide if you can make it work for your application.

More info on ORA-6502:

A user on Oracle DBA Forums encountered ORA-6502 as related to this SQL:

connected to:
Oracle 9i Enterprise Edition Release 9.2.5.0 - Production
With Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

Here is the detail=======================


SQL> DESC DEBUG_TEMP

Name Null? Type
----------------------------------------- -------- ----------------------------
MSG VARCHAR2(500)

SQL> SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME='DEBUG_TEMP';

OWNER TABLE_NAME
------------------------------ ------------------------------
LCL DEBUG_TEMP

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DEBUG_TEMP','LCL') FROM DUAL;

ERROR:
ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'n'
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3406
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4259
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

SQL> SPOOL OFF
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DEBUG_TEMP','LCL') FROM DUAL;
ERROR:
ORA-03113: end-of-file on communication channel


no rows selected
SQL>

==========================================

The replier offered this advice: