Tuesday, October 10, 2006

Oracle VARRAY Example

(Revised 09/20/2010, I've revised this example to be more pertinent.)
VARRAYs provide the interesting ability to store multiple values in a single column. Note, "interesting" doesn't mean I'm necessarily encouraging its use. I expect there are some potentially significant performance penalties using VARRAYs.

set serveroutput on;

column id FORMAT A5;
column url FORMAT A20;
column Tag(s) FORMAT A20;

clear;

-- Create a VARRAY that can hold 10 "objects" [cells] of type VARCHAR2 (i.e. 10 Tags of up to 50 characters)
create or replace type vcarray_tags as VARRAY(10) OF VARCHAR2(50);
/

-- Each URL is stored in a single row regardless of the number of tags
create table site_tags (id number, url varchar2(100), tag_list vcarray_tags);

insert into site_tags values (1, 'www.bing.com', vcarray_tags('search', 'microsoft'));
insert into site_tags values (2, 'www.google.com', vcarray_tags('search', 'google'));
commit;

set echo on;

-- Note, The VARRAY column is of type "object"
select 
   *
from
   site_tags;

-- Display each tag on a separate row 
select 
   site_tags.id, 
   site_tags.url,
   tags.column_value "Tag(s)"
from
   site_tags,
   table(site_tags.tag_list) tags;

-- Show all the URLs "tagged" with 'search'
select 
   site_tags.id, 
   site_tags.url,
   tags.column_value "Tag(s)"
from
   site_tags,
   table(site_tags.tag_list) tags
where
   tags.column_value = 'search';
/
-- PL/SQL Example 
begin
   for c in (select * from site_tags) loop
      dbms_output.put_line(c.id||' : '||c.url);
      for i in c.tag_list.first .. c.tag_list.last loop
         dbms_output.put_line('      Tag: '||c.tag_list(i));
      end loop;
   end loop;
end;
Results:
create or replace type vcarray_tags as VARRAY(10) OF VARCHAR2(50);
/
Type created
create table site_tags (id number, url varchar2(100), tag_list vcarray_tags);
 
Table created
insert into site_tags values (1, 'www.bing.com', vcarray_tags('search', 'microsoft'));
 
1 row inserted
insert into site_tags values (2, 'www.google.com', vcarray_tags('search', 'google'));
 
1 row inserted
commit;
 
Commit complete
set echo on;

select
 *
from
 site_tags;
 
   ID URL                  TAG_LIST
----- -------------------- --------
    1 www.bing.com         <Object>
    2 www.google.com       <Object>

select
 site_tags.id,
 site_tags.url,
 tags.column_value "Tag(s)"
from
 site_tags,
 table(site_tags.tag_list) tags;
 
   ID URL                  Tag(s)
----- -------------------- --------------------
    1 www.bing.com         search
    1 www.bing.com         microsoft
    2 www.google.com       search
    2 www.google.com       google

select
 site_tags.id,
 site_tags.url,
 tags.column_value "Tag(s)"
from
 site_tags,
 table(site_tags.tag_list) tags
where
 tags.column_value = 'search';
 
   ID URL                  Tag(s)
----- -------------------- --------------------
    1 www.bing.com         search
    2 www.google.com       search

begin
 for c in (select * from site_tags) loop
  dbms_output.put_line(c.id||' : '||c.url);
  for i in c.tag_list.first .. c.tag_list.last loop
   dbms_output.put_line('      Tag: '||c.tag_list(i));
  end loop;
 end loop;
end;
/
 
1 : www.bing.com
      Tag: search
      Tag: microsoft
2 : www.google.com
      Tag: search
      Tag: google

5 comments:

Anonymous said...

Thanks, quite clear.

narendra said...

Very Nice example to understand what is an varray and Nested table

Thanks!

Aniket Zamwar said...

Hello,

Can we put Check constraint on varrays ?

example
create type abc as varrays(3) of varchar2(20);

now i want to insert specific strings in this datatype when I use it in table.

CREATE TYPE info AS OBJECT(
name varchar2(20),
checker abc
);

when I create table of info, I need to put check constraint on checker..

Any suggestion?

Jason Vogel said...

What kind of constraint are you thinking of? You have a built-in constraint on the datatype [Varchar2(20)] and in the number of allowed elements (3).

Have you looked at the member methods?

Anonymous said...

thank you very much