Re: Updating PLSQL Table

I checked Metalink and someone else had posted the same question there (in 
2004) but didn't get an answer.

Choose "technical Forum" and search for "To perform DML operations on a PL/SQL 
nested table"

-Deepak


----- Original Message ----
From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
To: Stefan Knecht <knecht.stefan@xxxxxxxxx>
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, April 29, 2008 1:32:11 AM
Subject: Re: Updating PLSQL Table


Hi,

Tried this, but it didn't work:

DECLARE

lv_my_array my_array;
BEGIN


update ( select emp_name from TABLE ( CAST( lv_my_array AS my_array) ) )
set emp_name = 'NEW_' || emp_name;

END;

I'm getting "virtual column not allowed here".


----- Original Message ----
From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
To: sharmakdeep_oracle@xxxxxxxxx
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, April 29, 2008 12:58:25 AM
Subject: Re: Updating PLSQL Table

You can do UPDATE (SELECT) .....

Although there are some limitations. 

Cheers

Stefan


On Tue, Apr 29, 2008 at 7:55 AM, Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx> 
wrote:

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



     
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
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@xxxxxxxxxxxx
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
========================= 
________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile. Try it now.


      
____________________________________________________________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Other related posts: