Querying a Nested Table

Question

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??

Thanks is advance...

procedure PREENCHE_CURSOR_ESTRANG_TOTAL(O_CURSOR out t_refcur) is    

  c_nips t_refcur;

  dados T_PONTOS := T_PONTOS();--nested table instantiating..

  i number;

  nip number(8);

  gerador_de_nips varchar2(600) := 'a biG SQL QUERY nobody needs to know about =P';


begin
    i := 1;
    open c_nips for gerador_de_nips;
    loop
        dados.extend;
        fetch c_nips into nip;
        exit when c_nips%notfound;
        dados(i) := RETORNA_PONTOS(nip);
        i := i+1;

    end loop;


    close c_nips;

    open O_CURSOR for select * from table(dados); /*WHY*/


end PREENCHE_CURSOR_ESTRANG_TOTAL;

Answer 1

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.