RE: passing several columns into single array using RETURNING clause in java
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Aug 2004 10:04:50 -0400
Tanel,
I'd use a callable statement. Also, I'm assuming later on in the code =
you may want to treat the=20
array like a table, ie, select * from table( :array );, so use an =
array of objects instead
of a composite associative array.
Here's a simple example ( tested on 9.2.0.4 on rh as 3.0 - test both =
oci and thin drivers, both are fine )
/* first create the sql types/routines in the db */
create type emp_obj as object ( row_id varchar2(20), empno number, ename =
varchar2(10), sal number );
/
create type emp_array as table of emp_obj;
/
create function f_update_emps ( p_deptno number )
return emp_array
is
l_array emp_array :=3D emp_array();
begin
update emp
set sal=3D9999
where deptno =3D p_deptno
returning emp_obj(rowid,empno,ename,sal) bulk collect into l_array;
=20
return l_array;
end f_update_emps;
/
/* create the jdbc to call f_update_emps and display the output */
import java.lang.*;
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class tanel
{
public static void main( String args[] ) throws IOException, =
SQLException
{
DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() =
);
Connection conn =3D DriverManager.getConnection ( =
"jdbc:oracle:oci:@mydb", "scott", "tiger" );
conn.setAutoCommit( false );
Object attributes[] =3D new Object[4];
attributes[0] =3D new String();
attributes[1] =3D new Integer(1);
attributes[2] =3D new String();
attributes[3] =3D new Integer(1);
Object demo_obj[] =3D new Object[1];
demo_obj[0] =3D new oracle.sql.STRUCT (new =
oracle.sql.StructDescriptor("EMP_OBJ",conn),conn,attributes);
oracle.sql.ARRAY demo_array =3D new oracle.sql.ARRAY (new =
oracle.sql.ArrayDescriptor("EMP_ARRAY",conn),conn,demo_obj);
OracleCallableStatement cs =3D =
(OracleCallableStatement)conn.prepareCall ("BEGIN ? :=3D =
F_UPDATE_EMPS(?); END;");
cs.registerOutParameter (1,OracleTypes.ARRAY,"EMP_ARRAY");
cs.setInt (2,10); // deptno 10 has 3 rows
cs.execute();
oracle.sql.ARRAY results =3D (oracle.sql.ARRAY)cs.getArray(1);
ResultSet rs =3D results.getResultSet();
while (rs.next()) {
oracle.sql.STRUCT obj =3D (STRUCT)rs.getObject(2);
Object vals[] =3D obj.getAttributes();
System.out.println (vals[0] + " " + vals[1] + " " + vals[2] + " =
" + vals[3]);
}
cs.close();
conn.rollback();
conn.close();
}
}
$ javac tanel.java
$ java tanel
AAAUa6AASAAARYKAAG 7782 CLARK 9999
AAAUa6AASAAARYKAAI 7839 KING 9999
AAAUa6AASAAARYKAAN 7934 MILLER 9999
Hope that helps,
- ant
-----Original Message-----
From: Tanel P=F5der [mailto:tanel.poder.003@xxxxxxx]=20
Sent: Tuesday, August 03, 2004 8:07 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: passing several columns into single array using RETURNING =
clause in java=20
Hi!
How can I pass several column into single array using RETURNING clause =
in Java?
The statement would have to be following:
update table set status =3D 'X'
where status 'Y'
returning rowid, col1, col2, col3 into :array;
The update will update several rows at a time...
I need an example, how can I return all those 4 columns to a single =
array on Java client, not 4 different ones?
Can it be done using JDBC thin drivers?
Tanel.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put =
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
