ORACLE存儲過程的數(shù)組參數(shù)
http://www.examw.com/biancheng/oracle/158294/
環(huán)境：Eclipse+Oracle9.0.2+Tomcat5.5
功能：采用存儲過程、type組合來實現(xiàn)批量入庫，以節(jié)省系統(tǒng)開銷，提高效率。
*
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（）;
}
}
}
備注：如果在入庫的過程中發(fā)現(xiàn)字符串的值沒有入進去，請檢查有沒有加載該類庫nls_charset12.jar
* 該文章是參考“風(fēng)雪”文章后個人試驗的存檔。
*
* 其他參考：（引用2008-5-24 04:49 Aowken）
* Tomcat+Oracle調(diào)用存儲過程郁悶之旅
今天在改公司管理系統(tǒng)的時候，因為某個功能需要使用數(shù)組類型作為IN參數(shù)調(diào)用存儲過程，看了看文檔發(fā)現(xiàn)有Varray、nested table，但Varray有個最多數(shù)量的限制，也只好用nested table了，于是引發(fā)一連串的問題。
環(huán)境：
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這種據(jù)說很先進的東西就懵懵懂懂，索性就真的以為他非常牛X。使用幾維數(shù)組作為參數(shù)調(diào)用存儲過程還不是跟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他不會自己轉(zhuǎn)，非得人工干預(yù)。但我突然發(fā)現(xiàn)自己很愚蠢，我都沒告訴他procedure參數(shù)的類型，即使可以轉(zhuǎn)過去又有個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）;
執(zhí)行一下，結(jié)果異常。我靠數(shù)據(jù)庫里能用dbms_sql.Varchar2_Table聲明，他居然 java.sql.SQLException： 無效的名稱模式： DBMS_SQL.VARCHAR2_TABLE。我心想是不是得寫成SYS.dbms_sql.Varchar2_Table，結(jié)果還是一樣。再百度，人們說這樣不行，原因。。.不知道，但必須得自己定義類型才可以。于是我不得不
create type numbertable as table of number;
ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor（“numbertable”， conn）;
ARRAY arr = new ARRAY（arrDesc， conn， arrInt）;
結(jié)果又來了個java.sql.SQLException： 無效的名稱模式： baby.numbertable。我無語還得百度！@#￥%.。。.N久無果！但我發(fā)別人的碼的代碼里這個類型都是大寫，于是我也寫成 NUMBERTABLE。哈哈，果然不出那個異常了。但他NND又蹦個java.lang.ClassCastException： org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection出來。這下郁悶了，莫非從DBCP拿出來的Connection跟 OracleConnection不一樣。暈了，別介呀，我對java不懂！又search了半天，發(fā)現(xiàn)了一個UnWrapper，本以為能把這個 Wrapper給干掉的，但搞了半天沒搞明白。過了XX時間，不知道是在哪國網(wǎng)站上看到有人
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了吧！
哎，事實上最近一段時間總是事與愿違，執(zhí)行-又異常了！郁悶，一次比一次郁悶，一次比一次怪異！
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.偶爾發(fā)現(xiàn) （（DelegatingConnection） con）.getInnermostDelegate（）;之所以返回null是因為tomcat里Context.Resource的 accessToUnderlyingConnectionAllowed參數(shù)默認為false所致，被設(shè)置成true之后是可以取到 OracleConnection的.
聯(lián)系客服
