查询嵌套表 [英] Querying a Nested Table

问题描述

我正在尝试使用PL/SQL查询NESTED TABLE(可能有多个消息告诉我)，但是我一直收到错误消息: ORA-21700:对象不存在或标记为删除. ..

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

我不明白为什么它不允许我这样做...我已经确定dados变量中包含内容...我输出了它的.count并且它不为空.有人可以告诉我吗?

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

谢谢前进...

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;

推荐答案

我不明白您遇到的具体错误，但是通常您需要在SQL语句中包括对适当类型的显式强制转换:

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

这是因为该语句已从PL/SQL传递到SQL引擎进行处理，并且除了用户定义的变量外，它没有有关变量类型的信息.

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.

此外，这仅在模式级别(即，使用CREATE TYPE语句)声明类型(t_pontos)时有效.如果在PL/SQL代码中的某处声明了它，例如在程序包规范中，SQL引擎无法访问类型定义.

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.

这篇关于查询嵌套表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！