Re: Updating PLSQL Table

Thanks.

I actually thought it was possible when I read this in the document:

Go to Link--> http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

Search for this "Example: Performing INSERT, UPDATE, and DELETE Operations on PL/SQL Nested Tables"

To perform DML operations on a PL/SQL nested table, use the operators TABLEand CAST. This way, you can do set operations on nested tables using SQL notation, without actually storing the nested tables in the database.

But, it didn't give an example as to how to perform DML using TABLE and CAST.

-Deepak

• Original Message ---- From: William Robertson <william_at_williamrobertson.net> To: oracle-l_at_freelists.org Sent: Tuesday, April 29, 2008 1:57:20 AM Subject: Re: Updating PLSQL Table

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


      ____________________________________________________________________________________
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