How to bind collection using dbms_sql in Oracle 9iR2?

From: Alexander Miroshnikov <alexander_miroshnikov_at_hotmail.com>
Date: 3 Apr 2003 05:24:02 -0800
Message-ID: <83192647.0304030524.452c7995@posting.google.com>

In static sql I can open a select cursor that uses a collection type

create or replace type numbers as table of number
/

declare
  list numbers := numbers(1,2,3,4,5,6,7,8,9,10); begin
  for f in (select dba_objects.object_id from dba_objects   where dba_objects.object_id in
    (select column_value as object_id from

       table(cast(list as numbers)))) loop     null; -- do something
  end loop;
end;
/

How is it possible to do bind collection type using dbms_sql?

declare
  list numbers := numbers(1,2,3,4,5,6,7,8,9,10);   txt varchar2(4000) := 'select dba_objects.object_id from '||     'dba_objects where dba_objects.object_id in '||

      '(select column_value as object_id '||
      '  from table(cast(:1 as numbers)))';

  hnd integer := dbms_sql.open_cursor;
  res integer;
  val integer;
begin

  dbms_sql.parse( hnd, txt, dbms_sql.native );
  dbms_sql.define_column( hnd, 1, val );
  dbms_sql.bind_variable( hnd, ':1', list );

-- ^^ this line causes PLS-00306, which is - -- "wrong number or types of arguments"
  res := dbms_sql.execute_and_fetch( hnd );   while res <> 0 loop
    dbms_sql.column_value( hnd, 1, val );     null; -- do something
    res := dbms_sql.fetch_rows( hnd );
  end loop;
  dbms_sql.close_cursor( hnd );
exception
  when others then
    if dbms_sql.is_open( hnd ) then
      dbms_sql.close_cursor( hnd );
    end if;
    raise;
end;
/

I know that bind_variable( <integer>, <varchar2>, <collection> ) does not exist. I wonder if anybody managed to find a workaround?

Please help,

Many Thanks,
Alex Received on Thu Apr 03 2003 - 07:24:02 CST