Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: EXECUTE IMMEDIATE + BULK COLLECT without knowing in advance the number of columns fetched ?
codadilupo wrote:
> Perhaps I did't explained me too well. My question was: how can I code a
> SELECT statement using EXECUTE IMMEDIATE ... BULK COLLECT INTO if I
> don't know in advance the number of columns to fetch? (I can only assume
> they are all NUMBERs).
OK, I think I solved it:
SQL> create table mydata (a number, b number, c number) ;
Table created.
SQL> insert into mydata values(42,84,19);
1 row created.
SQL> insert into mydata values(21,18,3);
1 row created.
SQL> insert into mydata values(6,8,0);
1 row created.
SQL> create type NumberArrayType as table of number ; 2 /
Type created.
SQL> create type NumberMatrixType as table of NumberArrayType; 2 /
Type created.
SQL> get x.sql
1 declare
2 x NumberMatrixType ;
3 begin
4 execute immediate 'select numberArrayType(a,b,c) from mydata'
bulk collect into x ;
5 for i in x.first..x.last loop
6 dbms_output.put_line('--------------'); 7 dbms_output.put_line('1:'||x(i)(1)) ; 8 dbms_output.put_line('2:'||x(i)(2)) ; 9 dbms_output.put_line('3:'||x(i)(3)) ;10 end loop ;
1:42 2:84 3:19
1:21 2:18 3:3
1:6 2:8 3:0
PL/SQL procedure successfully completed.
-- CdLReceived on Fri Sep 14 2007 - 08:51:42 CDT
![]() |
![]() |