Create and populate Varray in Oracle SQL

Question

I'm trying to créate a Varray of beans type and populate it, but I'm in a hurry and don't find any usefull example.

arr=[[1,'A'],[2,'B'],[3,'C']]

This is my code:

 create table my_table (NUM_OPERACIO NUMBER,TITULS varchar2(3)) ;
 insert into my_table values(1,'A');
 insert into my_table values(2,'B');
 insert into my_table values(3,'C');

 create TYPE item IS object( NUM_OPERACIO NUMBER, TITULS varchar2(3)); 
 /
 create TYPE arr IS VARRAY(10) OF item;
 /

 insert into arr values( select NUM_OPERACIO, TITULS from my_table);

 FOR i IN 1..3 loop
     dbms_output.put_line(arr (i));
 END loop;

Help me achive this, please.

Thanks in advance

score 2 · Accepted Answer · answered Dec 15 '16 at 11:30

Oracle Setup:

create table my_table (NUM_OPERACIO NUMBER,TITULS varchar2(3)) ;
insert into my_table values(1,'A');
insert into my_table values(2,'B');
insert into my_table values(3,'C');

CREATE TYPE item IS object( NUM_OPERACIO NUMBER, TITULS varchar2(3)); 
/
CREATE TYPE item_array IS VARRAY(10) OF item;
/

PL/SQL:

DECLARE
  arr item_array;
BEGIN
  SELECT item( NUM_OPERACIO,TITULS )
  BULK COLLECT INTO arr
  FROM   my_table;

  FOR i IN 1..arr.COUNT loop
    dbms_output.put_line(arr(i).NUM_OPERACIO || ', ' || arr(i).TITULS);
  END loop;
END;
/

Create and populate Varray in Oracle SQL

1 Answers1