oracle+exceeds,ORA-22813: operand value exceeds system limits

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(.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!

StijnWow, you've dug up some of my old code from somewhere on the forums.

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

ddor 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.