How Nested Tables can be stored and retrieved in a Normal table-ORACLE PL/SQL

Author:VibinDas        

 As we all know, Nested Tables (especially the Oracle Collections) can be considered as one of the advanced concepts in Oracle. In many instances we may have to deal with Nested tables and Varrays in our coding where the requirement may be to save and retrieve a set or a bundle of values in database columns. One of the benefits of using this way is that, if we have multiple values to be stored corresponding to one subject or entity, these multiple values of each particular subject can be treated as a single entity and stored as a single value in database table.

Sometimes this introduction may bring a little bit of complexity in your mind; but the real fact is that, this is a very simple and interesting topic which you are going to enjoy for sure.

Let’s start with an example on this. Consider a STUDENT table which stores the information like the student id, first name, last name, his class etc... It’s very pretty simple as below.

Step 1 : First create a test table as below.

CREATE TABLE TEMP_STUDENT_DTLS

(

   STUD_ID NUMBER,

   STUD_NAME VARCHAR2(30),

   STUD_CLASS NUMBER

);

Step 2 : Insert some entries to it.

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(100, 'JAMES', 10);

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(200, 'ALBERT', 8);

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(300, 'MIKE', 5);

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(400, 'THOMAS', 2);

COMMIT;

**** 4 Rows inserted and committed.

Step 3 : Verifying the inserted records.

STUD_ID	STUD_NAME	STUD_CLASS
100	JAMES	10
200	ALBERT	8
300	MIKE	5
400	THOMAS	2

 So far everything is fine here. No weird things. But it’s going to come soon.

Suppose the requirement has got changed slightly and the additional thing is as follows

·         Need to store the subject list and corresponding mark of each student as well in the table.

Do you see anything fishy here? If each of the students are having same number of subjects, then it would been somewhat possible -  for example, if there are only 3 subjects for all students, we could have created 6 more columns such as SUB1, MARK1, SUB2, MARK2, SUB3, MARK3. But here since each of these students are in different classes, the number of subjects won’t be the same for everyone. For example, James being in 10th standard will have 12 subjects to study whereas Thomas will have only 4 subjects as he is in 2nd standard.

Here two approaches are in hand - One is to go for normalization and the second to go with Nested table concepts. If we are in a position not to split the table further, we can’t go for normalization. Now the only solution left is to go with Nested tables.

Solution Using Nested Table

Data Loading

      Since we need to store the (Subject, Mark) pair information corresponding to each subject, we need to first declare a Type object which is capable of storing Subject name and Mark as single.

Step 1 : Creation of an OBJECT Type

CREATE TYPE OBJ_TYPE_SUBJ_MARK IS OBJECT

(

   SUBJECT_NAME VARCHAR2(30),

   MARK NUMBER

);

Step 2 : Creation of a Nested Table Type

CREATE TYPE NT_SUBJ_MARK_DTLS IS TABLE OF OBJ_TYPE_SUBJ_MARK;

  By doing this, a one dimensional array ‘NT_SUBJ_MARK_DTLS’ is created where each cell can carry (SUBJECT, MARK) value combination.

NT_SUBJ_MARK_DTLS

(SUBJECT_NAME1, MARK1)

(SUBJECT_NAME2, MARK2)

(SUBJECT_NAME3, MARK3)

(SUBJECT_NAME4, MARK4)

(SUBJECT_NAME5, MARK5)

(SUBJECT_NAME6, MARK6)

.

.

.

.

        

  Actually there is no restriction on the size of this one dimensional array. For the time being, showed 6 elements only. However it can be any of any size according to the number of values we insert into the column.

Step 3 : Recreation of table with one column as this newly created Nested Table

          Now recreate the same table with one additional column which will carry the Subject and Mark details of each student in an array format. For that, the column should be having data type of “NT_SUBJ_MARK_DTLS” as given below.

DROP TABLE TEMP_STUDENT_DTLS PURGE;

CREATE TABLE TEMP_STUDENT_DTLS

(

   STUD_ID           NUMBER,

   STUD_NAME      VARCHAR2(30),

   STUD_CLASS     NUMBER,

   MARK_DTLS      NT_SUBJ_MARK_DTLS

) NESTED TABLE MARK_DTLS STORE AS SYS_GEN_TBL;

 Datatype of column is NT_SUBJ_MARK_DTLS which is nothing but the Nested table we created

Here the keyword ‘NESTED TABLE’ is used to inform Oracle that the column is having data type of a Nested table (which we have already created). One additional point to remember here is that, while declaring a database column of such a user defined data type of Nested Table, internally the contents of that column are not stored along with the remaining table column values. Instead, they are stored in another System generated table space allocated by Oracle which we need to name and in our example, the name ‘SYS_GEN_TBL’ has been given for that. This has to be given followed by the keyword ’STORE AS’.

Step 2 : Insert some entries to it.

   Suppose the Subject- Mark details of each of the students are as follows.

·         James

Marks 45, 40, 35 and 42 for Subjects Physics, Maths, English and Literature respectively.

·         Albert

Marks 48, 33 and 50 for Subjects Chemistry, Maths, and Latin respectively.

·         Thomas

Marks 38 and 42 for Subjects History and Maths respectively.

Hence the entries are like these.

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,

                               STUD_NAME,

                               STUD_CLASS,

                               MARK_DTLS)

      VALUES(100,

                  'JAMES',

                  10,

                  NT_SUBJ_MARK_DTLS(

                                              OBJ_TYPE_SUBJ_MARK ('PHYSICS', 45),

                                              OBJ_TYPE_SUBJ_MARK ('MATHS', 40),

                                              OBJ_TYPE_SUBJ_MARK ('ENGLISH', 35),

                                              OBJ_TYPE_SUBJ_MARK ('LITERATURE', 42)

                                             )

                );

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,

                               STUD_NAME,

                               STUD_CLASS,

                               MARK_DTLS)

      VALUES(200,

                  'ALBERT',

                  8,

                  NT_SUBJ_MARK_DTLS(

                                              OBJ_TYPE_SUBJ_MARK ('CHEMISTRY', 48),

                                              OBJ_TYPE_SUBJ_MARK ('MATHS', 33),

                                              OBJ_TYPE_SUBJ_MARK ('LATIN', 50)

                                             )

                );

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,

                               STUD_NAME,

                               STUD_CLASS,

                               MARK_DTLS)

      VALUES(400,

                  'THOMAS',

                  2,

                  NT_SUBJ_MARK_DTLS(

                                              OBJ_TYPE_SUBJ_MARK ('HISTORY', 38),

                                              OBJ_TYPE_SUBJ_MARK ('MATHS', 42)

                                             )

                );

COMMIT;

 *****3 Rows inserted and committed.

Step 3 : Verifying the inserted records.

SELECT * FROM TEMP_STUDENT_DTLS;

STUD_ID	STUD_NAME	STUD_CLASS	MARK_DTLS
100	JAMES	10	(DATASET)
200	ALBERT	8	(DATASET)
400	THOMAS	2	(DATASET)

As can be seen, the multiple Subjects and their corresponding marks have been loaded to a single column. Thus there is no need of any replication of same student record for each and every subject.

Data Retrieval

·        SELECT

  In order to get the subject-mark details of James, we need to use the TABLE operator as below.

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	45
MATHS	40
ENGLISH	35
LITERATURE	42

·        INSERT

On the other hand, if our intention is add to add one more subject-mark details to James, then

INSERT INTO TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      )

                        VALUES('CHEMISTRY',46);

COMMIT;

****   1 Row inserted and committed

.

To get the subject-mark details of James,

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	45
MATHS	40
ENGLISH	35
LITERATURE	42
CHEMISTRY	46

·        DELETE

In order to delete the subject = ‘CHEMISTRY’ record,

DELETE

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      )

           WHERE SUBJECT_NAME = 'CHEMISTRY';

COMMIT;

   1 Row deleted and committed

To get the subject-mark details of James,

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	45
MATHS	40
ENGLISH	35
LITERATURE	42

·        UPDATE

In order to update the mark of  subject = ‘PHYSICS’ record from current value of 45 to 50,

UPDATE TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      )

       SET MARK = 50

         WHERE SUBJECT_NAME = 'PHYSICS';

COMMIT;

   1 Row update and committed

To get the subject-mark details of James,

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	50
MATHS	40
ENGLISH	35
LITERATURE	42

Since we are making use of NESTED Table, the details are saved in the database itself as database table columns.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS APARTITION IN ORACLE

   WHAT IS ANINLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE