ORACLE存储过程的数组参数

ORACLE存储过程的数组参数

http://www.examw.com/biancheng/oracle/158294/

环境：Eclipse+Oracle9.0.2+Tomcat5.5

功能：采用存储过程、type组合来实现批量入库，以节省系统开销，提高效率。

*

sql脚本+测试代码：

* 1）create or replace type t_cableLine_point as object

（

ID NUMBER（10），

CABLELINEID NUMBER（10），

ROADPOINTID NUMBER（10），

ORDERNUM NUMBER（10），

REMARK NUMBER（10）

）

* 2）CREATE OR REPLACE TYPE ARRAY_cableLine_point AS table OF t_cableLine_point

* 3）create table RSC_CABLELINE_POINT

（

ID NUMBER（10） not null，

CABLELINEID NUMBER（10） not null，

ROADPOINTID NUMBER（10） not null，

ORDERNUM NUMBER（10），

REMARK NUMBER（10）

）

* 4）create or replace procedure batch_cableline_point（i_object in ARRAY_cableLine_point） is

begin

insert into RSC_CABLELINE_POINT

（ID， CABLELINEID， ROADPOINTID， ORDERNUM， REMARK）

select ID， CABLELINEID， ROADPOINTID， ORDERNUM， REMARK

from the （select cast（i_object as ARRAY_cableLine_point） from dual）;

end batch_cableline_point;

* 5）测试代码：

package com.nilpower.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

public class Test {

public static void main（String［］ args） {

try {

Class.forName（“oracle.jdbc.driver.OracleDriver”）;

String url = “jdbc:oracle:thin：@127.0.0.1:1521:nilpower”;

Connection con = DriverManager.getConnection（url， “scott”， “tiger”）;

PreparedStatement pstmt = null;

String sql = “{call batch_cableline_point（？）}”;

pstmt = con.prepareCall（sql）;

Object［］［］ object1 = new Object［10］［5］;

int max = 3615142;// 由于表有索引

for （int i = 0; i 《 10; i++） {

object1［i］［0］ = ++max;

object1［i］［1］ = 158870593;

object1［i］［2］ = 333;

object1［i］［3］ = 444;

object1［i］［4］ = 555;

}

oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor

.createDescriptor（“ARRAY_CABLELINE_POINT”， con）;

oracle.sql.ARRAY array = new oracle.sql.ARRAY（desc， con， object1）;

pstmt.setArray（1， array）;

pstmt.executeUpdate（）;

} catch （Exception e） {

e.printStackTrace（）;

}

}

}

备注：如果在入库的过程中发现字符串的值没有入进去，请检查有没有加载该类库nls_charset12.jar

* 该文章是参考“风雪”文章后个人试验的存档。

*

* 其他参考：（引用2008-5-24 04:49 Aowken）

* Tomcat+Oracle调用存储过程郁闷之旅

今天在改公司管理系统的时候，因为某个功能需要使用数组类型作为IN参数调用存储过程，看了看文档发现有Varray、nested table，但Varray有个最多数量的限制，也只好用nested table了，于是引发一连串的问题。

环境：

java version “1.6.0_05”

Java（TM） SE Runtime Environment （build 1.6.0_05-b13）

Java HotSpot（TM） Client VM （build 10.0-b19， mixed mode， sharing）

apache-tomcat-6.0.16

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows： Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

本来对Java这种据说很先进的东西就懵懵懂懂，索性就真的以为他非常牛X。使用几维数组作为参数调用存储过程还不是跟Set个String一样那么简单，但其实我错了，所以我也对java很失望，他远不如想象中那么XX。

Object arrInt［］ = {0，1，2，3，4，5，6};

callStmt.SetObject（1， arrInt， Types.ARRAY）;

要是想像上面这样操作他就会抛个java.lang.ClassCastException： ［Ljava.lang.Object; cannot be cast to oracle.sql.ARRAY，于是我知道java他不会自己转，非得人工干预。但我突然发现自己很愚蠢，我都没告诉他procedure参数的类型，即使可以转过去又有个P用，百度了一下才知道得用下面的法子。

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

Connnection conn = DBConnManager.getConnection（）;

callStmt = conn.prepareCall（sql）;

ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor（“dbms_sql.Varchar2_Table”， conn）;

ARRAY arr = new ARRAY（arrDesc， conn， arrInt）;

执行一下，结果异常。我靠数据库里能用dbms_sql.Varchar2_Table声明，他居然 java.sql.SQLException： 无效的名称模式： DBMS_SQL.VARCHAR2_TABLE。我心想是不是得写成SYS.dbms_sql.Varchar2_Table，结果还是一样。再百度，人们说这样不行，原因。。.不知道，但必须得自己定义类型才可以。于是我不得不

create type numbertable as table of number;

ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor（“numbertable”， conn）;

ARRAY arr = new ARRAY（arrDesc， conn， arrInt）;

结果又来了个java.sql.SQLException： 无效的名称模式： baby.numbertable。我无语还得百度！@#￥%.。。.N久无果！但我发别人的码的代码里这个类型都是大写，于是我也写成 NUMBERTABLE。哈哈，果然不出那个异常了。但他NND又蹦个java.lang.ClassCastException： org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection出来。这下郁闷了，莫非从DBCP拿出来的Connection跟 OracleConnection不一样。晕了，别介呀，我对java不懂！又search了半天，发现了一个UnWrapper，本以为能把这个 Wrapper给干掉的，但搞了半天没搞明白。过了XX时间，不知道是在哪国网站上看到有人

ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor（“NUMBERTABLE”， （（DelegatingConnection）conn）.getDelegate（）））;

他们用着都好用，到我这（（DelegatingConnection）conn）.getDelegate（）出来的Connection是个null，很郁闷。后来又看到

public static Connection getNativeConnection（Connection con） throws SQLException {

if （con instanceof DelegatingConnection） {

Connection nativeCon = （（DelegatingConnection） con）.getInnermostDelegate（）;

// For some reason， the innermost delegate can be null： not for a

// Statement‘s Connection but for the Connection handle returned by the pool.

// We’ll fall back to the MetaData‘s Connection in this case， which is

// a native unwrapped Connection with Commons DBCP 1.1.

return （nativeCon ！= null ？ nativeCon ： con.getMetaData（）.getConnection（））;

}

return con;

}

可（（DelegatingConnection） con）.getInnermostDelegate（）;依然是null但con.getMetaData（）.getConnection（））;得到了一个OracleConnection，debug时看着eclipse variables的窗口心中一阵暗喜，该OK了吧！

哎，事实上最近一段时间总是事与愿违，执行-又异常了！郁闷，一次比一次郁闷，一次比一次怪异！

java.lang.ClassCastException： oracle.jdbc.driver.OracleConnection cannot be cast to oracle.jdbc.OracleConnection 由于字符串太长搜都搜不到，想了好久，尝试着各种各样的方法！终于有一个次把tomcat/lib目录classes12.jar删掉，没有异常，一切 OK！但后来把classes12.jar又仍进去了，还正常的，代码没有一点变化！很是郁闷，但既然问题没了，也就懒得看了！

最后的代码：

-----------------------------------------------------------------------------------

public static Connection getConnection（） {

Connection con = null;

try {

Context ic = new InitialContext（）;

DataSource ds = （DataSource） ic.lookup（“java:comp/env/jdbc/yoyo”）;

con = ds.getConnection（）;

} catch （Exception e） {

System.out.println（“**** error DataSource”）;

}

return con;

}

-----------------------------------------------------------------------------------

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import org.apache.tomcat.dbcp.dbcp.DelegatingConnection;

public class BussinessLog {

public static ArrayList CancelLog（String sLoginUser， Object［］ arrLogID）

{

ArrayList arrList = new ArrayList（）;

Connection conn = null;

CallableStatement callStmt = null;

String sql = null;

ArrayDescriptor arrDesc = null;

try

{

conn = DbConnectionManager.getConnection（）;

sql = “{call P_CanceltLog（？，？，？，？）}”;

callStmt = conn.prepareCall（sql）;

arrDesc = ArrayDescriptor.createDescriptor（“NUMBERTABLE”， getNativeConnection（conn））;

ARRAY arr = new ARRAY（arrDesc， getNativeConnection（conn）， arrLogID）;

callStmt.setString（1， sLoginUser）;

callStmt.setObject（2， arr， Types.ARRAY）;

callStmt.registerOutParameter（3， Types.VARCHAR）;

callStmt.registerOutParameter（4， Types.INTEGER）;

callStmt.execute（）;

arrList.add（callStmt.getInt（4））;

arrList.add（callStmt.getString（3））;

return arrList;

} catch （Exception e） {

System.out.println（e.toString（））;

} finally {

DbAction.clear（conn， callStmt）;

}

return arrList;

}

public static Connection getNativeConnection（Connection con） throws SQLException {

if （con instanceof DelegatingConnection） {

Connection nativeCon = （（DelegatingConnection） con）.getInnermostDelegate（）;

// For some reason， the innermost delegate can be null： not for a

// Statement’s Connection but for the Connection handle returned by the pool.

// We‘ll fall back to the MetaData’s Connection in this case， which is

// a native unwrapped Connection with Commons DBCP 1.1.

return （nativeCon ！= null ？ nativeCon ： con.getMetaData（）.getConnection（））;

}

return con;

}

}

-----------------------------------------------------------------------------------

A. 在这之前我还下载了最新的commons-dbcp-1.2.2.jar，但使用里面DelegatingConnection时，con instanceof DelegatingConnection是false，看来tomcat里出来的Connection就的配Tomcat\lib\tomcat- dbcp.jar里的DelegatingConnection，还真是什么枪打什么鸟。

B.偶尔发现 （（DelegatingConnection） con）.getInnermostDelegate（）;之所以返回null是因为tomcat里Context.Resource的 accessToUnderlyingConnectionAllowed参数默认为false所致，被设置成true之后是可以取到 OracleConnection的.