Pages

Sunday, 25 October 2015

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.

CREATETABLE TEMP_STUDENT_DTLS

(

STUD_ID NUMBER,

STUD_NAME VARCHAR2(30),

STUD_CLASS NUMBER

);

Step 2 : Insert some entries to it.

INSERTINTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

VALUES(100,'JAMES',10);

INSERTINTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

VALUES(200,'ALBERT',8);

INSERTINTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

VALUES(300,'MIKE',5);

INSERTINTO 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

CREATETYPE OBJ_TYPE_SUBJ_MARK ISOBJECT

(

SUBJECT_NAME VARCHAR2(30),

MARK NUMBER

);

Step 2 : Creation of a Nested Table Type

CREATETYPE NT_SUBJ_MARK_DTLS ISTABLEOF
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.

DROPTABLE TEMP_STUDENT_DTLS PURGE;

CREATETABLE TEMP_STUDENT_DTLS

(

STUD_ID NUMBER,

STUD_NAME VARCHAR2(30),

STUD_CLASS NUMBER,

MARK_DTLS NT_SUBJ_MARK_DTLS

)NESTEDTABLE MARK_DTLS STOREAS SYS_GEN_TBL;

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

Here the keyword ‘NESTEDTABLE’ 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 allocatedby 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’STOREAS’.

Step 2 : Insert some entries to it.

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