ORA-06502 When using MAX(Column) with %TYPE

Hi,

I have tried to narrow down the problem as much as I can, and have managed to isolate it to a particular scenario. As the structure and data is sensitive, I have had to come up with a scenario which I have tested and it causes the same issue to occur. The scenario is as follows

Assuming I have a table defined as follows

CREATE TABLE TEST_TABLE   (TEST_COLUMN CHAR(8 BYTE)) ;

and the table only has a single record NZ07100S

We also define a function as

CREATE OR REPLACE FUNCTION
FUNCTION GETTESTVALUE
RETURN TEST_TABLE.TEST_COLUMN%TYPE
IS
  TEST_VALUE TEST_TABLE.TEST_COLUMN%TYPE;
BEGIN
  SELECT MAX(TEST_COLUMN) INTO TEST_VALUE FROM TEST_TABLE;
  RETURN TEST_VALUE;
END;



We ran the following command

SELECT GETTESTVALUE FROM DUAL;

and receive an error as follows

Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "GETTESTVALUE", line 6
06502. 00000 -  "PL/SQL: numeric or value error%s"


However, if were to modify the function to the following

CREATE OR REPLACE FUNCTION
FUNCTION GETTESTVALUE
RETURN TEST_TABLE.TEST_COLUMN%TYPE
IS
  TEST_VALUE TEST_TABLE.TEST_COLUMN%TYPE;
BEGIN
  SELECT TEST_COLUMN INTO TEST_VALUE FROM TEST_TABLE;
  RETURN TEST_VALUE;
END;

There is no error reported and the value NZ07100S is returned.

Of course, when we modified the function to be

CREATE OR REPLACE FUNCTION
FUNCTION GETTESTVALUE
RETURN TEST_TABLE.TEST_COLUMN%TYPE
IS
  TEST_VALUE TEST_TABLE.TEST_COLUMN%TYPE;
BEGIN
  SELECT CAST(MAX(TEST_COLUMN) AS CHAR(8))INTO TEST_VALUE FROM TEST_TABLE;
  RETURN TEST_VALUE;
END;

No errors are reported, and the value is returned

However, the function in question has been in production usage since 2004. We are currently making some modifications to the application and had imported the backup from Oracle 10g into Oracle XE. When we try to run the function, the error is thrown out.

I would like to understand if this is a limitation with Oracle XE that causes this problem. It would seem to me that the problem is selecting the MAX value into the variable that had been defined. Running the query directly from sqlplus does not generate an error, and as such do not feel there is an error with the SQL. The only time we seem to get the error is with the INTO statement.

Is there are a means to use the function as it has been defined in production? Why would the ORA-06502 error be thrown out? It does not seem to make sense to me. Would appreciate any guidance on this.


Thanks and regards

Jega