09 | 08 | 2022
Latest Articles
Popular Articles

Database Design


User Rating:  / 0

Working with Varrays


The data types we are familiar with are

1. number

2. varchar2

3. date

4. timestamp

5. clobs

6. blobs


... and some others


We can in rare cases use an ARRAY as a datatype in the Oracle Database. Associative Arrays are used in the PL/SQL language.You may want to read the PL/SQL guide. However 2 kind of ARRAYS we can use as a datatype in the table definition and we can use SQL to query the information.



2. Variable arrays or VARRAYS


You may want to read the application developpers guide for a complete description and difference bewteen the above two ARRAYS


Here some gym with a 1 dimension VARRAY


STEP 1 : We create a TYPE : JOB_TYPE


SQL> create type JOB_TYPE as VARRAY(7) of NUMBER(1);
2  /

Type created.

STEP 2 : We create a table which uses that TYPE

SQL> create table T_JOBS (job_id number,job_name varchar2(30),job_days JOB_TYPE);

Table created.

STEP 3 : We insert some records in the table and afterwards we use "ordinary" SQL to query the data.

SQL> insert into T_JOBS values (1,'BACKUP_HOST1_DB1',job_type(1,2,3));

1 row created.

SQL> insert into T_JOBS values (2,'BACKUP_HOST1_DB2',job_type(0));

1 row created.

SQL> commit;

SQL> select * from T_JOBS;

---------- ------------------------------
JOB_TYPE(1, 2, 3)


STEP 4 : We adjust our SQL statement to unnest the array

SQL> select a.job_id,a.job_name,
2  case b.column_value
3  when 1 then 'Monday'
4  when 2 then 'Tuesday'
5  when 3 then 'Wednesday'
6  when 4 then 'Thursday'
7  when 5 then 'Friday'
8  when 6 then 'Saturday'
9  when 0 then 'Sunday'
10  end AS DAY
11  from
12  t_jobs a, TABLE (a.job_days) b;

JOB_ID JOB_NAME              DAY
---------- ------------------------------
1 BACKUP_HOST1_DB1      Monday
1 BACKUP_HOST1_DB1      Tuesday
1 BACKUP_HOST1_DB1      Wednesday
2 BACKUP_HOST1_DB2      Sunday