Multiple tables as output parameters in Procedure using hibernate

Question

CREATE OR REPLACE PACKAGE BODY Pkg_name
IS

PROCEDURE proceure_Name (
Id             IN     Long,
startDate   IN     DATE,
EndDate     IN     DATE,

Table1     OUT    Table1_Name%ROWTYPE,
Table2   OUT    Table2_Name%ROWTYPE,
errormsg           OUT    VARCHAR2)
IS

Record another_Table%ROWTYPE;

I tried calling this procedure in hibernate as follows:

Long id = 111111111111111L;
Date startDate = setDateTime("08/31/2023 00:00:00"); 
Date endDate = setDateTime("09/01/2018 00:00:00"); // declaring stored Procedure 
StoredProcedureQuery query = this.em.createStoredProcedureQuery("package_name.procedure_name"); // input parameters 
query.registerStoredProcedureParameter("Id", Long.class, ParameterMode.IN); 
query.setParameter("in_regEntId", id); 
query.registerStoredProcedureParameter("startDate", Date.class, ParameterMode.IN); 
query.setParameter("in_chPeriodStartDate", startDate); 
query.registerStoredProcedureParameter("EndDate", Date.class, ParameterMode.IN); 
query.setParameter("in_chPeriodEndDate", endDate); // register output Parameters 
query.registerStoredProcedureParameter("Table1", Table1_name.class, ParameterMode.OUT); 
query.registerStoredProcedureParameter("Table2", Table2_name.class, ParameterMode.OUT); 
query.registerStoredProcedureParameter("errormsg", String.class, ParameterMode.OUT); // executing the stored Procedure 
query.execute(); // output 

Problem:

so while registering the output parameter I am getting an error , as invalid type Table1_name,

I am Not sure what type should I be giving there .

Answer 1

Nhibernate doesnt suppport returning complex structures from stored procedures as %rowtype. Change your stored procedure to return SYS_REFCUSOR instead. For example:

  PROCEDURE procedure_Name (
    Id        IN     Long,
    startDate IN     DATE,
    EndDate   IN     DATE,
    Table1    OUT    SYS_REFCURSOR,
    Table2    OUT    SYS_REFCURSOR,
    errormsg  OUT    VARCHAR2
  )
  IS
  BEGIN
    //etc....
  END;

Then update your nhibernate code to something like it:

Long id = 111111111111111L;
Date startDate = setDateTime("08/31/2023 00:00:00"); 
Date endDate = setDateTime("09/01/2018 00:00:00");

StoredProcedureQuery query = this.em.createStoredProcedureQuery("package_name.procedure_name");

query.registerStoredProcedureParameter("Id", Long.class, ParameterMode.IN);
query.setParameter("Id", id);
query.registerStoredProcedureParameter("startDate", Date.class, ParameterMode.IN);
query.setParameter("startDate", startDate);
query.registerStoredProcedureParameter("EndDate", Date.class, ParameterMode.IN);
query.setParameter("EndDate", endDate);

query.registerStoredProcedureParameter("Table1", OracleTypes.CURSOR, ParameterMode.REF_CURSOR);
query.registerStoredProcedureParameter("Table2", OracleTypes.CURSOR, ParameterMode.REF_CURSOR);
query.registerStoredProcedureParameter("errormsg", String.class, ParameterMode.OUT);

query.execute();

// read output parameters
List<Object[]> resultsOfTable1 = query.getResultList();
List<Object[]> resultsOfTable2 = query.getResultList();
String error= query.getOutputParameterValue("errormsg");

Also remmember to add an import: import oracle.jdbc.OracleTypes;