ORACLE : VARRAYS

VARRAYS IN ORACLE
Varrays (short for variable-size arrays) hold a fixed number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Note:
Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.

For Example

----------- CREATING VARRAY TYPE ------------
------- 10 rows of varchar2(25) column
CREATE OR REPLACE
TYPE TYPE_VARRAY AS VARRAY(10) OF VARCHAR2(25);
---------------------------------------------

--- CREATEING TABLE WITH VARRAY COLUMN ------
CREATE TABLE TBL_WITH_VARRAY
(
ID NUMBER
, ADDEDDATE DATE
, DATA TYPE_VARRAY
)
---------------------------------------------

DESC TBL_WITH_VARRAY
SELECT * FROM USER_TYPES

SELECT * FROM TBL_WITH_VARRAY

INSERT INTO TBL_WITH_VARRAY
(
ID
, ADDEDDATE
, DATA
) VALUES
(
1
, SYSDATE
, TYPE_VARRAY('1','2','3','4','5')
);
/
INSERT INTO TBL_WITH_VARRAY
(
ID
, ADDEDDATE
, DATA
) VALUES
(
2
, SYSDATE
, TYPE_VARRAY('A','B','C','D','E','F','G','H','I','J','K')
);
---[1]: (Error): ORA-22909: exceeded maximum VARRAY limit
INSERT INTO TBL_WITH_VARRAY
(
ID
, ADDEDDATE
, DATA
) VALUES
(
2
, SYSDATE
, TYPE_VARRAY('A','B','C','D','E','F','G','H','I','J')
);

SET SERVEROUTPUT ON;

DECLARE
TMP_VARRAY TYPE_VARRAY;
BEGIN
SELECT DATA INTO TMP_VARRAY FROM TBL_WITH_VARRAY WHERE ID = 2;
DBMS_OUTPUT.PUT_LINE('NO.OF RECORDS : ' || TMP_VARRAY.COUNT);
FOR CNT IN TMP_VARRAY.FIRST..TMP_VARRAY.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(TMP_VARRAY(CNT));
END LOOP;
END;
/

DECLARE
TMP_VARRAY TYPE_VARRAY;
BEGIN
SELECT DATA INTO TMP_VARRAY FROM TBL_WITH_VARRAY WHERE ID = 2;
DBMS_OUTPUT.PUT_LINE('NO.OF RECORDS : ' || TMP_VARRAY.COUNT);
FOR CNT IN REVERSE TMP_VARRAY.FIRST..TMP_VARRAY.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(CNT || ' : ' || TMP_VARRAY(CNT));
END LOOP;
END;

------- CREATING OBJECT TYPE --------
CREATE OR REPLACE
TYPE TYPE_OBJ_TEST AS OBJECT
(
CODE NUMBER
, VAL VARCHAR2(25)
);
-------------------------------------

----- CREATING VARRAY OF OBJECT -----
CREATE OR REPLACE
TYPE TYPE_VARRAY_OBJ IS VARRAY(10) OF TYPE_OBJ_TEST;
-------------------------------------

----- CREATING TABLE WITH OBJECT VARRAY ----
CREATE TABLE TBL_WITH_VARRAY1
(
ID NUMBER
, ADDEDDATE DATE
, DATA TYPE_VARRAY_OBJ
)
--------------------------------------------

DESC TBL_WITH_VARRAY1
SELECT * FROM TBL_WITH_VARRAY1

INSERT INTO TBL_WITH_VARRAY1
(
ID
, ADDEDDATE
, DATA
) VALUES
(
1
, SYSDATE
, TYPE_VARRAY_OBJ (TYPE_OBJ_TEST(91,'IND'), TYPE_OBJ_TEST(65,'SG'))
);

INSERT INTO TBL_WITH_VARRAY1
(
ID
, ADDEDDATE
, DATA
) VALUES
(
2
, SYSDATE
, TYPE_VARRAY_OBJ(TYPE_OBJ_TEST(91,'IND'), TYPE_OBJ_TEST(65,'SG'),TYPE_OBJ_TEST(11,'ABC'), TYPE_OBJ_TEST(22,'DEF'))
);

SET SERVEROUTPUT ON;

DECLARE
TMP_VARRAY TYPE_VARRAY_OBJ;
TMP_OBJ TYPE_OBJ_TEST;
BEGIN
SELECT DATA INTO TMP_VARRAY FROM TBL_WITH_VARRAY1 WHERE ID = 2;
DBMS_OUTPUT.PUT_LINE('NO.OF RECORDS : ' || TMP_VARRAY.COUNT);
FOR CNT IN TMP_VARRAY.FIRST..TMP_VARRAY.LAST
LOOP
TMP_OBJ := TMP_VARRAY(CNT);
DBMS_OUTPUT.PUT_LINE(TMP_OBJ.CODE || ' - ' || TMP_OBJ.VAL);
END LOOP;
END;

For Syntax and more information