Skip to Main Content
Forums

SQL & PL/SQL

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-22813: operand value exceeds system limits

889253Sep 20 2011 — edited Sep 20 2011
Hi Everyone,

I've been on this one for quite some time but I can't figure it out:
ORA-22813: operand value exceeds system limits

I'm getting this when calling a function that pipes out a simple type ...
The weird thing is that this works fine on one environment, and it errors out on another one ...

What I'm trying to achieve is accessing a comma-separated list as a table to use it in an IN clause later on

The Type:
create or replace
TYPE split_tbl AS TABLE OF VARCHAR2(32767);

the function:

function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767):= p_list;
l_value varchar2(32767);
begin
loop
l_idx :=instr(l_list,p_del);
if l_idx > 0 then
pipe row(trim(substr(l_list,1,l_idx-1)));
l_list:= substr(l_list,l_idx+length(p_del));

else
pipe row(trim(l_list));
exit;
end if;
end loop;
return;
end split;

The call:
SELECT column_value
FROM TABLE(<Some_package_where_you_put_the_function>.SPLIT('aaa, bbb' ), ','))

(obviously 'aaa, bbb' would be a subquery that returns a comma-separated result string, but it errors out just as well with the literal string ....)

I have no clue as to why this works on one environment, a,d errors out on the other ... Both are 11G ...

Any ideas?

Thanks for your input!

Stijn

Comments

BluShadow
886250 wrote:
Hi Everyone,

I've been on this one for quite some time but I can't figure it out:
ORA-22813: operand value exceeds system limits

I'm getting this when calling a function that pipes out a simple type ...
The weird thing is that this works fine on one environment, and it errors out on another one ...

What I'm trying to achieve is accessing a comma-separated list as a table to use it in an IN clause later on

The Type:
create or replace
TYPE split_tbl AS TABLE OF VARCHAR2(32767);

the function:

function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767):= p_list;
l_value varchar2(32767);
begin
loop
l_idx :=instr(l_list,p_del);
if l_idx > 0 then
pipe row(trim(substr(l_list,1,l_idx-1)));
l_list:= substr(l_list,l_idx+length(p_del));

else
pipe row(trim(l_list));
exit;
end if;
end loop;
return;
end split;

The call:
SELECT column_value
FROM TABLE(<Some_package_where_you_put_the_function>.SPLIT('aaa, bbb' ), ','))

(obviously 'aaa, bbb' would be a subquery that returns a comma-separated result string, but it errors out just as well with the literal string ....)

I have no clue as to why this works on one environment, a,d errors out on the other ... Both are 11G ...

Any ideas?

Thanks for your input!

Stijn
Wow, you've dug up some of my old code from somewhere on the forums. :D

You don't need to use a user defined function to split the data on a delimiter, it can be easily achieved in SQL:

e.g.
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'aa,bbb,ccc,dd' as txt from dual)
  2  --
  3  -- end of test data
  4  --
  5  select regexp_substr(txt,'[^,]+',1,level) as txt
  6  from t
  7* connect by regexp_substr(txt,'[^,]+',1,level) is not null
SQL> /

TXT
-------------
aa
bbb
ccc
dd
or like this...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'This is some sample text that needs splitting into words' as txt from dual)
  2  select x.*
  3  from t
  4      ,xmltable('x/y'
  5                passing xmltype('<x><y>'||replace(t.txt,' ','</y><y>')||'</y></x>')
  6                columns word varchar2(20) path '.'
  7*              ) x
SQL> /

WORD
--------------------
This
is
some
sample
text
that
needs
splitting
into
words

10 rows selected.
1 person found this helpful
Kim Berg Hansen
Your table type is:
create or replace
TYPE split_tbl AS TABLE OF VARCHAR2(32767);
VARCHAR2 can be up to 32K in PL/SQL environment only, not in SQL environment.
You could try it with:
create or replace
TYPE split_tbl AS TABLE OF VARCHAR2(4000);
(And a nitpick - why do you declare l_value variable when it is not used? ;-) )
1 person found this helpful
MichaelS
You could try it with:
create or replace
TYPE split_tbl AS TABLE OF VARCHAR2(4000);
No need to do this: Oracle will handle the size all by itself.
Kim Berg Hansen
Very interesting, thanks Michael.

Personally I think I would prefer to explicitly use 4000 so another programmer reading the code won't believe that it will work with 32K ;-)
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 18 2011
Added on Sep 20 2011
#11g, #database, #error, #pl-sql, #plsql, #query, #sql, #table
4 comments
4,463 views