Skip to Main Content
  • Questions

  • getting a disconnect when using a cast to varchar2

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, michael.

Asked: April 04, 2016 - 3:21 pm UTC

Last updated: April 05, 2016 - 11:52 am UTC

Version: 12.1.02

Viewed 1000+ times

You Asked

Hello Tom,
I'm getting disconnects when casting numeric values to varchar2,
Is this a bug or is this a feature and solvable with a change in parameter setting?

max_string_size extended.
blocksize 8k
nls_characterset AL32UTF8

When doing something as simple as:
-- no errors
select cast(1234 as varchar2(4009)) max_
from dual;
-- disconnect in sql*plus
select cast(max(1234) as varchar2(4009)) max_
from dual;
-- and another example
create table test (veld number(10));
insert into test values (1234)
commit;
-- no problem
select cast(veld as varchar2(4008)) max_
from test;
-- also disconnect in sql*plus
select cast(veld as varchar2(4009)) max_
from test;

-- in the alert file something like this with the disconnect:
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x5A98BC3, __intel_ssse3_rep_memcpy()+8787] [flags: 0x0, count: 1]

and Connor said...

Generally anything that crashes like that is a bug...ie, we should at least gracefully error.

Some things to check

a) you are looking at doing varchar2 greater than 4000, so make sure you've enabled that facility correctly ( see https://connormcdonald.wordpress.com/2015/09/11/longer-strings-in-12c/ )

b) make sure you are using a 12c version of sqlplus, because the older versions wont understand >4000.

If all that is fine, and its still crashing, its time to talk to Support.

My windows 12c (12.1.0.2 with latest BP) seems ok with this:

SQL> select cast(1234 as varchar2(4009)) max_
  2  from dual;

MAX_
-----------------------------------------------------------------------
------------------------------------------------------------
1234

SQL>
SQL> select cast(max(1234) as varchar2(4009)) max_
  2  from dual;

MAX_
-----------------------------------------------------------------------
------------------------------------------------------------
1234

SQL>
SQL> drop table test purge;

Table dropped.

SQL> create table test (veld number(10));

Table created.

SQL> insert into test values (1234);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select cast(veld as varchar2(4008)) max_
  2  from test;

MAX_
-----------------------------------------------------------------------
------------------------------------------------------------
1234

SQL>
SQL> select cast(veld as varchar2(4009)) max_
  2  from test;

MAX_
-----------------------------------------------------------------------
------------------------------------------------------------
1234

SQL>
SQL>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

it is probably a bug then ..

michael stanneveld, April 05, 2016 - 6:46 am UTC

Thank you for the quick response, to answer the a/b points:
a - yes ( mentioned in my incomplete list of parameters)
b - yes (and also in sqldeveloper 4.1.3).
I tested this also at another 12c database and got the same crash at cast (max( 124) as varchar2(1003) , varchar2 (1002) worked fine.
So it looks like it's related to database settings when it occurs, it (the crashing) might even happen at the database you are using but at varchar2(16017) or even higher?
Anyway the crashing part indeed looks like a bug, will report that to support. Thank you for your time.
Connor McDonald
April 05, 2016 - 11:52 am UTC

OK, here's my unpatched 12.1.0.2 instance

SQL> create table t ( v int );

Table created.

SQL> declare
  2    x varchar2(32767);
  3  begin
  4  for i in 4000 .. 32000 loop
  5    insert into t values (i);
  6    commit write wait;
  7    execute immediate 'select cast(max(1234) as varchar2('||i||')) from dual' into x;
  8  end loop;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8304
Session ID: 20 Serial number: 43841


SQL>  conn mcdonac/*****
Connected.

SQL> select * from t;

         V
----------
      4000
      4001
      4002
      4003
      4004
      4005
      4006
      4007
      4008
      4009
      4010
      4011
      4012
      4013
      4014
      4015
      4016
      4017
      4018
      4019
      4020
      4021
      4022
      4023
      4024
      4025
      4026
      4027
      4028
      4029
      4030
      4031
      4032
      4033
      4034
      4035
      4036
      4037
      4038
      4039
      4040
      4041
      4042
      4043
      4044
      4045
      4046
      4047
      4048
      4049
      4050
      4051
      4052
      4053
      4054
      4055
      4056
      4057
      4058
      4059
      4060
      4061
      4062
      4063
      4064
      4065
      4066
      4067
      4068

69 rows selected.


Hope this helps.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.