plsql - how to return associative array to java

Question

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.

Which versions of Oracle database and Oracle JDBC driver are you using ? There is a chapter about associative arrays in Oracle 12c JDBC Developer guide here: docs.oracle.com/database/121/JJDBC/oraint.htm#JJDBC28179 but Oracle 11.2 documentation doesn't mention them anywhere, could be that older versions of the database and the driver simply don't support them. — krokodilko
– krokodilko, Commented Jun 12, 2016 at 7:51

Luke Woodward · Accepted Answer · 2016-06-12 10:37:13Z

1

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:

[4, 1, null, 2, null, null, 3]

edited Jun 12, 2016 at 10:37

answered Jun 11, 2016 at 22:06

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

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

2 Comments

boxfish Over a year ago

Thanks for ur reply luke. As per your suggestion, I changed the code to use BINARY_INTEGER. TYPE NUMBER_ARRAY_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;. Also changed the registerIndexTableOutParameter like this getOracleStatement(cstmt).registerIndexTableOutParameter(bindOffset, m_maxLen, OracleTypes.NUMERIC, m_elemMaxLen);. I still see the same exception :-(

Luke Woodward Over a year ago

@boxfish: in case it helps I've added to my answer sample code I used to retrieve PL/SQL associative arrays with BINARY_INTEGER as the key datatype.

Collectives™ on Stack Overflow

plsql - how to return associative array to java

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related