Home » Developer & Programmer » JDeveloper, Java & XML » Passing java Arrays to Stored Procedure in Oracle.
| Passing java Arrays to Stored Procedure in Oracle. [message #121803] |
Wed, 01 June 2005 02:39 |
singhvikas
Messages: 2
Registered: June 2005
|
Junior Member |
|
|
Hi,
I have a SP with following desc...
PACKAGE Prr_Pkg IS
TYPE ratetypenbr_arrType IS TABLE OF NUMBER(1) INDEX BY BINARY_INTEGER;
TYPE begin_range_arrType IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE end_range_arrType IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE low_arrType IS TABLE OF NUMBER(10,3) INDEX BY BINARY_INTEGER;
TYPE high_arrType IS TABLE OF NUMBER(10,3) INDEX BY BINARY_INTEGER;
TYPE currencycode_arrType IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
TYPE productsetid_arrType IS TABLE OF VARCHAR2( INDEX BY BINARY_INTEGER;
TYPE action_arrType IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
PROCEDURE prr_proc (
v_property_id IN PROPERTY_RATE_RANGE.property_id%TYPE,
v_marketer_id IN PROPERTY_RATE_RANGE.marketer_Id%TYPE,
v_contract_id IN PROPERTY_RATE_RANGE.contract_Id%TYPE,
v_currency_code IN currencycode_arrType,
v_product_set_id IN productsetid_arrType,
v_rate_type_nbr IN ratetypenbr_arrType,
v_begin_range IN begin_range_arrType,
v_end_range IN end_range_arrType,
v_low_rate IN low_arrType,
v_high_rate IN high_arrType,
v_action IN action_arrType,
reterridx OUT NUMBER,
reterrmsg OUT VARCHAR2
);
END Prr_Pkg;
Now I try to call this SP from a Java Class sending the parameters :
// obtain a type descriptor of "sql_type_name"
ArrayDescriptor desc1 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("RATETYPENBR_ARRTYPE", con);
ArrayDescriptor desc2 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("BEGIN_RANGE_ARRTYPE", con);
ArrayDescriptor desc3 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("END_RANGE_ARRTYPE", con);
ArrayDescriptor desc4 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("LOW_ARRTYPE", con);
ArrayDescriptor desc5 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("HIGH_ARRTYPE", con);
ArrayDescriptor desc6 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("CURRENCYCODE_ARRTYPE", con);
ArrayDescriptor desc7 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("PRODUCTSETID_ARRTYPE", con);
ArrayDescriptor desc8 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("ACTION_ARRTYPE", con);
Logger.log( TRACE, " CArrayDescriptor made desc " + desc1);
String propertyId_sp = "10004";
String marketerId_sp = "SIXCONT";
String contractId_sp = "HI";
String [] currencyCode_sp = new String [] {"USD" , "USD"};
String [] productSetId_sp = new String[] {"RAC", "RAC"};
int [] rateTypeNbr_sp = new int[] {2, 3};
String [] effectiveDate_sp = new String[] {"01-FEB-2005", "01-FEB-2005"};
String [] discontinueDate_sp = new String[] {"01-FEB-2005", "01-FEB-2005"};
double [] lowRate_sp = new double[] {100.50, 100.50};
double [] highRate_sp = new double[] {200.50, 200.50};
String [] action_sp = new String[] {"I", "I"};
Logger.log( TRACE, " Arrays made ");
// create the ARRAY by calling the constructor
ARRAY array1 = new ARRAY (desc1, con, rateTypeNbr_sp);
ARRAY array2 = new ARRAY (desc2, con, effectiveDate_sp);
ARRAY array3 = new ARRAY (desc3, con, discontinueDate_sp);
ARRAY array4 = new ARRAY (desc4, con, lowRate_sp);
ARRAY array5 = new ARRAY (desc5, con, highRate_sp);
ARRAY array6 = new ARRAY (desc6, con, currencyCode_sp);
ARRAY array7 = new ARRAY (desc7, con, productSetId_sp);
ARRAY array8 = new ARRAY (desc8, con, action_sp);
Logger.log( TRACE, " Calling property_rate_range Stored Procedure");
CallableStatement proc = null;
proc = con.prepareCall("{ call prr_pkg.prr_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
proc.setString(1, propertyId_sp);
proc.setString(2, marketerId_sp);
proc.setString(3, contractId_sp);
Logger.log( TRACE, " 121");
proc.setArray(4, array6);
Logger.log( TRACE, " 122");
proc.setObject(5, array7);
Logger.log( TRACE, " 123");
proc.setArray(6, array1);
proc.setArray(7, array2);
proc.setArray(8, array3);
proc.setArray(9, array4);
proc.setArray(10, array5);
proc.setArray(11, array8);
//proc.registerOutParameter(12, sql.Type.NUMERIC);
//proc.registerOutParameter(13, sql.Type.STRING);
proc.execute();
The code compiles ok, but at run time I get a ClassCastException on the line :
ArrayDescriptor desc1 = (ArrayDescriptor)ArrayDescriptor.createDescriptor("RATETYPENBR_ARRTYPE", con);
What is the correct way for sending the array data to SP ?
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 09:40:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
