How to call Stored Procedure having vArray as argument and which returns a cursor

Hi Guys,

This might help new bees working in Java Enterprise Applications where performance is key constraint in the application development.

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class VarrayDemo {

public static void main(String args[]){

try{

Connection conn = DBConnectionFactory.getConnection();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

java.util.Date date = new java.util.Date();

/*DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");

System.out.println("System Date = "+dformat.format(date));*/

CallableStatement css= conn.prepareCall("{call getPriceForProduct(?,?,?,?)}");

css.setString(1,"Arg1");
css.setDate(2,new java.sql.Date(today.getTime()));

// Create an oracle.sql.ARRAY object to hold the values
oracle.sql.ArrayDescriptor arrayDesc = oracle.sql.ArrayDescriptor.createDescriptor("NameOfStoredProcedureArray".toUpperCase(), conn);

String arrayValues[] = {"arrayValue1","arrayValue1","arrayValue1","arrayValue1"};

oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn, arrayValues);

// Set the values to insert
((oracle.jdbc.driver.OraclePreparedStatement)css).setARRAY(3, array);

css.registerOutParameter(4,oracle.jdbc.driver.OracleTypes.CURSOR);

css.executeQuery();

ResultSet rs = ((OracleCallableStatement)css).getCursor(4);


int i=0;
while(rs.next())
{

int output2 = rs.getInt(1);
String output2 = rs.getString(2);
System.out.println("output3 : "+rs.getString(3));
System.out.println("Price : "+rs.getString(4));

}
}
catch (Exception e) {
System.err.println( e.getMessage());
e.printStackTrace();
}

}

}

Feel free to comment and buzz if any query.


Cheers,
Allwyn