Re: EXECUTE IMMEDIATE + BULK COLLECT without knowing in advance the number of columns fetched ?

From: codadilupo <codadilupo_at_operamail.com>
Date: Fri, 14 Sep 2007 15:51:42 +0200
Message-ID: <46ea91ed$0$21208$5fc30a8@news.tiscali.it>

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 ;
 11* end ;
 12 /

---

1:42
2:84
3:19

---

1:21
2:18
3:3

---

1:6
2:8
3:0

PL/SQL procedure successfully completed.

-- 
CdL

Received on Fri Sep 14 2007 - 08:51:42 CDT