Hey guys! Today we will learn how to create nested table type collection using user-define datatype. Hope you had great time with the last tutorial where we learnt the creation process of nested table with primitive datatype. I highly suggest you to take a look at that tutorial as we are going use the concepts from there.

Similar to primitive datatype a nested table can be created using user define datatypes also. For the demonstration we will use user Oracle Object. Objects require no introduction, if you have ever studied OOP Concepts. In Oracle, like other programming languages, object type is a kind of a datatype which works in the same way as other datatypes such as Char, Varchar2, Number etc. but with more flexibility.

To create an Oracle Object we use our old and trusty ‘Create Type’ statement.

CREATE OR REPLACE TYPE object_type AS OBJECT (
  obj_id  NUMBER,
  obj_name  VARCHAR2(10)
);
/

The above statement will create an oracle object with the name ‘object type’ with two attributes obj_id and obj_name on successful execution. This datatype then can be used to create a nested table.

CREATE OR REPLACE TYPE My_NT IS TABLE OF object_type;
/

I think if you checked the last tutorial then you’ll find this above statement very familiar, except the element type which is a primitive datatype there. Here we use a user-define datatype which is an Oracle Object.

The above statement has been explained in detail in the last tutorial which you can check here.

What does creating a nested table using Oracle Object means?

Whenever you create a nested table using an Oracle Object then the attributes of the Object become the columns of that table. For example, in our case we created a nested table ‘My_NT’ using the user-define datatype which is an Oracle Object ‘Object_Type’ which has two attributes obj_id and obj_name. These two attributes of the object will act as the columns of the table. The following pic will help you in understanding this more clearly.

how to create nested table using user-define datatype by manish sharma

Now that we have created the nested table using user-define datatype it’s time for putting it to some work.

CREATE TABLE Base_Table(
  tab_id  NUMBER,
  tab_ele My_NT
)NESTED TABLE tab_ele STORE AS stor_tab_1;
/

The above table named ‘Base_Table’ is a simple one which has 2 columns ‘tab_id’ and ‘tab_ele’. The first column is of Number Datatype while second column is of Nested Table type. This means that the second column contains a table into it and that table is our Nested table ‘My_Nt’

Though this ‘Base_Table’ is a simple table but one of its column contains a nested table into it which arises some questions such as:

How to insert data into the table? How to update the data of the table? Or how to retrieve the data from the table? Let’s try to find out the answers to all these questions one at a time.

How to insert data into the nested table?

Yes, I agree that inserting data into a table which has a column of nested table type can be tricky but somehow we have to find the way of doing it. As a table without data is of no use to us. Right? Let’s see how we can do that.

 INSERT INTO base_table (tab_id, tab_ele) VALUES
 (801,  -- value for 1st colum 
   My_NT (object_type (1,'Superman') -- values for 2nd column )
 );

As you can see in this INSERT statement everything is same as a normal Insert DML except the line number 3 where we are inserting data into the second column of the table. In order to insert data into the column which is of Nested Table type you first have to write the name of your nested table which in this case is ‘My_NT’ then you have to write the name of your Oracle Object which here is ‘Object_Type’ followed by the values you want to insert into your table. Don’t forget to match the parenthesis for table name and object name, otherwise you will get an error.

How to update values of the nested table?

UPDATE base_table SET tab_ele = My_NT(object_type(1,'SpiderMan')) WHERE tab_id = 801; 

The above DML statement will update the values accordingly on successful execution.

How to retrieve data from the nested table?

You can simply execute the Select statement on your table to get the data.

Select tab_id, tab_ele FROM base_table;

The following picture will show you the result returned from this table.

how to create nested tablke using user-define datatype by manish sharma

As you can see this SELECT statement will show you the data from the columns which are of primary datatype but only the name of your nested table along with the Oracle Object from the column which you define as a Nested Table type. You can easily overcome this problem by using TABLE expression like this.

SELECT * FROM TABLE(
  SELECT tab_ele FROM Base_Table WHERE tab_id = 801
)

Successful execution of the above query will show you the data from second column of your table ‘Base_Table’ in a relational format.

how to create nested table using user define datatype by manish sharma

That’s it for this tutorial hope you enjoyed and learnt something new. Make sure to subscribe and signup. Have a great day!