Re: Updating PLSQL Table

Yes, I didn't catch that part, my bad.

You need to have the nested table "as part of" a regular database table to perform DML on it. IOW as a column in a table.

Stefan

On Tue, Apr 29, 2008 at 8:57 AM, William Robertson < william_at_williamrobertson.net> wrote:

> It's an array variable in PL/SQL, not a database table. Although it can be
> cast to appear as a relational table for querying, you use the PL/SQL
> assignment, bulk collect etc syntax to set values.
>
> btw you don't need the explicit CAST in recent Oracle versions -
> TABLE(collection_name) will generally do it.
>
>
> -----Original message-----
> From: Deepak Sharma
> Date: 29/4/08 06:55
>
> > If I have a PLSQL "my_array" table defined as below:
> >
> > create or replace type my_rec as object (
> > emp_id number,
> > emp_name varchar2
> > )
> > /
> >
> > create or replace type my_array as table of my_rec
> > /
> >
> > I can select from it w/o problem, even using a WHERE clause :
> >
> > my_list my_array;
> >
> > FOR c1 IN (
> > SELECT emp_name
> > FROM TABLE( CAST( my_list AS my_array))
> > WHERE emp_id = 999
> > )
> > LOOP
> > DBMS_OUTPUT.PUT_LINE( c1.emp_name );
> > END LOOP;
> >
> >
> > But, is there a way to UPDATE a particular row of that array (just as a
> > Table) ?
> >
> > Say, I want to update and set emp_name = UPPER(emp_name), for this
> > entire array, how will I do it w/o having to loop across each element?
> > And, #2,, how can I update just specific row(s), say, I want to update
> > emp_name = 'UPPER(emp_name), where emp_id = 123, only.
> >
> >
> > Any ideas?
> >
> >
> > Thanks,
> > Deepak
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
=========================

Stefan P Knecht
Senior Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

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