Skip to Main Content

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!

ORA-22905: cannot access rows from a non-nested table item in Table func

dikuttNov 11 2004 — edited Nov 15 2004
I am using a table function in Oracle 8.1.7.4.0. I did declare an object type and a collection type like this:

CREATE TYPE t_obj AS OBJECT ...
CREATE TYPE t_tab AS TABLE OF t_obj;

My table function returns t_tab and is called like this:

SELECT ... FROM TABLE (CAST (my_pkg.table_fnc AS t_tab)) ...

This works pretty well as long as I run it in the same schema that owns the function and the 2 types. As soon as I run this query from another schema, I get an ORA-22905: cannot access rows from a non-nested table item error, even though I granted execute on both the types and the function to the other user and I created public synonyms for all 3 objects.

As soon as I specify the schema name of t_tab in the cast, the query runs fine:

SELECT ... FROM TABLE (CAST (my_pkg.table_fnc AS owner.t_tab)) ...

I don't like to have a schema hard coded in a query, therefore I'd like to do this without the schema. Any ideas of how to get around this error?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 13 2004
Added on Nov 11 2004
9 comments
4,643 views