CODEWITHSUNDEEP
sqloracle-databaseplsql
Pip Falconer
Pip Falconer

Reputation: 105

Why can't I can't I use my Oracle user defined type like this?

In an Oracle package I have defined a type

type setTable is table of my_sets.pkey%type;

in the package declaration (the non-body part). The pkey column referenced is a number(38). Then in a function in the package body I have 

...
with d as (select column_value from table(sets)),
...

where sets is a parameter to the function of type settable. This line fails to compile with the error 'ORA-22905: cannot access rows from a non-nested table item'. What can I do to resolve this?

Upvotes: 2

Views: 335

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132700

The select statement is SQL not PL/SQL, and the SQL engine can only work with types defined on the server like this:

create type setObj is object (<attributes>);
create type setTable is table of setObj;

Upvotes: 4

Related Questions