Requirement-It is very common requirement to call stored procedure which takes array as input parameter. In below example i am calling the stored procedure from Impl class of application module.
My procedure is having String of array as second parameter.When we want to pass String of array to Stored procedure we need to convert this String of array to Sql array type.For this we need below two things
- The array that we pass should be declared in data as type in database as shown in below CST_LTG_ARRAY_VARCHAR2 is declared as type in database.Same type will be used in stored procedure input parameter as shown in screenshot 2
We need to convert the java.sql.Connection to oracle.jdbc.OracleConnection object as shown in code snippet
Now use this OracleConnection reference to create array reference that we need to pass to stored procedure using below line.Remember minute details here -dont forget to mention schema name dot your type name in database in all uppercase otherwise code will throw java.sql.SQLException: invalid name pattern.
Array subSetCodesArray = oraConn.createARRAY("EL_EGWH_EXT_WORK.CST_LTG_ARRAY_VARCHAR2", subSetCodes);
Now final step is to pass this subSetCodesArray array to callableStatement setArray method as below
statement.setArray(2, subSetCodesArray);
Final method looks like below initializeDataSource is methd used to get connection reference from dataSource in weblogic server.
public String callApplyClusterStoredProcedure(Long batchNumber, String[] subSetCodes) {
DBTransactionImpl dbtrx = (DBTransactionImpl) getDBTransaction();
CallableStatement statement =
dbtrx.createCallableStatement(("BEGIN " + "SCHEMA_NAME.PROCEDURE_NAME(?,?,?,?);" + "END;"), 0);
try {
DataSource ds = initializeDataSource();
java.sql.Connection conn = ds.getConnection();
OracleConnection oraConn = conn.unwrap(OracleConnection.class);
statement.setLong(1, batchNumber);
if (subSetCodes != null) {
Array subSetCodesArray = oraConn.createARRAY("EL_EGWH_EXT_WORK.CST_LTG_ARRAY_VARCHAR2", subSetCodes);
statement.setArray(2, subSetCodesArray);
} else {
//use below code to set Null if array is empty or null
statement.setNull(2, java.sql.Types.ARRAY,"EL_EGWH_EXT_WORK.CST_LTG_ARRAY_VARCHAR2");
}
statement.registerOutParameter(3, java.sql
.Types
.VARCHAR);
statement.registerOutParameter(4, java.sql
.Types
.VARCHAR);
statement.execute();
//Long balanceToInclude = statement.getLong(4);
String result = statement.getString(3);
String message = statement.getString(4);
System.out.println("result -" + result);
System.out.println("message-" + message);
return result;
} catch (SQLException sqlerr) {
throw new JboException(sqlerr.getMessage());
} finally {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException closeerr) {
throw new JboException(closeerr.getMessage());
}
}
}
private DataSource initializeDataSource() {
InitialContext ctxt = null;
DataSource dataSource = null;
try {
ctxt = new InitialContext();
dataSource = (DataSource) ctxt.lookup("jdbc/EgwhDS");
ctxt.close();
} catch (Exception e) {
e.printStackTrace();
}
return dataSource;
}
