Fetch pl/sql array return values in java

Question

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();

Simon Dorociak · Accepted Answer · 2012-05-29T07:59:49.810

4

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.

edited May 29 '12 at 07:59

answered May 28 '12 at 13:58

Simon Dorociak

33,374
10
68
106

I am trying your solution. In my Function I have 3 the in parameters and I would like to make return array as out parameter. However when I compile I am getting errors. How can I define the function to have in out as 4th paramater? Because in java I am getting exception `wrong number or types of arguments in call to 'myfunc'` Thanks – Jacob May 28 '12 at 14:44
First 3 parameters are in params and in java I have written like the following: `callablestatement.registerOutParameter(4, Types.ARRAY); callablestatement.execute(); resultSet = callablestatement.getArray(4).getResultSet();` – Jacob May 28 '12 at 14:56
do it like i wrote, out parameter have 1 index not 4, index 2,3,4 are your parameters which have your stored procedure – Simon Dorociak May 28 '12 at 14:59
1

`{? = call (?,?,?)}` it means that in to first **?** will be selected your `Array`, fist **?** is `OUT` parameter. Now i must go away but i will be back later. – Simon Dorociak May 28 '12 at 15:01
I have function, not stored procedure. Besides I have included my java code as update 1 above. Thanks – Jacob May 28 '12 at 15:01
to to be all clear, you have function but in other words, function is stored procedure that return value of specific type and you right it's usually named like function :-) so then give mi answer wether it works or not. – Simon Dorociak May 28 '12 at 17:02
I have added my modified code above as update 2. When I execute I am getting exception `java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type` – Jacob May 29 '12 at 05:39
So yeah man, it was bad solution, i looked at it and corrected code so check it. – Simon Dorociak May 29 '12 at 08:00
I used your solution, but did a small change. I have added my code as update 3 above. Thanks a lot for your help and support. – Jacob May 29 '12 at 08:13

score 1 · Answer 2 · answered May 28 '12 at 13:37

1

myArray returned from PL/SQL is of type java.sql.Array. You can invoke getArray() on it and type cast it to get java array.

String[] array = (String[]) myArray.getArray();

answered May 28 '12 at 13:37

Chandra Sekhar

18,914
16
84
125

Also note that you may need to provide a type map argument: `getArray(typeMap)`. For more information, see [my answer on getArray() and writeArray()](https://stackoverflow.com/a/45867910/197733) – Arlo Aug 24 '17 at 17:59

score 1 · Answer 3 · edited May 23 '17 at 12:31

1

Yes, you need to registerOutParameter of type Types.ARRAY
see example here

edited May 23 '17 at 12:31

Community

1
1

answered May 28 '12 at 13:41

A.B.Cade

16,735
1
37
53

score 1 · Answer 4 · answered May 28 '12 at 13:41

1

The JDBC Specification contains a whole section (16.5) dedicated to dealing with arrays. You might want to give it a look.

answered May 28 '12 at 13:41

Edwin Dalorzo

76,803
25
144
205

score 1 · Answer 5 · answered May 28 '12 at 13:42

I believe you can use the following method present in the SerialArray class which is a direct descendant of java.sql.Array:

Object getArray(Map<String, Class<?>> map)

Here the Map is a holder of an object and its property which is to be mapped to the fetched resultset in the returned array.

For more details see http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/serial/SerialArray.html javadoc

Fetch pl/sql array return values in java

5 Answers5

Linked