通过JDBC向ORACLE存贮过程传入传出数组的问题(转)

摘要：本文针对使用JDBC调用Oracle存储过程时，当返回结果为字符串数组时会出现与所期望字符串不一致的现象展开讨论并给出了解决方法。
关键字：JDBC，J2EE，PL/SQL，存储过程，CallableStatement
1.引言
JDBC是J2EE的一项重要技术，它实现了与数据库交互的细节处理，提供了访问不同数据库的统一接口，从而降低应用程序操纵数据库的复杂程度。比如在访问Oracle数据库时，你只需加载Oracle的driver包，按标准的接口编程即可访问Oracle数据库。存储过程是一段在服务器上执行的程序，它在服务器端对数据进行处理，只把所需的经过筛选后的数据集返回客户端。这样，一方面可以利用服务器强大的计算能力和速度，另一方面避免把大量的数据从服务器下载到客户端，减少网络传输量，提高了处理效率。Oracle数据库支持存储过程功能。过程的编码是用PL/SQL语言实现的，PL/SQL是将过程化语言与结构化语言SQL结合，既具有面向过程语言的过程结构，又可以直接进行数据库的各种操作。
JDBC中的Statement对象用于将SQL语句发送到数据库中。实际上有三种Statement 对象：Statement、PreparedStatement和CallableStatement，区别在于发送不同类型的SQL语句：Statement对象用于执行不带参数的简单SQL 语句；PreparedStatement对象用于执行带或不带输入参数的预编译SQL语句；CallableStatement对象用于执行对数据库中存储过程的调用。
前两种Statement执行后大多返回结果集ResultSet对象，通过循环得到每条记录，在循环中通过getXXX方法获得相应字段。而CallableStatement除了返回结果集，还可以返回数组类型ARRAY，然后通过它的getArray或者getXXXArray方法直接得到相应的数据，而无需循环展开。
在Oracle数据库中，把和数组类似的数据类型定义为集合（collection），共有三种集合类型：索引表（Index-By Tables），嵌套表（Nested Tables）和变长数组（VARRAY）。
索引表是内存表，它只在内存中存储，而不在数据库中存储，所以它不能作为由JDBC调用的存储过程的输出参数。嵌套表和变长数组是在数据库中存储，可以用作由JDBC调用的存储过程的输出参数。二者的区别在于，变长数组有最大长度限制，并且不能删除单个元素，而嵌套表没有这些限制。
2．字符串数组获取异常
本文所要阐述问题是由JDBC调用存储过程在返回字符串数组时却得不到正确的字符串结果。
排除中文的问题，笔者在使用Statement和PreparedStatement获取数据库查询结果没有出现任何异常，使用CallableStatement获取简单的数据类型，如float，String等，即使是BigDecimal数组，float数组也都可以正常取到数据。但是对于String数组，得到的不是所期望的字符串，而是一个个以0X开头的既有字符又包含数字的串，这不是我们所要的结果。令人更加费解的是，整个编译和运行期间均没有出现错误提示。下面的程序就可以说明这种情况：
运行环境：Windows2000 Professional + Weblogic7.0 + Oracle9.2.0 。
程序采用Oracle嵌套表（Nested Tables）类型来返回结果。大致结构如下：



在数据库端用pl/sql过程处理数据查询，结果存到Nest Tables中。应用程序jsp通过JDBC的CalllableStatement来调用pl/sql过程，结果数据由Nest Tables类型转换为相应的array类型。
2.1程序源码：
Oracle端：
创建表：
create table test_table(myname varchar2(200),myid number(20,2));

向表中插入数据：
insert into test_table values('String1',101);
insert into test_table values('String2',102);

定义两个嵌套表类型：
create or replace type varchar2_table as table of varchar2(200);
create or replace type number_table as table of number(20, 2);

PL/SQL过程：
create or replace procedure test_proc(value1 out varchar2_table,
value2 out number_table) is
begin
value1 := varchar2_table();value2 := number_table();
value1.extend(5);value2.extend(5);
execute immediate ‘'select * from test_table’ bulk collect into value1, value2;
end test_proc;

Application Server端：testJsp.jsp：
......
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:oracleDB","test","test");
CallableStatement stmt = conn.prepareCall("BEGIN TEST_PROC(?,?); END;");
stmt.registerOutParameter(1,Types.ARRAY,"VARCHAR2_TABLE");
stmt.registerOutParameter(2,Types.ARRAY,"NUMBER_TABLE");
stmt.execute();
String[] strArray = (String[])((ARRAY)stmt.getArray(1)).getArray();
float[] fltArray = (float [])((ARRAY)stmt.getArray(2)).getFloatArray();
for(i = 0;i<strArray.length;i++ )
{
out.println(strArray[i] + "--" + fltArray[i]));
}
stmt.close();
conn.close();
……

程序中，使用如下方法得到结果数组，
String[] strArray = (String[])((ARRAY)stmt.getArray(1)).getArray();
float[] fltArray = (float [])((ARRAY)stmt.getArray(2)).getFloatArray();

先通过CallableStatement的getArray方法并转换成Oracle.sql.ARRAY对象，然后再由它的getArray
方法得到String数组，由getFloatArray得到float数组。
2.2运行结果：
0x537472696E6731--101
0x537472696E6732--102
2.3结果分析：
从运行结果可以看到，floatArray是一个float数组，得到是正确的结果，而stringArray是String数组，得到的不是数据库中存放的字符串，却变成以0x开头的字符串。首先考虑0x，16进制常数总是以0x或0X开头的，所以这是一个16进制的数；接着从16进制着手分析，16进制的一位在存储时占用4bit，而一个字符是以一个字节，即8bit存储，相当于两个16进制。因此把输出结果按两位的分析就会发现(53)16是’S’的Ascii码的16进制表示，相似的， (74)16是’t’、 (72)16是’r’、 (69)16是’i’、 (6E)16是’n’、 (67)16是’g’、 (31)16是’1’。连在一起就是’string1’，正好是数据库中存储的字符串。这可能是在某个转码阶段出现了异常，并且是软件系统本身的问题。
这有两种可能。一是在数据库端pl/sql存储过程取出字符串并放入nesttable类型变量中就可能出现错误字符串。二是在JDBC处理将nesttable转化成Array时出现了错误。针对第一种情况，笔者在sql*plus中直接运行 test_proc做了以下测试：

SQL> declare
2 i int;
3 column1 varchar2_table;
4 column2 number_table;
5 begin
6 test_proc('test_table','varchar2_column','number_column',column1,column2);
7 for i in 1.. column1.count loop
8 dbms_output.put_line('Row ' || i || ' is: varchar2_column:' || column1(i) || ', number_column:' || column2(i));
9 end loop;
10 end;
11 /
Row 1 is: varchar2_column:String1, number_column:101
Row 2 is: varchar2_column:String2, number_column:102

PL/SQL procedure successfully completed

从输出结果来看，String是正常的，因此可以排除第一种错误可能。
笔者采用的JDBC driver实现包是Oracle 9.2 自带的class12.zip，Weblogic应用服务器版本是7.0.1，编程时采用sun的jdk1.3.1提供的标准接口，可能是系统配置或兼容方面出现了问题。如果更换为其他的应用服务器也出现了类似的问题。足以说明这种问题出现是相当普遍的。笔者找到在编程时可以采用一定的处理来得到正确的结果，不过却以牺牲执行效率为代价。
3.补救措施
这类问题与常见的乱码问题不同，因为它的输出并不是’?’或着’□’等无效的字符，而是与正确的字符串有着Ascii码对应关系。因此可以将0x开头的字符串强行转化为正确的字符串。下面就是在testJsp中声明一个进行强制转换的方法，用它即可把从JDBC获取的String数组中每一个String逐个地转换为正确的字符串。

public static String convertToString(String s)
{
ByteArrayOutputStream baos = new ByteArrayOutputStream();
if((!(s.startsWith("0x"))) && (!(s.startsWith("0X")))) throw new IllegalArgumentException();
for(int i = 2; i < s.length(); i += 2)
{
char c = s.charAt(i);
if(i + 1 >= s.length()) throw new IllegalArgumentException();
char c1 = s.charAt(i + 1);
byte byt = 0;
if(c >= '0' && c <= '9') byt += (c - 48) * 16;
else if(c >= 'a' && c <= 'f') byt += ((c - 97) + 10) * 16;
else if(c >= 'A' && c <= 'F') byt += ((c - 65) + 10) * 16;
else throw new IllegalArgumentException();

if(c1 >= '0' && c1 <= '9') byt += c1 - 48;
else if(c1 >= 'a' && c1 <= 'f') byt += (c1 - 97) + 10;
else if(c1 >= 'A' && c1 <= 'F') byt += (c1 - 65) + 10;
else throw new IllegalArgumentException();
baos.write(byt);
}
return new String(baos.toByteArray());
}

然后，通过一个循环把column1中的所有字符串进行强制转换。
for(i = 0;i<strArray.length;i++ )
{
strArray[i] = convertToString(strArray[i]);
out.println(strArray[i] + "--" + fltArray[i]));
}

这样就可以输出正确的结果，即使是中文字符串也可以正确的还原。但是在数据量很大的情况下，增加这样一个循环无疑对程序的执行效率带来很大的损失。
4.结束语
这种异常是笔者在实际编程中出现的，带有一定的偶然性。笔者给出了其中一种应急措施，或许还有其他更好的方法，或许随着系统的不断升级，以及不同厂家产品相互兼容性的提高，在更新的服务器版本和JDBC driver下，将不再会出现这类错误。