Re: PL/SQL Arrays

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 04 Jul 1999 16:44:09 GMT
Message-ID: <37818d5b.13683626@newshost.us.oracle.com>

A copy of this was sent to jeperkins4_at_my-deja.com (if that email address didn't require changing) On Sat, 03 Jul 1999 22:14:04 GMT, you wrote:

>
>>

[snip]

>
>Thanks for the answer! How could I pass this "bulk collection" over to
>Java via 8i/SQLJ via a PL/SQL Stored procedure?
>

With a SQL Select

It would in general look like this:

SQL> create or replace type myScalartype as Object

  2  (       username        varchar2(30),
  3          user_id         number

  4 );
  5 /
Type created.

SQL> create or replace type myTableType as table of myScalarType;   2 /
Type created.

SQL> create or replace package myTableTypePkg   2 as
  3 procedure init( p_x in number );   4

  5       function get return myTableType;
  6       pragma restrict_references( get, WNDS, WNPS );
  7  
  8       pragma restrict_references( myTableTypePkg, wnds, rnds, wnps, rnps );

  9 end;
 10 /
Package created.

SQL> create or replace package body myTableTypePkg   2 as
  3
  4 g_x myTableType;
  5
  6
  7 procedure init( p_x in number )
  8 is
  9 begin
 10

 11          select cast( multiset( select username, user_id
 12                                   from all_users where rownum < p_x )
 13                                   AS myTableType )
 14            into g_x
 15            from dual;

 16 end;
 17
 18
 19 function get return myTableType
 20 is
 21 begin
 22 return g_x;
 23 end;
 24
 25 end;
 26 /
Package body created.

SQL> exec myTableTypePkg.init(20)
PL/SQL procedure successfully completed.

SQL> select *
  2 from THE ( select cast( myTableTypePkg.get as mytableType ) from dual ) a   3 /

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
SYSTEM                                  5
DOD                                    89
CSN                                    90
DBSNMP                                 17
TRACESVR                               19
RTS                                    91
DVS                                    21
WEBSYS                                 23
O8TRAIN                                26
SCOTT                                  25
RTS98                                  36
PS_ADMIN                               71
PS_MASTER                              70
PS_PMT                                 72
MANISH                                 87
BOB                                    85
SEAN                                   86
WEBVIEW                                83

19 rows selected.

>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jul 04 1999 - 11:44:09 CDT