I am trying to invoke an Oracle stored procedure , which has a combination of nested Object and tables as below ,
CREATE OR REPLACE PROCEDURE MYSCHEMA.MY_PROC(
p_obj_school IN OUT OBJ_SCHOOL) IS ...
With the following object type
CREATE TYPE "OBJ_SCHOOL" as OBJECT (Students LST_STUDENTS);
CREATE TYPE "LST_STUDENTS" AS TABLE OF STUDENTS;
CREATE TYPE "STUDENTS" as OBJECT(Subjects LST_SUBJECT);
CREATE TYPE "LST_SUBJECT" AS TABLE OF SUBJECT;
CREATE TYPE "SUBJECT" as OBJECT(Marks Number, Remark VARCHAR2(1000 BYTE));
And am unsure on how to implement this.
Whilst I tried a similar stored procedure with the following with parameter working
CREATE OR REPLACE PROCEDURE MYSCHEMA.MY_PROC2(
p_obj_school IN OUT OBJ_SCHOOL2 , id OUT Number ) IS ...
Whose type is relatively simple like below
CREATE TYPE "OBJ_SCHOOL2" AS OBJECT (SEQID NUMBER,
STUDENTS OBJ_STUDENTS)
CREATE TYPE "OBJ_STUDENTS" AS OBJECT (STUDENTNAME VARCHAR2(50 BYTE),CLASS VARCHAR2(2 BYTE),
STUDENTID NUMBER)
Using the java snippet like below
OBJ_SCHOOL2 objSchool2Request = new OBJ_SCHOOL2();
objSchool2Request.setSEQID(1);
OBJ_STUDENTS objStudentsRequest = new OBJ_STUDENTS();
objStudentsRequest.setSTUDENTNAME("name");
objStudentsRequest.setCLASS("4B");
objStudentsRequest.setSTUDENTID(1);
objSchool2Request.setOBJ_STUDENT(objStudentsRequest);
//where the mapper class is OBJ_SCHOOL2 pojo that implements SQLDATA
final OBJ_SCHOOL2_MAPPER mapper = new OBJ_SCHOOL2_MAPPER(objSchool2Request);
SimpleJdbcCall simpleJdbcCall =
new SimpleJdbcCall(dataSource())
.withSchemaName("MYSCHEMA")
.withProcedureName("MY_PROC2")
.declareParameters(new SqlInOutParameter("P_OBJ_SCHOOL", OracleTypes.STRUCT , "MYSCHEMA.OBJ_SCHOOL2" ))
.declareParameters(new SqlOutParameter("ID", OracleTypes.NUMBER));
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("p_obj_school", mapper);
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
And I could successfully invoke the stored procedure.
However , for the earlier mentioned Stored Proc (MYSCHEMA.MY_PROC) , I am not sure on how to implement nested object that includes "table" types.