Re: Updating PLSQL Table
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: william@xxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Apr 2008 09:23:23 -0700 (PDT)
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@xxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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
>
>
--
//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
Other related posts:
