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

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Tue, 3 Aug 2004 10:04:50 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C033F9B@wgdc02.wgenhq.net>

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,

-----Original Message-----
From: Tanel P=F5der [mailto:tanel.poder.003_at_mail.ee]=20 Sent: Tuesday, August 03, 2004 8:07 AM
To: oracle-l_at_freelists.org
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_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 - 09:00:40 CDT