By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create type t_tab as table of varchar2(1)
/
declare
table_hrcs t_tab := new t_tab('X');
v_hrc_list t_tab := new t_tab();
query_statement varchar2(100);
v_c1 sys_refcursor;
begin
query_statement := 'select * from dual WHERE dummy MEMBER OF (select * from table (:table_hrcs))';
OPEN v_c1 FOR query_statement USING table_hrcs;
LOOP
v_hrc_list.extend;
FETCH v_c1 into v_hrc_list(v_hrc_list.count);
EXIT WHEN (v_c1%NOTFOUND);
END LOOP;
CLOSE v_c1;
dbms_output.put_line(v_hrc_list.count);
end;
/
ORA-00932: inconsistent datatypes: expected UDT got CHAR
ORA-06512: at line 8
declare
table_hrcs t_tab := new t_tab('X');
v_hrc_list t_tab := new t_tab();
query_statement varchar2(100);
v_c1 sys_refcursor;
begin
query_statement := 'select * from dual WHERE dummy IN (select * from table (:table_hrcs))';
OPEN v_c1 FOR query_statement USING table_hrcs;
LOOP
v_hrc_list.extend;
FETCH v_c1 into v_hrc_list(v_hrc_list.count);
EXIT WHEN (v_c1%NOTFOUND);
END LOOP;
CLOSE v_c1;
dbms_output.put_line(v_hrc_list.count);
end;
/
1 rows affected
dbms_output:
2
declare
table_hrcs t_tab := new t_tab('X');
v_hrc_list t_tab := new t_tab();
query_statement varchar2(100);
v_c1 sys_refcursor;
begin
query_statement := 'select * from dual WHERE dummy MEMBER OF :table_hrcs';
OPEN v_c1 FOR query_statement USING table_hrcs;
LOOP
v_hrc_list.extend;
FETCH v_c1 into v_hrc_list(v_hrc_list.count);
EXIT WHEN (v_c1%NOTFOUND);
END LOOP;
CLOSE v_c1;
dbms_output.put_line(v_hrc_list.count);
end;
/
1 rows affected
dbms_output:
2
declare
table_hrcs t_tab := new t_tab('X');
v_hrc_list t_tab;
query_statement varchar2(100);
v_c1 sys_refcursor;
begin
query_statement := 'select * from dual WHERE dummy MEMBER OF :table_hrcs';
EXECUTE IMMEDIATE query_statement
BULK COLLECT INTO v_hrc_list
USING table_hrcs;
dbms_output.put_line(v_hrc_list.count);
end;
/
1 rows affected
dbms_output:
1