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.
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.
The above statement has been explained in detail in the last tutorial which you can check here.
Now that we have created the nested table using user-define datatype it’s time for putting it to some work.
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.
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.
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.
That’s it for this tutorial hope you enjoyed and learnt something new. Make sure to subscribe and signup. Have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.