Sayantan's Blog On Oracle

Oracle Collection In SQL

Collection in SQL queries:

Only Nested Tables and Varrays can be used in Sql queries.

Because these are the persistent collections, so can be stored in database.

Nested Tables In SQL:

Creating Nested Table Object:

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:

select TYPE_NAME, 
       TYPE_OID, 
       TYPECODE, 
       ATTRIBUTES, 
       METHODS, 
       PREDEFINED, 
       INCOMPLETE, 
       FINAL, 
       INSTANTIABLE
from user_types
where type_name = 'ADDR_TYPE';

select TYPE_NAME, 
       TYPE_OID, 
       TYPECODE, 
       ATTRIBUTES, 
       METHODS, 
       PREDEFINED, 
       INCOMPLETE, 
       FINAL, 
       INSTANTIABLE
from user_types
where type_name = 'ADDR_TAB';

Creating Nested Tables:

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 TABLE_NAME, 
       TABLE_TYPE_OWNER, 
       TABLE_TYPE_NAME, 
       PARENT_TABLE_NAME, 
       PARENT_TABLE_COLUMN
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')));

Selecting data from Nested Tables:

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:

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
order by custid;

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:

delete table(select t.cust_address
from customer_add_info t, 
     table(t.cust_address) d
where t.custid = 100001
and d.state = 'Boston');

Varray in SQL:

Creating Varrays:

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';

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:

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;

Selecting data from a Varray column:

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');

Error:

RELATED TOPICS:

• COLLECTION IN PLSQL
• INTRODUCTION TO COLLECTION
• COLLECTION METHODS HANDS ON
• USING BULK OPERATION IN PLSQL
• USING BULK BINDING IN ORACLE