Take the 2-minute tour ×

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';

    i := 1;
    open c_nips for gerador_de_nips;
        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*/

share|improve this question
Can you post the definition of T_PONTOS? In particular, is it declared in SQL or PL/SQL? Additionally, RENTORNA_PONTOS appears not to be defined anywhere. –  Justin Cave Feb 23 '12 at 21:30

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer

Try defining your cursor in the beginning of your procedure and open it in the body of your procedure.

share|improve this answer

Your Answer


