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
– Andy, Commented Dec 16, 2011 at 20:00
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
– Bytekoder, Commented Dec 16, 2011 at 20:10
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
– Andy, Commented Dec 16, 2011 at 20:23
How are we supposed to guess what you do in callStoredProcedure()? — maximdim
– maximdim, Commented Dec 16, 2011 at 20:23

maximdim · Accepted Answer · 2011-12-16 20:46:42Z

1

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

answered Dec 16, 2011 at 20:46

maximdim

8,2073 gold badges36 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bytekoder Over a year ago

Thanks people for your support but I made it work. I just called the function inside a Stored Procedure and it worked. I had that thought before but for some reason I think I didn't try harder.

Bytekoder Over a year ago

You are right but the thing is we are using a certain design pattern and I didn't want to violate it. No one thought before that we might end up using table objects and thus we never exposed such a utility that would make a direct function call easier.

Collectives™ on Stack Overflow

Call Oracle function from Java

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related