Using vaarray in dynamic sql - ORA-00904: invalid identifier

From: Kevin S <>
Date: Tue, 27 Jan 2009 06:32:25 -0800 (PST)
Message-ID: <>


I am doing the following work in oracle

The procedure below is producing the error "ORA-00904: "NTT": invalid identifier"

Note that I have declared a type in my schema as follows CREATE OR REPLACE TYPE Type_Varchar2_4000 AS TABLE OF VARCHAR2(4000)

How can I get the dynamic sql to recognise ntt and use its contents?


  PROCEDURE testmemberofprc(

            po_ref_cursor OUT RefCursor)   IS
    --TYPE alc_tab IS TABLE OF aggregatelevelright.aggregatelevelcode %TYPE INDEX BY PLS_INTEGER;
    ntt type_varchar2_4000;

    v_sql VARCHAR2(2000);
    v_errcode VARCHAR2(2000);

  BEGIN     ntt := type_varchar2_4000('A', 'B', 'C');

    v_sql := 'INSERT INTO ks(ks_varchar2) ' ||

'SELECT ks_varchar2 ' ||
'FROM ks ' ||
'WHERE ks_varchar2 MEMBER OF ntt';
OPEN po_ref_Cursor FOR v_sql;


       v_errcode := Sqlerrm;

  END testmemberofprc; Received on Tue Jan 27 2009 - 08:32:25 CST

Original text of this message