CODEWITHSUNDEEP
sqloracle-databasecastingoracle12cvarchar2
ihkawiss
ihkawiss

Reputation: 986

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?

Upvotes: 2

Views: 2876

Answers (2)

ihkawiss
ihkawiss

Reputation: 986

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))

Upvotes: 1

MT0
MT0

Reputation: 167972

This answer is a frame challenge to the question.

From Oracle Datatype limits:

NUMBER [ (p [, s]) ] Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.

So, if you are formatting a value as a string it can have a maximal value of:

-999999999999999999999999999999999999990000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Which is 127 characters, or if you consider the largest negative exponent then

-.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999

Which has a length of 169 characters (170 if there is a leading zero).

db<>fiddle

There is no need to cast a numeric value stored in a table to anything more than this size so you can use:

CAST(value AS VARCHAR2(200 CHAR))

and it should more than cope with all values stored in a NUMBER column.

Upvotes: 1

Related Questions