Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22905: cannot access rows from a non-nested table item (oracle9i)
ORA-22905: cannot access rows from a non-nested table item [message #327797] Tue, 17 June 2008 12:54 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Can any one please tell me how i can fix this error?

SQL> explain plan set statement_id='XYZ' for
  2  select * from emp where ename='SCOTT';

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','XYZ','BASIC'));
select * from table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','XYZ','BASIC'))
              *
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
Re: ORA-22905: cannot access rows from a non-nested table item [message #327798 is a reply to message #327797] Tue, 17 June 2008 13:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

select * from table( dbms_xplan.display );
Re: ORA-22905: cannot access rows from a non-nested table item [message #327799 is a reply to message #327797] Tue, 17 June 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 9i, you have to cast to the function return type.

By the way, if you have a look at error messages, you find the same thing:
Quote:
ORA-22905: cannot access rows from a non-nested table item
Cause: attempt to access rows of an item whose type is not known at parse time or that is not of a nested table type
Action: use CAST to cast the item to a nested table type


Regards
Michel

[Updated on: Tue, 17 June 2008 13:03]

Report message to a moderator

Re: ORA-22905: cannot access rows from a non-nested table item [message #327804 is a reply to message #327799] Tue, 17 June 2008 13:28 Go to previous message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Michel, Thank you.

Previous Topic: Queries That Never Complete
Next Topic: need help in writing proc or getting the logic.
Goto Forum:
  


Current Time: Sat Dec 10 22:36:45 CST 2016

Total time taken to generate the page: 0.22512 seconds