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?