Help me to solve the simple but complicated problem ?

Hi Satish,

You can use stored procedures using CallableStatement. You could loop through your array and make a batch update eg,

for (int i = 0; i<rowsToUpdate;i++) {

CallableStatement cs = con.prepareCall("{call package_name.procedure_name(?,?)}");

cs.setString(1, "Some val");

cs.setInt("2", 67);

cs.addBatch();

}

cs.executeBatch();

Richard>

Rijaos at 2007-6-29 12:44:32 >

Oracle JDBC driver is JDBC 2.0 compliant. Right ?

And in JDBC 2.0 there exists the interface java.sql.Array

Oracle JDBC provides the class oracle.sql.ARRAY Have you thought of using it ? It may be what you are looking for.

Here is an example that comes with Oracle JDBC driver...

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.oracore.Util;

import oracle.jdbc.driver.*;

import java.math.BigDecimal;

public class ArrayExample

{

public static void main (String args[])

throws Exception

{

// Register the Oracle JDBC driver

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// Connect to the database

// You need to put your database name after the @ sign in

// the connection URL.

//

// The sample retrieves an varray of type "NUM_VARRAY",

// materializes the object as an object of type ARRAY.

// A new ARRAY is then inserted into the database.

Connection conn =

DriverManager.getConnection ("jdbc:oracle:oci8:@",

"scott", "tiger");

// It's faster when auto commit is off

conn.setAutoCommit (false);

// Create a Statement

Statement stmt = conn.createStatement ();

try

{

stmt.execute ("DROP TABLE varray_table");

stmt.execute ("DROP TYPE num_varray");

}

catch (SQLException e)

{

// the above drop statements will throw exceptions

// if the types and tables did not exist before. Just ingore it.

}

stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)");

stmt.execute ("CREATE TABLE varray_table (col1 num_varray)");

stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");

ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");

showResultSet (rs);

//now insert a new row

// create a new ARRAY object

int elements[] = { 300, 400, 500, 600 };

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn);

ARRAY newArray = new ARRAY(desc, conn, elements);

PreparedStatement ps =

conn.prepareStatement ("insert into varray_table values (?)");

((OraclePreparedStatement)ps).setARRAY (1, newArray);

ps.execute ();

rs = stmt.executeQuery("SELECT * FROM varray_table");

showResultSet (rs);

// Close all the resources

rs.close();

ps.close();

stmt.close();

conn.close();

}

public static void showResultSet (ResultSet rs)

throws SQLException

{

int line = 0;

while (rs.next())

{

line++;

System.out.println("Row "+line+" : ");

ARRAY array = ((OracleResultSet)rs).getARRAY (1);

System.out.println ("Array is of type "+array.getSQLTypeName());

System.out.println ("Array element is of type code "+array.getBaseType());

System.out.println ("Array is of length "+array.length());

// get Array elements

BigDecimal[] values = (BigDecimal[]) array.getArray();

for (int i=0; i<values.length; i++)

{

BigDecimal value = (BigDecimal) values[i];

System.out.println(">> index "+i+" = "+value.intValue());

}

}

}

}

neville_sequeira at 2007-6-29 12:44:32 >

hi neville sequeira,

Thanks for your response. I got one more problem. If my table columns data type is varchar,int and date for which array values to be inserted. Is there any method by which i can do insertion to my tables taking values from array passed to callable statement.

Advance thanks again,

Satish

satish_gs at 2007-6-29 12:44:32 >