RE: Varrays

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Mon, 11 Oct 2004 20:43:02 -0400
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA6501B9D9BF@25exch1.vicorpower.vicr.com>

Lex,

        That was the way I read it too, the problem is how does one write the PL/SQL to handle it? You've got a variable number of elements that have to be provided for.=20

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Natural Join B.V. [mailto:lex.de.haan_at_naturaljoin.nl]=20 Sent: Monday, October 11, 2004 4:03 AM
To: Goulet, Dick; oracle-l_at_freelists.org Subject: Re: Varrays

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"=20
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,

Lex.

> To All,

>=20
> I've RTFM'd, and scanned through everything I could find on
Metalink,=20
> but damned if I can figure this one out, easily. Here's the
> problem:

>=20
> We had a duhveloper who thought it would be a good thing to
store=20
> multiple archive tapes in a varray within a table instead of having a=20
> second table to join on. Therefore he created the following:

>=20

> Create type hist_tape_id as varray(30) of number; Create table=20
> module_master(model_number varchar2(20),......., archive_tape_id=20
> number, historical_tape_id hist_tape_id);

>=20

> The problem is that 100% of the rows in this table have an=20
> archive_tape_id, but the historical_tape_id is null. 10% have more=20
> than one value stored in the historical_tape_id. Now I know the idea=20
> was that there would never be a need in the future for historical=20
> tapes, but as with many things times change & now I'm stuck trying to=20
> figure out how to add to the historical_tape_id column. So the=20
> question is< how to you add values to the historical_tape_id column=20
> without loosing the existing values. The books say you have to=20
> "recreate" it. Metalink is no help either since they keep confusing
it with a nested table.

>=20

> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 11 2004 - 19:38:35 CDT