Use dbms_sql to process query, cursor and value
SQL> create table myTable ( x number ) pctfree 0;
Table created.
SQL>
SQL> insert into myTable select rownum from all_objects where rownum < 100;
SQL>
SQL> create or replace procedure ARRAY_PROCESS is
2 s integer := dbms_sql.open_cursor;
3 n1 dbms_sql.number_table;
4 d number;
5 c number;
6 BEGIN
7 dbms_sql.parse(s,'select * from myTable', DBMS_SQL.native);
8 dbms_sql.define_array(s,1,n1,500,1);
9 d := dbms_sql.execute(s);
10 loop
11 c := DBMS_SQL.FETCH_ROWS(s);
12 DBMS_SQL.COLUMN_VALUE(s, 1, n1);
13 exit when c < 500;
14 end loop;
15
16 DBMS_SQL.CLOSE_CURSOR(s);
17 END;
18 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Related examples in the same category