Call Oracle function from Java

Question

I have following function specification:

FUNCTION FUNC_GET_SOMETHING_FROM_DATABASE ( IN_parameter1           IN VARCHAR2,
                                            IN_parameter2           IN VARCHAR2,
                                            IN_parameter3           IN VARCHAR2,
                                            IN_parameter4           IN VARCHAR2,
                                            IN_parameter5           IN VARCHAR2,
                                            IN_parameter6           IN VARCHAR2) 

RETURN REFCURTYP;

Following is my method in Java that is calling the function in Oracle:

public List<SomeVO> getLogReport(
            String parameter1, String parameter2, String parameter3,
            String parameter4, String parameter5,
            String parameter6) throws BlahException, RemoteDataAccessException {

        Vector<Object> params = new Vector<Object>();

        DataCollectionImpl<LogReportVO> someData = new DataCollectionImpl<LogReportVO>(
                LogReportVO.class);

                // IN Parameters

        params.add(parameter1);
        params.add(parameter2);
        params.add(parameter3);
        params.add(parameter4);
        params.add(parameter5);
        params.add(parameter6);


        //Out Parameter
        params.add(new DBParameter(DBParameter.OUT, DBParameter.CURSOR));

        try {
            callStoredProcedure(
                    Constants.FUNC_GET_SOMETHING_FROM_DATABASE, params);
        } catch (RemoteDataAccessException e) {
            throw new BlahException("LogReportDAO",
                    "getLogReport", e.getMessage(),
                    e.getRealException());
        }
        return someData.getDataCollectionObjects();
    }

The error I am getting is:

wrong number or types of arguments in call

Additional Information:

protected void callStoredProcedure(String procedureName, Vector params) throws RemoteDataAccessException {
        callStoredProcedure(getSchema(), procedureName, params);
    }

    protected void callStoredProcedure(String schema, String procedureName, Vector params) throws RemoteDataAccessException {
        callStoredProcedure(getDatasource(), schema, procedureName, params);
    }

    protected void callStoredProcedure(String dataSourceName, String schema, String procedureName, Vector params) throws RemoteDataAccessException {
        getOracleConnection(dataSourceName).callStoredProcedure(
                getFullyQualifiedProcedureName(schema, procedureName), params);
    }

    private OracleConnection getOracleConnection(String datasource) {   
        OracleConnection oraConn = null;
        try {   
            oraConn = new OracleConnection(datasource); 
        } catch (RemoteDataAccessException rdae) {  
            log.fatal("BaseDAO.getOracleConnection " + rdae.getMessage(), rdae);
        } catch (Exception ie) {    
            log.fatal("BaseDAO.getOracleConnection" + ie.getMessage(), ie);
        }

        return oraConn;
    }

ever think about using a wrapper for JDBC, like Mybatis3. It makes life easier when working with complex types. code.google.com/p/mybatis — Andy
You are right but right now time is the factor so I dont think that would be possible. I think I am having a problem with the OUT parameter. Do you know any way to get around this? — Bytekoder
I don't really. I know all of this possible with MyBatis. Out parameters are just set into the field on the parameter object. Sorry I couldn't come up with an answer for you. — Andy
How are we supposed to guess what you do in callStoredProcedure()? — maximdim

maximdim maximdim · Accepted Answer · 2011-12-16T20:46:42

Most likely your code is not taking into account stored procedure return value.

Why not to use standard JDBC way of calling stored procedure instead? It works like a charm.

http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/callablestatement.html

Call Oracle function from Java

1 Answers