RE: Varrays

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Mon, 11 Oct 2004 22:26:23 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61CD10047@wgdc02.wgenhq.net>

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_at_vicr.com]
Sent:	Mon 10/11/2004 8:43 PM
To:	Natural Join B.V.; oracle-l_at_freelists.org
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_at_naturaljoin.nl]=3D20 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"=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
> --
> http://www.freelists.org/webpage/oracle-l
>=3D20

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


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

Received on Mon Oct 11 2004 - 21:22:02 CDT