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:10:01 -0400
Arrg... Outlook has obfuscated a message once again. Here's the code in an 
attachment.
 - a

-----Original Message-----
From: Tanel Põder [mailto:tanel.poder.003@xxxxxxx] 
Sent: Tuesday, August 03, 2004 8:07 AM
To: oracle-l@xxxxxxxxxxxxx
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@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
-----------------------------------------------------------------

-- 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
----------------------------------------------------------------
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
-----------------------------------------------------------------
RE: passing several columns into single array using RETURNING clause in java

Other related posts:
