Re: arrays in oracle , urgent!!!!!!!11

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Dec 1998 16:31:27 GMT
Message-ID: <3687b0d9.4069151@192.86.155.100>

A copy of this was sent to "Saul Mond" <smond_at_cyberdude.com> (if that email address didn't require changing) On Mon, 28 Dec 1998 16:23:46 GMT, you wrote:

>How do i create a array of numbers and assigned a value
>please email at smond_at_pcsiusa.com
>

Database version? Language/constraints involved? Thats a large question with little to go on...

Ok, assuming version 7 and you want an array in pl/sql, you could code:

create or replace package TYPES
as

    type numArray is table of number index by binary_integer; end;
/

create or replace procedure my_procedure as

   myArray types.numArray;
begin

   for i in 1 .. 10 loop

      myArray(i) := i;
   end loop;

   for i in 1 .. myArray.count loop

      dbms_output.put_line( myArray(i) );    end loop;
end;
/

Assuming Oracle8 and you wanted to create a table with an array of numbers as a column, it might look like:

SQL> create or replace type myNumArrayType as varray(255) of number   2 /

Type created.

SQL>
SQL> create table myTable

  2  (       x               int     primary key,
  3          data    myNumArrayType

  4 )
  5 /

Table created.

SQL>
SQL> insert into myTable values ( 1, myNumArrayType( 1, 2, 3, 4, 5 ) );

1 row created.

SQL> 
SQL> column data format a40
SQL> select x, data from myTable;

         X DATA
---------- ----------------------------------------
         1 MYNUMARRAYTYPE(1, 2, 3, 4, 5)

SQL>
SQL> begin

  2          for y in ( select * from myTable ) loop
  3                  dbms_output.put_line( y.x );
  4                  for i in 1 .. y.data.count loop
  5                          dbms_output.put_line( i || ' - ' || y.data(i) );
  6                  end loop;
  7          end loop;

  8 end;
  9 /
1

1 - 1
2 - 2
3 - 3
4 - 4
5 - 5



 

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 28 1998 - 10:31:27 CST