JDBC oracle stored procedure , handling nested object?

235 views Asked by At

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.

0

There are 0 answers