Pass array as input parameter to oracle stored procedure using simple jdbc call
Here are my oracle procedure specs
CREATE OR REPLACE PACKAGE PKG_RE_FI AS
PROCEDURE PRC_RE_FI_DETAILS(P_FAN_NO IN VARCHAR2,
P_REF_ID IN TY_APP_REF_ID,
P_COMMENTS IN VARCHAR2,
P_BILLING_FLAG IN VARCHAR2,
P_STATUS OUT VARCHAR2);
END PKG_RE_FI;
TY_APP_REF_ID
CREATE OR REPLACE TYPE ty_app_REF_ID as varray(500) of obj_array_ref_id
CREATE OR REPLACE TYPE obj_array_ref_id AS OBJECT(
app_ref_id VARCHAR2(100)
)
I am using Spring JDBC Framework (SimpleJdbcCall object) to complete the above procedure. Below is the code snippet where I have declared
this.reFIJdbcCall = new SimpleJdbcCall(dataSource).withCatalogName("PKG_RE_FI").
withProcedureName("PRC_RE_FI_DETAILS").declareParameters(new SqlParameter("P_FAN_NO", Types.VARCHAR),
new SqlParameter("P_REF_ID", Types.ARRAY),
new SqlParameter("P_COMMENTS", Types.VARCHAR),
new SqlParameter("P_BILLING_FLAG", Types.VARCHAR),
new SqlOutParameter("P_STATUS", Types.VARCHAR)
);
How to pass an array to
new SqlParameter("P_REF_ID", Types.ARRAY),
at MapSqlParameterSource
MapSqlParameterSource in = new MapSqlParameterSource();
+1
source to share
2 answers
PeudoCode for the same as I achieved.
# 1.You will require a structDescriptor object for an object equivalent in pl sql like :
StructDescriptor structDes= new StructDescriptor("<schemaname in caps>.<sql_object_name>", connectionObject);
# 2. You will need to pass one object values such name, class, id to an object array in order and accordance to 'sql_object_name' object.
For exmaple:
STRUCT[] structArray=new STRUCT[<ListObj>.size()];
int index=0;
for (a in ListObj){
Object[] object=new Object[]{a.getName(),a.getId()};
STRUCT struct=new STRUCT(structDes ,connectionObject,object);
structArray[index]=struct;
index++;
}
ArrayDescriptor arrayDes=ArrayDescriptor.createDescriptor(
"<Schema name>.<table object from sql>", connectionObject);
ARRAY array=new ARRAY(arrayDes,connectionObject, structArray);
then pass it to proc
.declareParameters(
new SqlInOutParameter("<parameter to proc name>",OracleTypes.ARRAY,"
<schema name>.<sql_array_or_table_obj>"))
like
Hashmap<String, Object> map= new HashMap<>();
map.put("<parameter to proc name>",array);
psStatement.execute(map);
Hope this helps. This sequence can vary depending on the requirement and the type of sql database used, but the base is the same.
+1
source to share
The Spring Data JDBC Extensions project has some support that makes this easier. Take a look at the Oracle ARRAY transfer reference manual .
+2
source to share