How to use VARRAY/Nested table as Data Type in Table Creation or Package level

Let's see following examples to understand VARRAY and NESTED TABLE concepts in table creation .


SQL> CREATE OR REPLACE TYPE Mobile_VARRAY IS VARRAY(2) OF INTEGER;
  2  /

Type created

SQL> CREATE TABLE CUST_new( C_ID INTEGER,Phone Mobile_VARRAY);

Table created

SQL> insert into CUST_new values('101',Mobile_VARRAY(9164841212,9164841213));

1 row inserted

SQL> insert into CUST_new values('102',Mobile_VARRAY(9164841212,9164841213));

1 row inserted

SQL> insert into CUST_new values('103',Mobile_VARRAY(9164841212,9164841213));

1 row inserted

SQL> select * from cust_new;

                                   C_ID PHONE
--------------------------------------- -----
                                    101 <Obje
                                    102 <Obje
                                    103 <Obje





You may wanted to query this table to see all information ,then you need to know about Table Function Concept in Oracle.

Ref : For Table Function concept https://myplsqltutorial.blogspot.in/2017/02/table-function-in-oracle.html

 SQL> select c_id, column_value as Phone_Number from CUST_new c1,table(c1.Phone) c2;

                                   C_ID PHONE_NUMBER
--------------------------------------- ------------
                                    101   9164841212
                                    101   9164841213
                                    102   9164841212
                                    102   9164841213
                                    103   9164841212
                                    103   9164841213

6 rows selected 



There may be scenario's like to have store multiple address for single customer or multiple information for any particular column then we can do as below.

So we ill take scenario  to have multiple account details for single customer.

Now Design column for account details .
It can be
1) Type of account 2) account number 3) Customer_id 4) Branch 5) Location 

Based on above design ,lets create object type to hold all information in single column.

CREATE OR REPLACE TYPE ACC_DETAILS IS OBJECT
(Type_Acc Varchar2(100), acc_number integer, C_id Varchar2(100),
Branch Varchar2(100),Location Varchar2(100));

 

We had already know how to create object type .
Ref for Creating object type: https://myplsqltutorial.blogspot.in/2017/02/how-to-create-object-type-in-sql.html

Now our main task is to hold multiple account details per single Customer.

so lets create Nested table with Object type to achive this.

CREATE OR REPLACE TYPE   ACC_DETAILS_TABLE IS TABLE OF ACC_DETAILS;

Now create table which holds customer information.

CREATE TABLE CUSTOMER (Id Integer,Name Varchar2(100),Acc_Det  ACC_DETAILS_TABLE)
NESTED TABLE  Acc_Det STORE AS ACC_DETAILS_TABLE_FIELD;

Eg:

SQL> CREATE OR REPLACE TYPE ACC_DETAILS IS OBJECT
  2  (
  3    Type_Acc   Varchar2(100),
  4    acc_number integer,
  5    C_id       Varchar2(100),
  6    Branch   Varchar2(100),
  7    Location Varchar2(100));
  8  /

Type created

SQL> CREATE OR REPLACE TYPE   ACC_DETAILS_TABLE IS TABLE OF ACC_DETAILS;
  2  /

Type created

SQL>
SQL> CREATE TABLE CUSTOMER (Id Integer,Name Varchar2(100),Acc_Det  ACC_DETAILS_TABLE)
  2  NESTED TABLE  Acc_Det STORE AS ACC_DETAILS_TABLE_FIELD
  3  /

Table created

SQL>
SQL> INSERT INTO CUSTOMER VALUES(101,'VENKY',ACC_DETAILS_TABLE(ACC_DETAILS('Current',12121212,'A101','Marathahalli','Bangalore'),
  2                                                            ACC_DETAILS('Savings',13131313,'A101','Marathahalli','Bangalore'),
  3                                                            ACC_DETAILS('PPF_Acc',14141414,'A101','Marathahalli','Bangalore'),
  4                                                            ACC_DETAILS('Loan_Acc',15151515,'A101','Marathahalli','Bangalore'),
  5                                                            ACC_DETAILS('Deposit_Acc',16161616,'A101','Marathahalli','Bangalore')));

1 row inserted

SQL> SELECT * FROM CUSTOMER;

                                     ID NAME                                                                             ACC_DET
--------------------------------------- -------------------------------------------------------------------------------- -------
                                    101 VENKY                                                                            <Object




let's below command to get full information.