Re: Updating PLSQL Table

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Tue, 29 Apr 2008 09:57:14 -0700 (PDT)
Message-ID: <935252.68817.qm@web52802.mail.re2.yahoo.com>

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_at_yahoo.com> To: Stefan Knecht <knecht.stefan_at_gmail.com> Cc: oracle-l <oracle-l_at_freelists.org> 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_at_gmail.com> To: sharmakdeep_oracle_at_yahoo.com Cc: oracle-l <oracle-l_at_freelists.org> 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_at_yahoo.com> 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_at_trivadis.com
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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 29 2008 - 11:57:14 CDT