Software Engineer Thoughts

Some time ago i wrote about Binding list variable. In fact it is conversion of string to rows. My example works fine in SQL but when used in PL/SQL it might cause error ORA-22905: cannot access rows from a non-nested table item. I do know what are the necessary conditions for this error, as it does not always happen in PL/SQL. I faced it usually for quite complicated queries. To overcome this problem I just cast binding variable to varchar2 as shown below:

 procedure test_proc(p_list in varchar2)
 is
 begin
 for r in (select items.extract('/l/text()').getStringVal() item
 from table(xmlSequence(
 extract(XMLType(''||
 replace(cast (p_list as varchar2(4000)),
 ',','')||'')
 ,'/all/l'))) items) loop
 null;
 end loop;
 end;

It worked for me in all situations where I faced ORA-22905 on Oracle 10gR2 (10.2.0.3)

Paweł

Paweł Barut said...

Take a look at todays Laurent Schneider post. He uses the same xml construction for changing string to list of tokens.

29 May, 2007 22:06

David Budac said...

Thank god I found this post. I have had a similar problem yesterday (it worked perfectly in sql but not in plsql) and I would probably never figure out I had to CAST the string as a varchar. I still don't understand why it actually works to be honest.

24 August, 2010 16:05

Anonymous said...

thank you very much for your post. it saved me quite a lot of time.

18 November, 2011 11:30