dbms_sql.varchar2s

************************************************************************************************* 

declare
  v_sql   dbms_sql.varchar2s;
  c       integer;
  s       integer;
  v_empno dbms_sql.Number_Table;
  v_ename dbms_sql.Varchar2_Table;
  v_sal   dbms_sql.Number_Table;
  r       number;
begin
  v_sql(1) := 'select e.empno,
                     e.ename,
                     e.sal';
  v_sql(2) := 'from emp e';

  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, v_sql, v_sql.first, v_sql.last, true, dbms_sql.native);
  /* dbms_sql.define_column(c, 1, v_empno);
  dbms_sql.define_column(c, 2, v_ename, 10);
  dbms_sql.define_column(c, 3, v_sal);*/
  dbms_sql.define_array(c, 1, v_empno, 5, 1);
  dbms_sql.define_array(c, 2, v_ename, 5, 1);
  dbms_sql.define_array(c, 3, v_sal, 5, 1);
 
  s := dbms_sql.execute(c);

  loop
    r := dbms_sql.fetch_rows(c);
    dbms_output.put_line('fetch rows is ' || r);
 
    exit when r = 0;
    dbms_sql.column_value(c, 1, v_empno);
    dbms_sql.column_value(c, 2, v_ename);
    dbms_sql.column_value(c, 3, v_sal);
    for i in 1 .. r loop
      dbms_output.put_line(v_empno(i));
      -- dbms_output.put_line(n_tab(i) || ',' || n_tab1(i));
    end loop;
 
  end loop;
  dbms_sql.close_cursor(c);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

 

=============================================================================

declare
  v_sql   dbms_sql.varchar2a;
  c       integer;
  s       integer;
  v_empno number;
  v_ename Varchar2(10);
  v_sal   Number;
  r       number;
begin
  v_sql(1) := 'select e.empno,
                     e.ename,
                     e.sal';
  v_sql(2) := 'from emp e where e.empno = :no';

  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, v_sql, v_sql.first, v_sql.last, true, dbms_sql.native);
  dbms_sql.bind_variable(c,'no',7369);
  /* dbms_sql.define_column(c, 1, v_empno);
  dbms_sql.define_column(c, 2, v_ename, 10);
  dbms_sql.define_column(c, 3, v_sal);*/
  dbms_sql.define_column(c, 1, v_empno);
  dbms_sql.define_column(c, 2, v_ename,10);
  dbms_sql.define_column(c, 3, v_sal);
 
  s := dbms_sql.execute(c);

  loop
    r := dbms_sql.fetch_rows(c);
    dbms_output.put_line('fetch rows is ' || r);
 
    exit when r = 0;
    dbms_sql.column_value(c, 1, v_empno);
    dbms_sql.column_value(c, 2, v_ename);
    dbms_sql.column_value(c, 3, v_sal);
    dbms_output.put_line(v_empno);
   /* for i in 1 .. r loop
      dbms_output.put_line(v_empno(i));
      -- dbms_output.put_line(n_tab(i) || ',' || n_tab1(i));
    end loop;*/
 
  end loop;
  dbms_sql.close_cursor(c);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;