Oracle PL/SQL:如何从ref的VARRAY中DEREF

Question

我是新的Oracle对象，我有一个问题。我不知道如何从ref的VARRAY中取消引用一个项目。下面是再现我遇到的问题的一些源代码。这个错误是:PLS-00306:调用'DEREF'时参数的数量或类型错误

DROP TYPE LOC FORCE
/
DROP TYPE LIST_LOC FORCE
/
DROP TYPE PIZ FORCE
/
CREATE OR REPLACE TYPE LOC AS OBJECT(
  NAME      VARCHAR2(30),
  MEMBER FUNCTION GET_NAME RETURN VARCHAR2 
)
/
CREATE OR REPLACE TYPE BODY LOC AS
  MEMBER FUNCTION GET_NAME RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.NAME;
  END;
END;
/
CREATE OR REPLACE TYPE LIST_LOC AS VARRAY(10) OF REF LOC
/
CREATE OR REPLACE TYPE PIZ AS OBJECT(
  LOCS      LIST_LOC,
  MEMBER PROCEDURE DISPLAY_LOCS
)
/
CREATE OR REPLACE TYPE BODY PIZ AS
  MEMBER PROCEDURE DISPLAY_LOCS IS
  BEGIN
    FOR IDX IN SELF.LOCS.FIRST..SELF.LOCS.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(DEREF(SELF.LOCS(IDX)).GET_NAME()); --this is the line that generates the error
    END LOOP;
  END;
END;
/

错误出现在DISPLAY_LOCS过程中，当我试图从LOCS数组中获得REF位置IDX，并将其DEREF以获得名称。

Answer 1

DEREF必须在SQL语句中:http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm i463707

在PL/SQL中是VALUE, REF和DEREF函数只能出现在SQL语句中声明

这是

CREATE OR REPLACE TYPE BODY PIZ AS
  MEMBER PROCEDURE DISPLAY_LOCS IS
  x varchar2(30) ;
  BEGIN
    FOR IDX IN SELF.LOCS.FIRST..SELF.LOCS.LAST LOOP
        select DEREF(SELF.LOCS(IDX)).GET_NAME() into x from dual ;
      DBMS_OUTPUT.PUT_LINE(x); --this is the line that generates the error
    END LOOP;
  END;
END;
/

很好的测试用例来复制!