Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Varrays

Re: Varrays

From: Natural Join B.V. <>
Date: Mon, 11 Oct 2004 08:03:02 MET
Message-Id: <>

Hi Dick,

the SQL language does not support varray indices -- that is, you can't access individual elements of a varray by its index. that's why you have to "recreate" them, or better: you retrieve the current value, change it in some way where you add the new/updated values or delete them where appropriate, and then you replace the entire existing varray value with the new one you just constructed.

you might be better off writing some PL/SQL procedures to do this for you -- obviously, in the PL/SQL environment you can access/change/delete individual varray elements.

additions/corrections welcome,


> To All,
> I've RTFM'd, and scanned through everything I could find on
> Metalink, but damned if I can figure this one out, easily. Here's the
> problem:
> We had a duhveloper who thought it would be a good thing to
> store multiple archive tapes in a varray within a table instead of
> having a second table to join on. Therefore he created the following:
> Create type hist_tape_id as varray(30) of number;
> Create table module_master(model_number varchar2(20),.......,
> archive_tape_id number, historical_tape_id hist_tape_id);
> The problem is that 100% of the rows in this table have an
> archive_tape_id, but the historical_tape_id is null. 10% have more than
> one value stored in the historical_tape_id. Now I know the idea was
> that there would never be a need in the future for historical tapes, but
> as with many things times change & now I'm stuck trying to figure out
> how to add to the historical_tape_id column. So the question is< how to
> you add values to the historical_tape_id column without loosing the
> existing values. The books say you have to "recreate" it. Metalink is
> no help either since they keep confusing it with a nested table.
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> --

Received on Mon Oct 11 2004 - 01:00:23 CDT

Original text of this message