Sayantan's Blog On Oracle

How to store string array in Oracle DB?

There are 2 types of array which are persistent in type. So they can be stored in Oracle DB.

• Nested Table
• Varray

Nested Table in SQL:

Creating Nested Table Object:

--How to store string array in Oracle DB? : Example

CREATE OR REPLACE TYPE ADDR_TYPE
AS OBJECT
(STATE VARCHAR2(100),
 CITY  VARCHAR2(100)
);
/

CREATE OR REPLACE TYPE ADDR_TAB AS TABLE OF ADDR_TYPE;
/

Verifying Nested Table Object Data Dictionary:

--How to store string array in Oracle DB? : View data

SELECT TYPE_NAME, TYPE_OID, TYPECODE, ATTRIBUTES, PREDEFINED, INCOMPLETE, FINAL, INSTANTIABLE 
FROM USER_TYPES
WHERE TYPE_NAME IN ('ADDR_TYPE', 'ADDR_TAB');

Creating Nested Tables:

--How to store string array in Oracle DB? : Example

CREATE TABLE CUSTOMER_ADD_INFO
(CUSTID       NUMBER(6),
 CUST_NAME    VARCHAR2(100),
 CUST_ADDRESS ADDR_TAB
)
NESTED TABLE CUST_ADDRESS STORE AS CUST_ADD;

Verifying Nested Table Data Dictionary:

SELECT * FROM USER_NESTED_TABLES
WHERE TABLE_NAME = 'CUST_ADD';

Inserting Data into Nested Tables:

INSERT INTO CUSTOMER_ADD_INFO
VALUES(100001, 'Steven Smith', ADDR_TAB(ADDR_TYPE('New York','New York')));
INSERT INTO CUSTOMER_ADD_INFO
VALUES(100002, 'Jhon Smith', ADDR_TAB(ADDR_TYPE('California','California')));

SELECT * FROM CUSTOMER_ADD_INFO;

Above data from CUST_ADDRESS column are not user readable format. To convert it user readable format user below query:

--How to store string array in Oracle DB? : View data

SELECT A.CUSTID, 
       A.CUST_NAME, 
       B.STATE, 
       B.CITY
FROM CUSTOMER_ADD_INFO A, 
     TABLE(A.CUST_ADDRESS) B;

Update a specific record in Nested table column:

--How to store string array in Oracle DB? : Update data

UPDATE TABLE(SELECT CUST_ADDRESS
             FROM CUSTOMER_ADD_INFO
             WHERE CUSTID = 100001
            ) T
SET T.STATE = 'New York State',
    T.CITY = 'New York City';

SELECT A.CUSTID, A.CUST_NAME, B.STATE, B.CITY
FROM CUSTOMER_ADD_INFO A, TABLE(A.CUST_ADDRESS) B;

Further Update, Insert and verify data from Nested Tables:

INSERT INTO CUSTOMER_ADD_INFO VALUES(100001, 'Steven Smith', ADDR_TAB(ADDR_TYPE('Boston','Boston')));

SELECT A.CUSTID, 
       A.CUST_NAME, 
       B.STATE, 
       B.CITY
FROM CUSTOMER_ADD_INFO A, 
     TABLE(A.CUST_ADDRESS) B;

SELECT A.CUSTID, A.CUST_NAME, B.STATE, B.CITY
FROM CUSTOMER_ADD_INFO A, TABLE(A.CUST_ADDRESS) B
WHERE A.CUSTID = 100001
ORDER BY CUSTID;

Delete a specific record from Nested Table:

--How to store string array in Oracle DB? : Delete data

DELETE TABLE(SELECT T.CUST_ADDRESS
FROM CUSTOMER_ADD_INFO T, TABLE(T.CUST_ADDRESS) D
WHERE T.CUSTID = 100001
AND D.STATE = 'Boston');

SELECT A.CUSTID, A.CUST_NAME, B.STATE, B.CITY
FROM CUSTOMER_ADD_INFO A, TABLE(A.CUST_ADDRESS) B
WHERE A.CUSTID = 100001
ORDER BY CUSTID;

Varray in SQL:

CREATE OR REPLACE TYPE ADDR_TAB_VA
IS VARRAY(10) OF ADDR_TYPE;
/

Verifying Varrays from Data Dictionary:

SELECT TYPE_NAME, TYPE_OID, TYPECODE, ATTRIBUTES, PREDEFINED, INCOMPLETE, FINAL, INSTANTIABLE
FROM USER_TYPES
WHERE TYPE_NAME = 'ADDR_TAB_VA';

Creating Table with Varray column:

--How to store string array in Oracle DB? : Example

CREATE TABLE CUSTOMER_ADD_INFO_VA
(CUSTID       NUMBER(6),
 CUST_NAME    VARCHAR2(100),
 CUST_ADDRESS ADDR_TAB_VA
);

Inserting data into table with Varray column:

INSERT INTO CUSTOMER_ADD_INFO_VA
VALUES(100001, 'Steven Smith', ADDR_TAB_VA(ADDR_TYPE('New York','New York')));
INSERT INTO CUSTOMER_ADD_INFO_VA
VALUES(100002, 'Jhon Smith', ADDR_TAB_VA(ADDR_TYPE('California','California')));

Selecting data from table with Varray column:

SELECT * FROM CUSTOMER_ADD_INFO_VA;

Above CUST_ADDRESS column is not in readable format. To convert it in user readable format use the below query:

--How to store string array in Oracle DB? : View data

SELECT A.CUSTID, 
       A.CUST_NAME, 
       B.STATE, 
       B.CITY
FROM CUSTOMER_ADD_INFO_VA A, 
     TABLE(A.CUST_ADDRESS) B
ORDER BY CUSTID;

Update a specific record in Varray column:

UPDATE CUSTOMER_ADD_INFO_VA
SET CUST_ADDRESS = ADDR_TAB_VA(ADDR_TYPE('New York State', 'New York City'))
WHERE CUSTID = 100001;

SELECT A.CUSTID, 
       A.CUST_NAME, 
       B.STATE, 
       B.CITY
FROM CUSTOMER_ADD_INFO_VA A, 
     TABLE(A.CUST_ADDRESS) B
ORDER BY CUSTID;

Further Insert and Query data from table with Varray:

INSERT INTO CUSTOMER_ADD_INFO_VA
VALUES(100001, 'Steven Smith', ADDR_TAB_VA(ADDR_TYPE('Boston','Boston')));

SELECT A.CUSTID, 
       A.CUST_NAME, 
       B.STATE, 
       B.CITY
FROM CUSTOMER_ADD_INFO_VA A, 
     TABLE(A.CUST_ADDRESS) B
ORDER BY CUSTID;

Can not perform Delete operation on a Varray:

DELETE TABLE(SELECT T.CUST_ADDRESS
             FROM CUSTOMER_ADD_INFO_VA T, 
                  TABLE(T.CUST_ADDRESS) D
             WHERE T.CUSTID = 100001
             AND D.STATE = 'Boston'
            );

RELATED TOPICS:

• Oracle Collection in PLSQL