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

From: Kevin S <SearleK_at_googlemail.com>
Date: Tue, 27 Jan 2009 06:32:25 -0800 (PST)
Message-ID: <65e54329-9424-4cf0-806e-523aaa4cf28f_at_p2g2000prf.googlegroups.com>



All,

I am doing the following work in oracle 10.2.0.1

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?

Thanks

  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;

  EXCEPTION
  WHEN OTHERS

       THEN
       v_errcode := Sqlerrm;
       RAISE;

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

Original text of this message