Re: Pro C/Java returning PLSQL Tables

A copy of this was sent to jeperkins4 <johnp_at_1rent.com> (if that email address didn't require changing) On Tue, 16 Nov 1999 20:04:19 GMT, you wrote:

>What if I want to insert or update values in the database using this
>method, which would return a PL/SQL Table. This way I always get a

it doesn't return a PLSQL table -- it returns a SQL nested table type.

>result set that is identical in it's signature with the get. When I
>tried to do this, I got the following error:
>
>ORA-14551: cannot perform a DML operation inside a query
>

Can you clarify? I don't understand the question.

tkyte_at_8.0> insert into t
  2 select a.column_value val
  3 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   4 /

9 rows created.

tkyte_at_8.0>
tkyte_at_8.0> update t
  2 set val = val+1
  3 where val in (
  4 select a.column_value val
  5 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   6 )
  7 /

9 rows updated.

tkyte_at_8.0>
tkyte_at_8.0> delete from t
  2 where val in (
  3 select a.column_value val
  4 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   5 )
  6 /

8 rows deleted.

>Thanks,
>
>John
>
>> tkyte_at_8.0>
>> tkyte_at_8.0> REM here we go... selecting from it:
>> tkyte_at_8.0>
>> tkyte_at_8.0> select *
>> 2 from THE ( select cast( getMyTableType() as mytableType ) from
>dual ) a
>> 3 /
>>
>> X Y Z
>> ---------- --------- -------------------------
>> 1 06-NOV-99 This is row 1
>> 2 07-NOV-99 This is row 2
>> 3 08-NOV-99 This is row 3
>> 4 09-NOV-99 This is row 4
>> 5 10-NOV-99 This is row 5
>>
>> Since every language can deal with a Query -- this lets you
>get 'tables of
>> records' back quite easily as result sets (works in JDBC as well).
>>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 17 1999 - 07:04:00 CST