Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-22813 when using varchar2(32767 CHAR) in an objecttype

rooimMay 16 2019 — edited May 17 2019

We are using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I am geting this error while selecting from a pipelined table function using a type that contains a varchar2(32767 CHAR) type attribute:

ORA-22813. 00000 -  "operand value exceeds system limits"

*Cause:    Object or Collection value was too large. The size of the value

           might have exceeded 30k in a SORT context, or the size might be

           too big for available memory.

*Action:   Choose another value and retry the operation.

This is my code:

++++++++++++

create or replace type type_varchar2_char_r force as object

( max_varchar_char            varchar2(32767 CHAR)

);

create or replace TYPE  type_varchar2_char_t as table of type_varchar2_char_r

;

create or replace function

type_varchar2_char_pl

return type_varchar2_char_t pipelined

is

  --

  rec_type_varchar2_char  type_varchar2_char_r := type_varchar2_char_r( null);

begin

  rec_type_varchar2_char.max_varchar_char := 'abcdefghijklmnopqrstuvwxyz';

  pipe row(rec_type_varchar2_char );

  --

  return;

end;

select *

from table( type_varchar2_char_pl )

;

ORA-22813. 00000 -  "operand value exceeds system limits"

*Cause:    Object or Collection value was too large. The size of the value

As soon as I use  varchar2(32767 BYTE) in my Object Type, it works fine:

create or replace type type_varchar2_byte_r force as object

(  max_varchar_char            varchar2(32767 BYTE)

);

create or replace TYPE  type_varchar2_byte_t as table of type_varchar2_byte_r

;

create or replace function

type_varchar2_byte_pl

return type_varchar2_byte_t pipelined

is

  --

  rec_type_varchar2_byte  type_varchar2_byte_r := type_varchar2_byte_r( null);

begin

  rec_type_varchar2_byte.max_varchar_char := 'abcdefghijklmnopqrstuvwxyz';

  pipe row(rec_type_varchar2_byte );

  --

  return;

  --

end;

select *

from table( type_varchar2_byte_pl )

;

MAX_VARCHAR_CHAR

----------------------------------------------

abcdefghijklmnopqrstuvwxyz

As a workaround we downsized the type attribute to varchar2(24000 CHAR), and this works fine.

Does anybody have a clue if I am doing something wrong?

Or is this expected behaviour? Where can I find information about the maximum allowed BYTE's instead off CHAR's?

Comments
Post Details
Added on May 16 2019
1 comment
961 views