Oracle for Beginner: ORDB with Oracle (II)

Continuing my last note, here we will try to use VARRAY() to make multiple value for a attribute of table. One of disadvantage of varray is we have to specify the maximum of element array that we want to save in it.

Here an example of how to create a varray type:

SQL> create type v_pengajar_t as varray(3) of pengajar_t;

Like previous example, we can create table which have one attribute with varray() type.

SQL> create table vkursus(
2 no_kursus char(5) primary key,
3 nama varchar(30),
4 pengajar v_pengajar_t
5 );

To insert new data, we can use statement below:

SQL> insert into vkursus values ('X01', 'Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P02', 'Wati')));

1 row created.

SQL> insert into vkursus values ('X02', 'ORDB with Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P03', 'Othie'), pengajar_t('P04','Indah')));

1 row created.

SQL> commit;


To make a query for varray, we can use table() function to create a virtual table from varray field. Here is an example:

SQL> select no_kursus, v.nama, p.nama
2 from vkursus v, table(v.pengajar) p;

NO_KU NAMA NAMA
----- ------------------------------ ------------------------------
X01 Oracle Budi
X01 Oracle Wati
X02 ORDB with Oracle Budi
X02 ORDB with Oracle Othie
X02 ORDB with Oracle Indah