How to store string array in Oracle DB?

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: