How to execute a packages procedure in Java with specific parameter structure

Question

Im working with java and oracle db and I have the following question:

In the oracle database I have a PACKAGE that is something like this:

 CREATE OR REPLACE PACKAGE PAC_EXAMPLE IS
 
  TYPE reg_ret IS RECORD (group NUMBER(1),market NUMBER(3),amount  NUMBER(16));
  
  TYPE tab_ret is table of reg_ret
  INDEX BY BINARY_INTEGER
  ;
 
  PROCEDURE P_CREATE_ELEMENT(
  pa_name IN VARCHAR2,
  pa_signature IN VARCHAR2,
  pa_ret IN tab_retenciones,
  pa_obl_id OUT NUMBER)
  ;
  --
 END K_SJUD_DER;

As you can see It has two auxiliar type: reg_ret and tab_ret.

When I want to execute this procedure in the database I just do it this way:

declare
  vl_obl_id number;
  --
  mi_ret PAC_EXAMPLE.reg_ret;
  obj PAC_EXAMPLE.tab_ret;
  --
begin
   mi_ret.group := 1;
   mi_ret.market := 110;
   mi_ret.amount := 500;
   obj(1) := mi_ret;
   mi_ret.group := 1;
   mi_ret.market := 95;
   mi_ret.amount := 300;
   obj(2) := mi_ret;

PAC_EXAMPLE.P_CREATE_ELEMENT('JHON','JHON R',obj,vl_obl_id);
dbms_output.put_line('output: '||vl_obl_id);
end;

First I have to create the types mi_ret and obj and then I could execute the procedure.

Well, my problem here is that I have to execute this procedure in Java with hibernate. I know that when you want to execute a procedure in Java you have to do something like this:

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("PAC_EXAMPLE.P_CREATE_ELEMENT")
                .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
                .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
                .registerStoredProcedureParameter("outId", Integer.class, ParameterMode.OUT)
                .setParameter(1, 'JHON');
                .setParameter(2, 'JHON R');
query.execute();
// get result
Integer outId = (Integer)storedProcedure.getOutputParameterValue("outId");

The thing here is that I cant find out how to create the types reg_ret and tab_ret in Java and send it by parameter to execute the procedure. I could use some help. Thanks!

Hi you will have to define a string which will contain the type name, check this link https://stackoverflow.com/questions/54344166/java-how-to-call-an-oracle-procedure-with-custom-types — Himanshu Kandpal, Mar 16 '23 at 17:27

MT0 · Answer 1 · 2023-03-16T21:22:20.450

If you can use a native query then just call your PL/SQL anonymous block. Something like (untested):

String plsql = "
declare
  vl_obl_id number;
  --
  mi_ret PAC_EXAMPLE.reg_ret;
  obj PAC_EXAMPLE.tab_ret;
  --
begin
   mi_ret.group := 1;
   mi_ret.market := 110;
   mi_ret.amount := 500;
   obj(1) := mi_ret;
   mi_ret.group := 1;
   mi_ret.market := 95;
   mi_ret.amount := 300;
   obj(2) := mi_ret;

PAC_EXAMPLE.P_CREATE_ELEMENT(?,?,obj,?);
end;
";
CallableStatement cs = connection.prepareCall(plsql);
cs.setString(1, "JHON");
cs.setString(2, "JHON R");
cs.registerOutParameter(3, Types.INTEGER);
cs.execute();
int obl_id = cs.getInt(3);

How to execute a packages procedure in Java with specific parameter structure

1 Answers1