RE: Varrays
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <DGoulet@xxxxxxxx>, "Natural Join B.V." <lex.de.haan@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Oct 2004 22:26:23 -0400
Dick,
you have a couple of options.
=20
First, as soon as you can, get the varrays, nested tables, objects, =
etc...
out of your tables. Those constructs are best suited for pl/sql
not in relational tables.=20
=20
Second, don't blame your developer. You dba's love doin that.
Just post your question and be done with it.
"Duhveloper"... brilliant. Check the lazydba list for a week
and you tell me how many duh-dba's you see there.
Anyway, one thing you can do is cartesian the varray into a regular =
table,
then you can do regular updates.
create table varray_fix as
select model_number, archive_tape_id, x.column_value historical_tape_id
from module_master, table(historical_tape_id) x
order by 1;
now you have a regular table and can get rid of the table with the =
varray.
If you are forced to keep the varray, you can add values like this:
(i'm adding 3 values, 100,200,300 to the varray without touching
the other values)
declare
x hist_tape_id;
begin
for i in ( select model_number, historical_tape_id from =
module_master )
loop
x :=3D i.historical_tape_id;
for i in 1 .. 3
loop
x.extend(1);
x(x.last()) :=3D i*100;
end loop;
update module_master set historical_tape_id =3D x=20
where model_number =3D i.model_number;
end loop;
end;
/
=20
That will successfully add 3 vals (100,200,300) to each varray in the =
table.
hope that helps,
ant =20
-----Original Message-----
From: Goulet, Dick [mailto:DGoulet@xxxxxxxx]
Sent: Mon 10/11/2004 8:43 PM
To: Natural Join B.V.; oracle-l@xxxxxxxxxxxxx
Cc:=09
Subject: RE: Varrays
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.=3D20
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Natural Join B.V. [mailto:lex.de.haan@xxxxxxxxxxxxxx]=3D20
Sent: Monday, October 11, 2004 4:03 AM
To: Goulet, Dick; oracle-l@xxxxxxxxxxxxx
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"=3D20
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,
>=3D20
> I've RTFM'd, and scanned through everything I could find on
Metalink,=3D20
> but damned if I can figure this one out, easily. Here's the
> problem:
>=3D20
> We had a duhveloper who thought it would be a good thing to
store=3D20
> multiple archive tapes in a varray within a table instead of having =
a=3D20
> second table to join on. Therefore he created the following:
>=3D20
> Create type hist_tape_id as varray(30) of number; Create table=3D20
> module_master(model_number varchar2(20),......., archive_tape_id=3D20
> number, historical_tape_id hist_tape_id);
>=3D20
> The problem is that 100% of the rows in this table have an=3D20
> archive_tape_id, but the historical_tape_id is null. 10% have =
more=3D20
> than one value stored in the historical_tape_id. Now I know the =
idea=3D20
> was that there would never be a need in the future for historical=3D20
> tapes, but as with many things times change & now I'm stuck trying =
to=3D20
> figure out how to add to the historical_tape_id column. So the=3D20
> question is< how to you add values to the historical_tape_id =
column=3D20
> without loosing the existing values. The books say you have to=3D20
> "recreate" it. Metalink is no help either since they keep confusing
it with a nested table.
>=3D20
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> --
> //www.freelists.org/webpage/oracle-l
>=3D20
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
