Mega Code Archive
Use dbms_sql parse to bind a select statement to a cursor
<![CDATA[
SQL>
SQL>
SQL> create table foo (
2 a integer,
3 b dec( 9, 2 ),
4 c character varying( 30 ),
5 d national char( 3 )
6 )
7 /
Table created.
SQL> declare
2 l_cursor number := dbms_sql.open_cursor;
3 l_num dbms_sql.number_table;
4 l_var dbms_sql.varchar2_table;
5 l_ignore number;
6 l_cnt number;
7 begin
8 dbms_sql.parse( l_cursor,'select b, c from foo',dbms_sql.native );
9 dbms_sql.define_array( l_cursor, 1, l_num, 100, 1 );
10 dbms_sql.define_array( l_cursor, 2, l_var, 100, 1 );
11
12 l_ignore := dbms_sql.execute( l_cursor );
13 loop
14 dbms_output.put_line( 'About to attempt a fetch of 100 rows.' );
15 l_cnt := dbms_sql.fetch_rows( l_cursor );
16 dbms_output.put_line( 'Fetched ' || l_cnt || ' rows.' );
17
18 exit when l_cnt = 0;
19
20 dbms_sql.column_value( l_cursor, 1, l_num );
21 dbms_sql.column_value( l_cursor, 2, l_var );
22
23 for i in 1 .. l_num.count loop
24 dbms_output.put_line( 'N = ' || l_num(i) || ' V = ' || l_var(i) );
25 end loop;
26
27 exit when l_cnt < 100;
28
29 end loop;
30 dbms_sql.close_cursor( l_cursor );
31 end;
32 /
About to attempt a fetch of 100 rows.
Fetched 0 rows.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table foo;
Table dropped.
SQL> --
]]>