Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Monday, May 28, 2007

Solving ORA-22905: cannot access rows from a non-nested table item

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)
    for r in (select items.extract('/l/text()').getStringVal() item
              from table(xmlSequence(
                  replace(cast (p_list as varchar2(4000)),
                      ,'/all/l'))) items) loop
    end loop;
It worked for me in all situations where I faced ORA-22905 on Oracle 10gR2 (



Paweł Barut said...

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

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.

Anonymous said...

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


Copyright © Paweł Barut
Printing from DOS to USB Printer