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;