use virtual table in PL/SQL block
SQL>
SQL>
SQL> create or replace type virtual_table_type as table of number
2 /
Type created.
SQL> create or replace
2 function virtual_table( p_start number,
3 p_end number ) return virtual_table_type as
4 l_vt_type virtual_table_type := virtual_table_type();
5 begin
6 for i in p_start .. p_end loop
7 l_vt_type.extend();
8 dbms_output.put_line( 'adding ' || i || ' to collection...' );
9 l_vt_type(l_vt_type.count) := i;
10 end loop;
11 dbms_output.put_line( 'done...' );
12 return l_vt_type;
13 end virtual_table;
14 /
Function created.
SQL> set serveroutput on
SQL> begin
2 for x in ( select *
3 from table( virtual_table( -2, 2) ) )
4 loop
5 dbms_output.put_line( 'printing from anonymous block ' ||
6 x.column_value );
7 end loop;
8 end;
9 /
adding -2 to collection...
adding -1 to collection...
adding 0 to collection...
adding 1 to collection...
adding 2 to collection...
done...
printing from anonymous block -2
printing from anonymous block -1
printing from anonymous block 0
printing from anonymous block 1
printing from anonymous block 2
PL/SQL procedure successfully completed.
Related examples in the same category