How to access data from nested tables using SELECT queries?

Nested table is Oracle, How to access the data from nested table in Oracle, SELECT queries in Oracle to access nested table data, TABLE() function in Oracle, Unnesting the nested table data

How to access data from nested tables using SELECT queries?

Nested tables can be accessed using TABLE( ) function as follows; let us consider the table from the previous post here;  

CREATE TABLE Bills ( Cust_ID CHAR(10), Name VARCHAR(30), Phone NUMBER(10), Bill_Date DATE, Billed_Items Items_Purchased_Tab ) NESTED TABLE Billed_Items STORE AS Billed_Items_Tab;

Note: All words in ALL CAPS are keywords.

In the above table, Billed_Items is the column that is of type Nested table.

To display all the records from bills table, the query is as follows;

Query 1

SELECT * FROM Bills;

The result will be;

Cust_ID               Name                    Phone                   Bill_Date             ----------- ----------- ----------- ---------------- Billed_Items(Item_ID, Quantity, Price) ------------------------------------------------------ C101                      Raghu                   9878676500         04-JUN-2016 Items_Purchased_Tab(Items_Purchased_Typ(‘I101’, 2, 300.00), Items_Purchased_Typ(‘I106’, 2, 30.00), Items_Purchased_Typ(‘I110’, 1, 500.00))

To access individual attributes of nested table, we can write the query as follows;

Query 2

SELECT p.* FROM Bills b, TABLE(b.Billed_Items) p;

In this query, the function TABLE ( ) will unnest (flatten) the nested table column Billed_Items for every record. For example, in our table, we have one record with customer id C101 and that customer has billed three items. Now the result of TABLE (b.Billed_Items) will be as follows (the nested table is flattened);

Item_ID	Quantity	Price
I101	2	300.00
I106	2	30.00
I110	1	500.00

Here, the headings Item_ID, Quantity and Price are the attributes of the type Items_Purchased_Typ. Refer here.

Hence the result of the Query 2 is as follows;

Item_ID               Quantity                              Price ------------                ------------                -------------------- I101                       2                              300.00 I106                       2                              30.00 I110                       1                              500.00

*****************

 

Go to Collection Types page

Go to Object database systems page

Go to Nested Tables page