I'm trying my hand at querying a NESTED TABLE using PL/SQL (which I'm told by several sources is possible) but I keep getting the error message: ORA-21700: object does not exist or is marked for delete...

I don't understand why it won't let me do it...I've already established that the dados variable has content in it... I output its .count and it's not empty.. Could someone shed me a light on this??

2 Answers
2

I don't understand the specific error you are getting, but generally you need to include an explicit cast to the appropriate type in the SQL statement:

open O_CURSOR for select * from table(CAST(dados AS t_pontos));

This is because the statement is handed off from PL/SQL to the SQL engine for processing, and it has no information about the type of the variable beyond it being user-defined.

Also, this only works if the type (t_pontos) is declared at the schema level, i.e. with a CREATE TYPE statement. If it is declared somewhere in PL/SQL code, e.g. in a package specification, the SQL engine cannot access the type definition.