将空的sql数组发送到oracle过程中

Question

我正在尝试将 sql.Array 发送到过程中，但不知何故它在 oracle 过程内部变空。

我的java代码用数据填充sql.Array并尝试将其发送到过程中。

         oracle.jdbc.OracleConnection connection = (OracleConnection) JdbcConnection.getInstance().createConnection();

            String [] name = new String[]{"20206643799002684001","20206643799002684001"};

            ArrayDescriptor arrDec = ArrayDescriptor.createDescriptor("ARRAY_VARCHAR2",connection.unwrap(oracle.jdbc.OracleConnection.class));

            Array arr = new ARRAY(arrDec,connection.unwrap(oracle.jdbc.OracleConnection.class),name);

            OracleCallableStatement callableStatement = connection.prepareCall("{call bss_acc.ACC_STATEMENT(?,?,?,?)}");

            callableStatement.setDate(1,date);

            callableStatement.setDate(2,dateL);

           ((OracleCallableStatement)callableStatement).setARRAY(3,arr);

            callableStatement.setString(4,this.branchId);

            callableStatement.execute();

在程序中，我放置了日志，以便查看执行该程序后获得的值。我总是得到在此过程中显示的空数组元素。

procedure ACC_STATEMENT

(

i_begin_date date,

i_end_date date,

i_ids array_varchar2,

i_mfo varchar2

)

is

o_ext_acc varchar2(20);

o_beginRest number;

o_endRest number;

o_name varchar2(100);

o_rest number;

o_type varchar2(100);

begin

FOR i IN 1.. i_ids.count LOOP

o_ext_acc := i_ids(i);

-- test

RAISE_ERROR(20000, 'o_ext_acc -' ||o_ext_acc);

UPD 当我设置调试模式以查看 arr 发生了什么时，我注意到了这个数据。这意味着数组不从中获取值，并且它显示问号而不是它。

Answer 1

经过两天的搜索，主要问题是 datumnArray 是 ???。这意味着 oracld db 和 jdbc 与其字符集不匹配。添加此代码后。&nbsp;String s1 = new String(account.get(0).getBytes(), Charset.forName("ISO-8859-1"));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String [] name = new String[]{s1};&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //oracle.sql.ArrayDescriptor arrayDescriptor = new ArrayDescriptor("ARRAY_VARCHAR2",connection);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; oracle.sql.ARRAY a = connection.createARRAY("ARRAY_VARCHAR2", name);并将这个库添加到maven中<dependency>&nbsp; &nbsp; &nbsp; &nbsp; <groupId>com.oracle</groupId>&nbsp; &nbsp; &nbsp; &nbsp; <artifactId>orai18n</artifactId>&nbsp; &nbsp; &nbsp; &nbsp; <version>11.1.0.7.0</version>&nbsp; &nbsp; </dependency>这对我有用。我认为他们都是解决问题的关键。下面的图片开头。

Answer 2

使用oracle.sql.ARRAYandoracleConnection.setARRAY()而不是java.sql.Arrayand javaConnection.setArray()：Connection connection = JdbcConnection.getInstance().createConnection();OracleConnection oconnection = (OracleConnection) connection.unwrap( oracle.jdbc.OracleConnection.class );String [] name = new String[]{"20206643799002684001","20206643799002684001"};ArrayDescriptor arrDec = ArrayDescriptor.createDescriptor("ARRAY_VARCHAR2",oconnection);ARRAY arr = new ARRAY(arrDec,oconnection,name);/*&nbsp;* Or&nbsp;* ARRAY arr = oconnection.createARRAY("ARRAY_VARCHAR2", name);&nbsp;*/OracleCallableStatement callableStatement&nbsp; = (OracleCallableStatement) oconnection.prepareCall("{call bss_acc.ACC_STATEMENT(?,?,?,?)}");callableStatement.setDate(1,date);callableStatement.setDate(2,dateL);callableStatement.setARRAY(3,arr);callableStatement.setString(4,this.branchId);callableStatement.execute();