plsql - how to return associative array to java

I am trying to return an associative array to java but facing exceptions. I am using a proprietary persistence layer so I won't be able to post my code but while searching on google I found something which is exactly similar to what I have:

===========================

create or replace PACKAGE testLookAside as
type AssocArry IS TABLE OF varchar(30) INDEX BY VARCHAR(30);
function lookupMasterData return AssocArry;
end testLookAside;
/
create or replace PACKAGE BODY testLookAside as
function lookupMasterData_ return AssocArry as
retval AssocArry;
begin
retval('1') := '1';
retval('2') := '2';
retval('3') := '3';
retval('4') := '4';
return retval;
end lookupMasterData_;
/
function lookupMasterData return AssocArry as
retVal AssocArry;
begin
retVal := lookupMasterData_();
return retVal;
end lookupMasterData;
end testLookAside;

Statement s = null;;
Class.forName("oracle.jdbc.driver.OracleDriver");
// set up connection here....
s=con.createStatement();

//String query = "begin ? := DEVELOPER.testLookAside.lookupMasterData(); end;";
String query = "{? = call DEVELOPER.testLookAside.lookupMasterData()}";

OracleCallableStatement stmt = (OracleCallableStatement)con.prepareCall(query);

// register the type of the out param - an Oracle specific type
stmt.registerIndexTableOutParameter(1, 30, OracleTypes.VARCHAR, 30);

stmt.execute();

And I kept getting errors like:

Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Could anyone explain what the right way is to access that datatype from jdbc?

Also, what should I do if my custom type uses number and binary integer like this:

type AssocArry IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

I am trying to solve this problem from last one week and looked into numerous threads with no results.

Thanks.

I am going to stick my neck out and say that there isn't a direct way to access a datatype declared as TABLE OF varchar(30) INDEX BY VARCHAR(30) from JDBC.

The Oracle JDBC documentation mentions the element type of the associative array (i.e. the first varchar(30) in your type) in various places, but as far as I can see it says nothing about the key datatype. Furthermore, the documentation mentions that associative arrays are passed in and returned as Java arrays. This leads me to suspect that Oracle JDBC only supports associative arrays with BINARY_INTEGER as the key datatype.

So, if you want to access data in a PL/SQL associative array with VARCHAR2 keys from JDBC, I would recommend converting the data into another datatype first.

However, I would expect that the JDBC code you've written will handle your associative array with BINARY_INTEGER keys, once you change OracleTypes.VARCHAR for OracleTypes.NUMERIC in your call to registerIndexTableOutParameter. Be aware that the Java array returned will have as many elements in it as the largest key value, so ensure that the maximum number of elements (the second parameter to registerIndexTableOutParameter) is large enough for this. Also make sure that the associative array has no negative or zero keys as the JDBC driver appears not to support these either.

For reference, here's the code I used to get associative arrays declared as INDEX BY BINARY_INTEGER working. Firstly, the PL/SQL package and body:

create or replace PACKAGE testLookAside as
  type AssocArry IS TABLE OF number INDEX BY binary_integer;
  function lookupMasterData return AssocArry;
end testLookAside;
/

create or replace PACKAGE BODY testLookAside as
  function lookupMasterData return AssocArry as
    retval AssocArry;
  begin
    retval(2) := 1;
    retval(4) := 2;
    retval(7) := 3;
    retval(1) := 4;
    return retval;
  end lookupMasterData;
end testLookAside;
/

Secondly, the Java class:

import java.math.BigDecimal;
import java.sql.*;
import java.util.Arrays;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class AssocArrayTest {
    public static void main(String[] args) throws Exception {
        Connection c = DriverManager.getConnection("url", "user", "password");
        OracleCallableStatement s = (OracleCallableStatement)c.prepareCall("{? = call testLookAside.lookupMasterData }");
        s.registerIndexTableOutParameter(1, 30, OracleTypes.NUMERIC, 0);
        s.execute();
        BigDecimal[] data = (BigDecimal[])s.getPlsqlIndexTable(1);
        System.out.println(Arrays.toString(data));
    }
}

When I run the Java class, I get the following output: