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:10:01 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C51D1DE@wgdc02.wgenhq.net>

Arrg... Outlook has obfuscated a message once again. Here's the code in an attachment.

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

From: Tanel Põder [mailto:tanel.poder.003_at_mail.ee] 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

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 = '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

Attached file included as plaintext by Ecartis --
File: jdbcdemo.txt
Desc: jdbcdemo.txt

/* 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 := emp_array();
begin

update emp

       set sal=9999
     where deptno = p_deptno

returning emp_obj(rowid,empno,ename,sal) bulk collect into l_array;

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 = DriverManager.getConnection ( "jdbc:oracle:oci:@mydb", "scott", "tiger" ); conn.setAutoCommit( false );

Object attributes[] = new Object[4];

    attributes[0] = new String();
    attributes[1] = new Integer(1);
    attributes[2] = new String();
    attributes[3] = new Integer(1);

Object demo_obj[] = new Object[1];

demo_obj[0] = new oracle.sql.STRUCT (new oracle.sql.StructDescriptor("EMP_OBJ",conn),conn,attributes);

oracle.sql.ARRAY demo_array = new oracle.sql.ARRAY (new oracle.sql.ArrayDescriptor("EMP_ARRAY",conn),conn,demo_obj);

OracleCallableStatement cs = (OracleCallableStatement)conn.prepareCall ("BEGIN ? := 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 = (oracle.sql.ARRAY)cs.getArray(1);

ResultSet rs = results.getResultSet();

while (rs.next()) {

oracle.sql.STRUCT obj = (STRUCT)rs.getObject(2);

Object vals[] = 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

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

Received on Tue Aug 03 2004 - 09:05:47 CDT