1

From Spring boot application I am trying to call Oracle Procedure with multiple IN and OUT parameters. One of the OUT parameters is of nested custom Object type. I'm looking for a way to call the procedure from Java code and return all the output parameters.

create or replace TYPE  DS_INT_RMS_VOLUME_TBL IS TABLE OF PUB.DS_INT_RMS_VOLUME_OBJ;
create or replace TYPE     DS_INT_RMS_VOLUME_OBJ AS OBJECT
(
 order_month         VARCHAR2(20),
 ds_ppv              NUMBER,
 ds_dlv              NUMBER,
 ds_pv               NUMBER,
 ds_gv               NUMBER,
 ds_tv               NUMBER,
 ds_ro               NUMBER,
 ds_mpv              NUMBER,
 ds_mtv              NUMBER,
 ds_3ppv             NUMBER,
 ds_12ppv            NUMBER,
 ds_cv               NUMBER,
 chr_attribute1      NUMBER,
 chr_attribute2      NUMBER,
 dte_attribute2      DATE,
 ds_3dlv             NUMBER,
 ds_12dlv            NUMBER,
 ds_6ppv             NUMBER,
 ds_6dlv             NUMBER,
 ds_2ppv             NUMBER,
 mb_points           NUMBER,
 rms_volumes         ds_rms_vol_obj_tab,
 country_volumes         CTRY_INT_VOLTYPE_TAB,
 cdlv           NUMBER,
    CONSTRUCTOR FUNCTION DS_INT_RMS_VOLUME_OBJ
      RETURN SELF AS RESULT
)

create or replace TYPE     CTRY_INT_VOLUMES_TAB IS TABLE OF CTRY_INT_VOLUMES_OBJ;

create or replace TYPE     CTRY_INT_VOLUMES_OBJ AS OBJECT
(
 Country_code            VARCHAR2(100),
 Rms_volume_points      ds_rms_vol_obj_tab,
    CONSTRUCTOR FUNCTION CTRY_INT_VOLUMES_OBJ
      RETURN SELF AS RESULT
);

The above code is custom type in oracle db

The procedure inside a package is below:

PROCEDURE get_rms_volumes_info (
      out_chr_err_code               OUT VARCHAR2,
      out_chr_err_msg                OUT VARCHAR2,
      out_chr_ds_id                  OUT VARCHAR2,
      out_ds_volume_tab              OUT DS_INT_RMS_VOLUME_TBL,
      out_ds_primary_volume          OUT NUMBER,
      ...some more out paramter of type number....
      in_chr_debug                IN     VARCHAR2 DEFAULT g_chr_no,
      in_chr_service_consumer     IN     VARCHAR2,
      in_chr_ds_id                IN     VARCHAR2,
      in_chr_from_month           IN     VARCHAR2,
      in_chr_to_month             IN     VARCHAR2,
      include_Order_purpose       IN     VARCHAR2,
      inventory_order_month       IN     VARCHAR2);

Code I tried

public Map<String, Object> getVolumesInfo() {
        SqlReturnArray sqlReturnArray=new SqlReturnArray();
        SimpleJdbcCall simpleJdbcCall=new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("get_rms_volumes_info")
                .withCatalogName("DS_INT_COMMON_PKG")
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(new SqlParameter[]{
                        new SqlOutParameter("out_chr_err_code", Types.VARCHAR),
                        new SqlOutParameter("out_chr_err_msg", Types.VARCHAR),
                        new SqlOutParameter("out_chr_ds_id", Types.VARCHAR),
                        new SqlOutParameter("out_ds_volume_tab", Types.ARRAY,"DS_INT_RMS_VOLUME_TBL",sqlReturnArray),
                        .....some more out parameters.....,
                        new SqlParameter("in_chr_debug", Types.VARCHAR),
                        new SqlParameter("in_chr_service_consumer", Types.VARCHAR),
                        new SqlParameter("in_chr_ds_id", Types.VARCHAR),
                        new SqlParameter("in_chr_from_month", Types.VARCHAR),
                        new SqlParameter("in_chr_to_month", Types.VARCHAR),
                        new SqlParameter("include_Order_purpose", Types.VARCHAR),
                        new SqlParameter("inventory_order_month", Types.VARCHAR),

                        }
                );
        Map<String,String> in = new HashMap<>();
        in.put("in_chr_debug","N");
        in.put("in_chr_service_consumer","MYHL");
        in.put("in_chr_ds_id","STAFF");
        in.put("in_chr_from_month","202208");
        in.put("in_chr_to_month","202208");
        in.put("include_Order_purpose","Y");
        in.put("inventory_order_month","Y");
        Map<String,Object> out=new HashMap<>();
        out=simpleJdbcCall.execute(in);
        return out;
    }

The above code gives error "com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Direct self-reference leading to cycle (through reference chain: java.util.LinkedHashMap["out_ds_volume_tab"]-> oracle.sql.STRUCT[0]->oracle.sql.STRUCT["physicalConnection"]->oracle.jdbc.driver.T4CConnection["wrapper"])"

Aishwarya B
  • 21
  • 3
  • May be Jackson doesn't like your "country_volumes CTRY_INT_VOLTYPE_TAB": is it a a TABLE OF inside a TABLE OF ? Why didn't you try to return directly a JSON payload from your procedure? – p3consulting Apr 09 '23 at 06:52
  • @p3consulting I have edited the question with CTRY_INT_VOLTYPE_TAB type..Unfortunately I cannot make any changes to procedure – Aishwarya B Apr 09 '23 at 07:13
  • So you have at least 3 levels of nested TABLE OF... "Rms_volume_points ds_rms_vol_obj_tab" being another erTABLE OF... If you can't change the procedure, could you create a new new one calling the original one and converting into JSON in PL/SQL ? – p3consulting Apr 09 '23 at 08:32
  • Possible duplicate of https://stackoverflow.com/q/54344166/1509264 – MT0 Apr 09 '23 at 08:42
  • @MTO, not exactly a dup but same family of question indeed, here he needs a TABLE OF OBJECT not a simple OBJECT, but that make me think that Types.ARRAY is inappropriate here, I will not be surprised that a TABLE OF OBJECT is returned as a OracleTypes.CURSOR. – p3consulting Apr 09 '23 at 09:42
  • Or go the JDBC, uses OracleCallableStatement and registerOutParameter(…, OracleTypes.ARRAY,…) – p3consulting Apr 09 '23 at 12:07
  • I used `StructMapper MAPPER =BeanPropertyStructMapper.newInstance(DsIntRmsVolumeObj.class);` `new SqlOutParameter("out_ds_volume_tab", Types.ARRAY,"DS_INT_RMS_VOLUME_TBL",new SqlReturnStructArray(MAPPER))` I created class **DsIntRmsVolumeObj implements SQLData** and overridden getSQLTypeName(),readSQL(SQLInput stream, String typeName),writeSQL(SQLOutput stream) I am able to get all the out parameters except rms_volumes and country_volumes – Aishwarya B Apr 09 '23 at 14:23

0 Answers0