2

I am trying to create a generic class to be used in all of my application, to make stored procedure calls with using three variables: StoredProcedure Name, Map of Input variables and Map of output variables.

The return of these call should return the result of the stored procedure that the user passed in the function.

I tried to used different ways to calling the stored procedure but i have to used JPA to do it. I search all internet but it seems nobody had problems using ARRAYS as input argument in Java.

I already put as arguments an Object[] but i am getting an error saying that "the variable XXX is not defined"

Global Function for calling stored procedures

@Override
    public List<Object> executeArrayProcedure(String procedureName, Map<String, Object[]> inputParams, Map<String, Class> outputParams) {
        List<Object> results = new ArrayList<>();
        try {
            StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery(procedureName);
            // register parameters
            for (Entry<String, Object[]> entry : inputParams.entrySet()) {
                storedProcedure.registerStoredProcedureParameter(entry.getKey(), entry.getValue().getClass(),ParameterMode.IN);
                // set parameters   
                storedProcedure.setParameter(entry.getKey(), entry.getValue());
            }
            for (Entry<String, Class> entry : outputParams.entrySet()) {
                storedProcedure.registerStoredProcedureParameter(entry.getKey(),entry.getValue(), ParameterMode.OUT);
            }
            // execute SP
            storedProcedure.execute();
            // get result
            for (Entry<String, Class> entry : outputParams.entrySet()) {
                results.add(storedProcedure.getOutputParameterValue(entry.getKey()));
            }           
        } catch (Exception e) {
            e.printStackTrace();
        }
        return results;
    }

Stored procedure saved in my database

CREATE OR REPLACE TYPE intArrType AS VARRAY(100) OF NUMBER;

create or replace PROCEDURE testing (t_in IN intArrType, yout OUT number) IS
  BEGIN
    yout := 0;
    FOR i IN 1..t_in.count LOOP
      yout := yout + t_in(i);
    END LOOP;
  END;

declare
 yout number;
begin
  testing(intArrType(1,2,3),yout); 
  DBMS_OUTPUT.PUT_LINE(yout); 
end;

Error when passed Object[]

erro: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults

ORA-06550: 
linha 1, coluna 7:\nPLS-00306: wrong number or types of arguments in call to 'testing'\n
ORA-06550: linha 1, coluna 7:\n
PL/SQL: Statement ignored\n

Do you have any tip for me?

Bruno Nogueira
  • 23
  • 4
  • The problem is likely that Hibernate is not capable of converting the `Object[]` parameter to the appropriate DB type. I'd strongly recommend using plain JDBC here. There's no benefit to using JPA in your example, and with JDBC you should simply be able to do `CallableStatement.registerOutParameter(n, Types.ARRAY)`. See [this question](https://stackoverflow.com/questions/5198856/pass-array-to-oracle-procedure) for ways to pass the array into `CallableStatement` objects – crizzis Apr 01 '19 at 16:24

0 Answers0