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,
END testmemberofprc; Received on Tue Jan 27 2009 - 08:32:25 CST
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