sqloracle-databasecastingoracle12cvarchar2

Casting numerical value to VARCHAR2 throws ORA-03113

When we run statements against an Oracle 12c Enterprise Edition Release 12.2.0.1.0, which contains casts of numerical values to VARCHAR2(4000 char), we receive an ORA-03113 end-of-file on communication channel.

Various resources - such as https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9527821800346583868 describe, that this might be caused by a wrong database configuration. The mentioned resource (asktom.oracle.com) has one in common - they mentioned the same boundary of 1002 / 1003 where we encounter the error. However, I was not able to find a specific configuration/explanation which leads to this behaviour - especially with the 1002/1003 boundaries. Let me show a sample query:

This works:

select cast(numerical_value as varchar2(1002 char)) 
from my_table;

This fails with ORA-03113:

select cast(numerical_value as varchar2(1003 char)) 
from my_table;

Has anyone ever observed this behaviour or can tell me, which database setting might cause this?

Solution

  • The error is caused by the max_string_size=EXTENDED introduced in Oracle 12c.

    If this parameter is set to EXTENDED, casting a NUMBER(X byte) to a VARCHAR2(4009 byte or greater) will cause the database to crash. As result you'll get the mentioned error message ORA-03113 end-of-file on communication channel.

    This can be reproduced:

    1. Create a database with standard settings (max_string_size=STANDARD)

    2. Check, that casting is working:

      select cast(cast(12345 as NUMBER(19)) as VARCHAR2(4000 char))

    3. Migrate to max_string_size=EXTENDED as described here:
      https://dbaclass.com/article/max_string_size-parameter-oracle-12c/

    4. Do the same query again, which will fail: 

      select cast(cast(12345 as NUMBER(19)) as VARCHAR2(4000 char))