19 | 08 | 2017
Latest Articles
Popular Articles

Database Design

Varrays

User Rating:  / 0
PoorBest 

Working with Varrays

 

The data types we are familiar with are

1. number

2. varchar2

3. date

4. timestamp

5. clobs

6. blobs

7.bfiles

... 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.

 

1. NESTED TABELS

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_ID JOB_NAME
---------- ------------------------------
JOB_DAYS
--------------------------------------------------------------------------------
1 BACKUP_HOST1_DB1
JOB_TYPE(1, 2, 3)

2 BACKUP_HOST1_DB2
JOB_TYPE(0)



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