plsql-如何将关联数组返回给Java [英] plsql - how to return associative array to java

问题描述

我正在尝试将关联数组返回给Java，但遇到异常.我使用的是专有的持久层，因此我将无法发布代码，但是在Google上搜索时，我发现了一些与我拥有的东西完全相似的东西:

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

谁能解释从jdbc访问该数据类型的正确方法是什么?

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.

谢谢.

推荐答案

我要伸出脖子，说没有直接方法可以从JDBC访问声明为TABLE OF varchar(30) INDEX BY VARCHAR(30)的数据类型.

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.

Oracle JDBC文档提到了关联数组的元素类型(即，您类型中的第一个varchar(30))在各个地方，但据我所知，它与键数据类型无关.此外，文档还提到关联数组作为Java数组传入并返回.这使我怀疑Oracle JDBC仅支持将BINARY_INTEGER作为关键数据类型的关联数组.

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.

因此，如果要使用JDBC中的VARCHAR2键访问PL/SQL关联数组中的数据，建议您首先将数据转换为另一种数据类型.

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.

但是，我希望一旦您在对registerIndexTableOutParameter的调用中将OracleTypes.VARCHAR更改为OracleTypes.VARCHAR时，您编写的JDBC代码将使用BINARY_INTEGER键处理关联数组.请注意，返回的Java数组中将包含与最大键值一样多的元素，因此请确保最大数目的元素(registerIndexTableOutParameter的第二个参数)足够大.另外，请确保关联数组没有负号或零键，因为JDBC驱动程序似乎也不支持这些键.

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.

作为参考，这是我用来获取声明为INDEX BY BINARY_INTEGER的关联数组的代码.首先，PL/SQL包和主体:

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;
/

第二，Java类:

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

运行Java类时，得到以下输出:

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

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

这篇关于plsql-如何将关联数组返回给Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！