Oracle Database Express Edition (XE)

Hi Guys,

Just had this discussion on the main database forums, and the problem was somewhat resolved. But they did ask me to check here if this was a known issue.

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

Some of the guys in the main forums tried this out on Enterprise edition and did not encounter any issues. To faciliate their testing, I spooled the sqlsplus output and it is as follows

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL> create table test_table (test_column char(8 byte));
 
Table created.
 
SQL> insert into test_table
  2       values ('NZ07100S');
 
1 row created.
 
SQL> commit
  2  / 
 
Commit complete.
 
SQL> create or replace function gettestvalue
  2      return test_table.test_column%type
  3  is
  4     test_value   test_table.test_column%type;
  5  begin
  6     select max(test_column) into test_value from test_table;
  7  return test_value;
  8  end;
  9  / 
 
Function created.
 
SQL> select gettestvalue from dual
  2  / 
select gettestvalue from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "JEGA.GETTESTVALUE", line 6
 
 
SQL> spool off

At the request of one of the posters, the following was also executed.

SQL> select lengthb (TEST_COLUMN)
  2  from test_table
  3  where test_column = 'NZ07100S'
  4  / 
 
LENGTHB(TEST_COLUMN)
--------------------
                   8
 
SQL> select max (lengthb (test_column))
  2  from test_table
  3  / 
 
MAX(LENGTHB(TEST_COLUMN))
-------------------------
                        8

The eventual solution that made this piece of code work on XE was as follows

SQL> alter table test_table modify test_column varchar2 (8 byte);
 
Table altered.
 
SQL> select gettestvalue from dual;
 
GETTESTVALUE
--------------------------------------------------------------------------------
NZ07100S
 
SQL> spool off;

Now, I can't change the production (Enterprise Edition) schema. The XE is being used by the developers for their local testing. As such, knowing that the problem does not occur on Enterprise, I can ask them to make the changes locally to their XE for their local testing.


However, I was just wondering, does anyone know if this is a known issue with Oracle XE ??


Thanks and regards

Jega