2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ihkawiss
  • 986
  • 3
  • 9
  • 25
  • 5
    Just curios, are you counting atoms? (1000 digit numbers are huge.) – jarlh Nov 15 '19 at 11:48
  • @jarlh This is a very good question. Honestly, I don't know (especially since the original query tires to cast to 4000) - these queries are part of a underlying dependency/ base product. I do belive, that this might not be necessary as you mentioned. As I read from Oracle - the default maximum String Size is 4000, so technically it should be possible, shouldn't it? I might not be able to change this. – ihkawiss Nov 15 '19 at 11:57
  • @Andrew we selecting against Oracle 12c Enterprise Edition Release 12.2.0.1.0 – ihkawiss Nov 15 '19 at 11:59
  • is it possible for you to try this query in oracle 11 or different version ? this looks bug to me..for more information check here :https://community.oracle.com/message/9998243 – Andrew Nov 15 '19 at 12:00
  • What's the type of the numerical_value column? – LukStorms Nov 15 '19 at 12:09
  • 2
    I have just tried this with a large number (`9.2345678909876551E+125`) from a binary_double column to `varchar2(1003 char)` on my 18c XE instance and it worked fine. So maybe it's a bug in 12c. However, I don't understand why you want to cast a numerical value to such a large string. , Even a binary_double can't hold a number exceeding a few hundred digits. – APC Nov 15 '19 at 12:10
  • @Andrew I'am currently not able to test this query against another Oracle Version than 12c. But we've tested the same query on an other instance - where the error does not occour. – ihkawiss Nov 15 '19 at 12:30
  • @APC thanks for your effort to test this query. You're right, as mentioned above I'm not aware of the reason why it's done this way. – ihkawiss Nov 15 '19 at 12:31
  • @LukStorms type of column is NUMBER(19) – ihkawiss Nov 15 '19 at 13:01
  • Hi, have you tried using to_char() instead of cast()? e.g. select to_char(numerical_value) from my_table – damir huselja Nov 15 '19 at 14:00

2 Answers2

1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    I've upvoted your answer, since you are basically right! However, as you maybe noticed in the comments above, I'm not able to change this due to support / base product reasons. Therefore, I'm searching for the technical explanation here. – ihkawiss Nov 15 '19 at 14:18
  • @ihkawiss I know you can't change the database version; however, the point of my frame challenge was that you don't need to cast to `VARCHAR2(1003)` when `VARCHAR2(170)` should be sufficient (or `VARCHAR2(200)` for extra piece of mind). The technical explanation is going to revolve around it being a bug in Oracle 12c (since 11g and 18c don't obviously suffer from it - but that could be setup related and the bug isn't showing) but its so esoteric that I doubt anyone has much concrete evidence of a particular cause; the best solution would be to contact My Oracle Support and see what they say. – MT0 Nov 15 '19 at 14:53
1

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))
ihkawiss
  • 986
  • 3
  • 9
  • 25