Fetch pl/sql array return values in java

java, jdbc, oracle, plsql

Solution

I didn't do that with `ARRAY` but it should works. First you must register `out` parameter of your function. So it can be like this.

private final String PRODECURE_NAME = "{? = call <ProcedureName>(?,?,?)}";

Connection con = null;
CallableStatement cs = null;

       try {
            con = DAOFactory.getDatabaseConnection();
            cs = con.prepareCall(PRODECURE_NAME);
            cs.registerOutParameter(1, java.sql.Types.ARRAY);
            cs.setYourType(2, <yourData>);
            cs.setYourType(3, <yourData>);
            cs.setYourType(4, <yourData>);
            cs.execute();
            Array arr = cs.getArray(1);
            if (arr != null) {
               String[] data = (String[]) arr.getArray();
            }
        } 
        catch (SQLException ex) {
            Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
            try {
                con.rollback();
            }
        }
        finally {
            if (con != null) {
                try {
                    con.close();
                } 
                catch (SQLException ex) {
                    Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

Try this man and give me then asnwer wether it do or not.

EDIT:

These char ? represents one parameter that you will set(it's named as parametrized). So this:

cs.setYourType(2, <yourData>);
cs.setYourType(3, <yourData>);
cs.setYourType(4, <yourData>);

means, that you set your three parameters (?), first parameter of method is column index and second are you data of your specific type.

EDIT 2:

So sorry i wrote bad solution, already updated so check code now and try it.

Problem

In Java how to get values from a `pl/sql` function which returns an array. if my pl/sql function returns array called `myArray`, in java is it possible to fetch values from `myArray` into java objects using `callablestatement`? Thanks Update 1 My Java code where I am calling function, but I am getting exception. ``` PLS-00306: wrong number or types of arguments in call to 'myfunc' connection = con.getConnection(); callablestatement = connection.prepareCall("{call myfunc(?,?,?}"); callablestatement.setInt(1, param1); callablestatement.setInt(2, param2); callablestatement.setString(3, param3); callablestatement.registerOutParameter(4, Types.ARRAY); callablestatement.execute(); resultSet = callablestatement.getArray(4).getResultSet(); ``` Update 2 ``` private final String PRODECURE_NAME = "{? = call myfunc(?,?,?)}"; ``` and ``` connection = con.getConnection(); callablestatement = connection.prepareCall(PRODECURE_NAME); callablestatement.registerOutParameter(1, Types.ARRAY); callablestatement.setInt(2, param1); callablestatement.setInt(3, param2); callablestatement.setString(4, param3); callablestatement.execute(); create or replace type dates is varray(100) of varchar2(32); ``` function ``` CREATE OR REPLACE function myfunc ( p_id IN number, p_c_id IN number, p_co_no IN number ) RETURN dates AS myarray contract_dates; par1 VARCHAR2 (32); par2 VARCHAR2 (32); ``` Fixed Update 3 ``` connection = con.getConnection(); callablestatement = connection.prepareCall("begin ? :=myfunc(?,?,?); end;"); callablestatement.registerOutParameter(1, OracleTypes.ARRAY, "DATES"); callablestatement.setInt(2, param1); callablestatement.setInt(3, param2); callablestatement.setString(4, param3); callablestatement.execute(); ```

Original source

Related problems