RE: passing several columns into single array using RETURNING clause in java

From: Richard Ji <Richard.Ji_at_ztango.com>
Date: Tue, 3 Aug 2004 13:30:19 -0400
Message-ID: <E1732F21A9B99440B2117A3BEC76B91D2FE671@ztangousexch.ad.ztango.com>

Tanel,

You can use CallableStatement to call the following anonymous block. Based on Anthony's example

declare
l_array emp_array;
begin
update emp set sal=3D9999 where deptno=3D? returning emp_obj(rowid,empno,ename,sal) bulk collect into l_array;

open ? for select * from table(cast(l_array as emp_array)); end;

And bind the two variables.

Will this work for you?

Richard Ji

-----Original Message-----

From:	Anthony Molinaro [mailto:amolinaro_at_wgen.net]
Sent:	Tue 8/3/2004 11:26 AM
To:	oracle-l_at_freelists.org
Cc:=09
Subject:	RE: passing several columns into single array using RETURNING =

clause in java=20
Tanel,
  I only put sql in directly in jdbc if it is extremely simple, =3D otherwise I'm using plsql in jdbc.
  I'm not sure how to make a prepared statement accept an OUT parameter =
=3D

( I believe the values in a RETURNING clause=3D20   must be treated like OUTs in jdbc ).
  I was under the impression you could only do it with a callable =3D statement.
  But, I'm no java pro, so I could be wrong. Maybe metalink?   Is the requirement to do this in JDBC, because this would be a breeze =
=3D

in pro*c.

-----Original Message-----
From: Tanel P=3DF5der [mailto:tanel.poder.003_at_mail.ee]=3D20 Sent: Tuesday, August 03, 2004 10:30 AM
To: oracle-l_at_freelists.org
Subject: Re: passing several columns into single array using RETURNING =
=3D

clause in java=3D20

Thanks Anthony!

Is there a way to do this with a single update called from JDBC, without =
=3D

PL/SQL as well? I know it's possible with OCI, but I'm not that familiar =
=3D

with Java, so maybe anyone of you can give me some pointers...

Thanks,
Tanel.

Original Message -----=3D20 From: "Anthony Molinaro" <amolinaro_at_wgen.net> To: <oracle-l_at_freelists.org> Sent: Tuesday, August 03, 2004 5:04 PM Subject: RE: passing several columns into single array using RETURNING =
=3D
clause in java

> Tanel,
>
>   I'd use a callable statement. Also, I'm assuming later on in =

the=3D20
> code =3D3D you may want to treat the=3D3D20 > array like a table, ie, select * from table( :array );, so use an =
=3D
=3D3D=3D20

> array of objects instead
> of a composite associative array.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put =3D 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Aug 03 2004 - 12:26:20 CDT