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;

Hi all.

Sadly it does not appear to bepossible to edit posts once they are on
line and so as I now realised I have posted this code with the line

'INSERT INTO ks(ks_varchar2) ' ||

which I meant to take out so please igore it.

5 minutes after posting this I foud a solution.

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 := 'SELECT ks_varchar2 ' ||
'FROM ks ' ||
'WHERE ks_varchar2 MEMBER OF :ntt ';

OPEN po_ref_Cursor FOR v_sql USING ntt;

EXCEPTION
WHEN OTHERS
THEN
v_errcode := Sqlerrm;
RAISE;
END testmemberofprc;