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