dbms_bind

set serveroutput on format wrapped

create or replace type numbers as table of number
/

declare
  list dbms_sql.Number_Table;
  txt varchar2(4000) := 'select dba_objects.object_id from '||
    'dba_objects where object_id  = :1 ';

  hnd integer := dbms_sql.open_cursor;
  res integer;
  val integer;
begin
  for i in 1 .. 10 loop
    list(i) := i;
    dbms_output.put_line(i);
  end loop;
  dbms_sql.parse(hnd, txt, dbms_sql.native);
  dbms_sql.define_column(hnd, 1, val);
  dbms_sql.bind_array(hnd,':1',list,1,10);
  res := dbms_sql.execute_and_fetch(hnd);

  while res <> 0 loop
    dbms_sql.column_value(hnd, 1, val);
    dbms_output.put_line(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;
/