- PR -

配列をPL/SQLに渡してPL/SQL内でInsert処理を行う

投稿者投稿内容
satoko
常連さん
会議室デビュー日: 2006/05/06
投稿数: 35
お住まい・勤務地: 東京
投稿日時: 2007-05-08 11:42
いつも勉強させていただいています。
この業界に入ってまだ間もないので、色々とおかしい表現があるかと思いますが、
どうぞよろしくお願いします。
javaから配列をストアドに渡して、INSERT処理を行いたいのですが、
うまくいかないので投稿させていただきました。

[環境]
OS:WindowsServer2003
DB:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
APP:apache2.0.55
tomcat5.5
JDK1.5

ThinドライバでVARRAY型を使う方法を取りました。

---------------------------------
まずは、VARRAY型を宣言します。
CREATE TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(100);
/
CREATE TYPE NUMBER_ARRAY IS TABLE OF NUMBER;
/
---------------------------------

次にそれを受け取り、VARRAY型からPL/SQL表への格納を行い、それをバインド
してINSERTするストアドを作ります。

CREATE OR REPLACE FUNCTION TEST_BIND_INSERT
(
TEST_NO_ARRAY_IN IN NUMBER_ARRAY,
TEST_NAME_ARRAY_IN IN VARCHAR2_ARRAY
)

RETURN NUMBER
IS
I NUMBER := 0;
ARRAY_COUNT NUMBER := 0;
SET_TEST_NO_ARRAY DBMS_SQL.NUMBER_TABLE;
SET_TEST_NAME_ARRAY DBMS_SQL.VARCHAR2_TABLE;
SQL_TMP VARCHAR2(100);
CURSOR_HANDLE INTEGER;
EXECUTE_RET INTEGER;
RETURN_VALUE NUMBER := 0;
BEGIN
-- 引き渡された配列データ(VARRAY型)をループさせながら、PL/SQL表へと格納しなおす
-- (バインドが使えるのがPL/SQL表だけなので)
LOOP
I := I + 1;
EXIT WHEN NOT TEST_NO_ARRAY_IN.EXISTS(I); -- 配列データが存在しなければ、ループを抜ける
SET_TEST_NO_ARRAY(I) := TEST_NO_ARRAY_IN(I);
SET_TEST_NAME_ARRAY(I) := TEST_NAME_ARRAY_IN(I);
END LOOP;

-- バインドを使ったINSERTを行う
-- カーソルIDの取得
CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
-- SQLの作成
SQL_TMP := NULL;
SQL_TMP := 'INSERT INTO T_CST_TEST(ID, TYPE) ' || 'VALUES(:B_TEST_NO_ARRAY, :B_TEST_NAME_ARRAY) ';
-- 解析
DBMS_SQL.PARSE(CURSOR_HANDLE, SQL_TMP, DBMS_SQL.NATIVE);
-- バインド
DBMS_SQL.BIND_ARRAY(CURSOR_HANDLE, 'B_TEST_NO_ARRAY', SET_TEST_NO_ARRAY);
DBMS_SQL.BIND_ARRAY(CURSOR_HANDLE, 'B_TEST_NAME_ARRAY', SET_TEST_NAME_ARRAY);
-- INSERTの実行
BEGIN
EXECUTE_RET := DBMS_SQL.EXECUTE(CURSOR_HANDLE);
RETURN_VALUE := 1;
EXCEPTION
WHEN OTHERS THEN RETURN_VALUE := 400000 - SQLCODE;
END;
-- カーソルのクローズ
DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);

IF RETURN_VALUE = 1 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

RETURN RETURN_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN_VALUE := 400000 - SQLCODE;
ROLLBACK;
RETURN RETURN_VALUE;
END;
/

---------------------------------
このストアドを呼び出すJAVAのメソッド
---------------------------------
public class BizTest {
public void insertTestData(HttpServletRequest request, ManListForm frm, int[] AbsArray, String[] DataArray) throws FatalException, SQLException {
CallableStatement cstmt = null;
Connection con = null;
int ret = 1;
String sql = "";
try
{
con = DriverManager.getConnection("jdbc:oracle:thin:@svrName:1521:orcl","userID","passwd");
// Oracleの配列(コレクション型(VARRAY型))への定義変数作成
ArrayDescriptor strDesc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", con);
ArrayDescriptor intDesc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", con);
// Oracle配列の作成
ARRAY arrAbs = new ARRAY(intDesc, con, AbsArray);
ARRAY arrData = new ARRAY(strDesc, con, DataArray);
// SQL文(ストアドファンクション実行文)の作成
sql = "BEGIN ? := TEST_BIND_INSERT(?, ?); END;";
// ステートメントを作成
cstmt = con.prepareCall(sql);
// パラメータの設定
cstmt.registerOutParameter(1, Types.VARCHAR);
((OracleCallableStatement)cstmt).setARRAY(2, arrAbs);
((OracleCallableStatement)cstmt).setARRAY(3, arrData);

// ストアードファンクション実行
cstmt.execute();

// 結果の取得
ret = cstmt.getInt(1);
cstmt.close();

// エラー発生の場合
if(ret != 1 )
{
// ロールバック
con.rollback();
log.fatal("SQLエラー:ret=" + ret);
throw new FatalException("error.sql");
}
else
// コミット
con.commit();
}
// SQLエラー
catch (SQLException e)
{
log.fatal("SQL="+ sql);
con.rollback();
throw e;
}
finally
{
con.close();
}
}
}

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

上記のソースで実行すると、
テーブルT_CST_TESTのカラムIDにはint[] AbsArrayの値がINSERTされるのですが、
カラムTYPEにはString[] DataArrayの値がINSERTされません。
間違えている箇所を指摘していただけると助かります。

それと上記のストアドを単体でデバッグをしたいのですが、
パラメータのOracle配列をどのように記述して渡せばよいのか分かりません。

だらだらと長くなってしまいましたが、よろしくお願いします。
zilloll
常連さん
会議室デビュー日: 2006/02/01
投稿数: 24
投稿日時: 2007-05-09 20:40
同じ環境じゃないので恐縮ですが、
9i環境で、クライアントマシンの上でのコマンドラインからのJava実行(JDK1.3)では、
問題なく動きますね。
コードは間違っていないように思います。
引数で渡されているStringの配列にはデータが入っているでしょうか。

因みにPL/SQL部分は、まんまコピッて登録して、
Javaの実行コードは以下のようにデータ配列をメソッド内で作っている以外は
まんまコピーです。
------------------------------------------------------
int[] AbsArray = {0,1,2};
String[] DataArray = {"一件目","二件目","三件目"};
ArrayDescriptor strDesc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", con);
ArrayDescriptor intDesc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", con);
// Oracle配列の作成
ARRAY arrAbs = new ARRAY(intDesc, con, AbsArray);
ARRAY arrData = new ARRAY(strDesc, con, DataArray);
// SQL文(ストアドファンクション実行文)の作成
String sql = "BEGIN ? := TEST_BIND_INSERT(?, ?); END;";
// ステートメントを作成
cstmt = con.prepareCall(sql);
// パラメータの設定
cstmt.registerOutParameter(1, Types.VARCHAR);
((OracleCallableStatement)cstmt).setARRAY(2, arrAbs);
((OracleCallableStatement)cstmt).setARRAY(3, arrData);

cstmt.execute() ;
-------------------------------------------------------

メソッド内で初期化した配列を渡しても、入らないですか?
※とりあえず確実にデータのある配列を渡した場合の確認
原因がJDBCドライバの問題とかだとおてあげですが・・・

PL/SQLの確認は、以下の内容のスクリプトファイルをSQL*Plusで実行してみる
のはどうでしょうか。

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

set serveroutput on

declare
num_table NUMBER_ARRAY;
v2_table VARCHAR2_ARRAY;

result NUMBER;

CURSOR cur IS
SELECT ID, TYPE
FROM T_CST_TEST;
begin
num_table := NUMBER_ARRAY(0,1,2);
v2_table := VARCHAR2_ARRAY('一件目', '二件目', '三件目');

result := TEST_BIND_INSERT(num_table, v2_table);

FOR rCur IN cur
LOOP
dbms_output.put_line('[ID]=[' || rCur.ID || '][TYPE]=[' || rCur.TYPE || ']');
END LOOP;

commit work;
exception
when others then
dbms_output.put_line(sqlerrm);
rollback work;
end;
/

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

あと、ちょっと気になったのですが、呼び出し側でトランザクションの
制御をするのであれば、PL/SQLのFunctionでは、
commit/rollbackしないようがよいように思います。



[ メッセージ編集済み 編集者: zilloll 編集日時 2007-05-09 20:46 ]
satoko
常連さん
会議室デビュー日: 2006/05/06
投稿数: 35
お住まい・勤務地: 東京
投稿日時: 2007-05-09 21:21
zillollさん返信ありがとうございます^^。

引用:

引数で渡されているStringの配列にはデータが入っているでしょうか。



これは値は入っています。

引用:

メソッド内で初期化した配列を渡しても、入らないですか?
※とりあえず確実にデータのある配列を渡した場合の確認



引数で渡してある配列でも、メソッド内で初期化した配列でも、入りませんでした。
もうちょっと調べてみたところ、

// ステートメントを作成
cstmt = con.prepareCall(sql);

の所のcstmt= T4CCallableStatement (ID=84)の中で、
errMsgMixedBind= "Ordinal binding and Named binding cannot be combined!"
と出ていました。

// Oracleの配列(コレクション型(VARRAY型))への定義変数作成
ArrayDescriptor strDesc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", con);

と、

// Oracle配列の作成
ARRAY arrData = new ARRAY(strDesc, con, DataArray);

の所で、値が入っていないみたいです。
ストアドに値が渡されていないのは確認しました。

引用:

PL/SQLの確認は、以下の内容のスクリプトファイルをSQL*Plusで実行してみる
のはどうでしょうか。



実行してみたところ、正常にどちらの値もINSERTされました。
やはり、ストアド側に値がうまく渡っていないようです。

JDBCのドライバはthinドライバの最新を入れました。

引用:

あと、ちょっと気になったのですが、呼び出し側でトランザクションの
制御をするのであれば、PL/SQLのFunctionでは、
commit/rollbackしないようがよいように思います。



これはミスでした。直しておきます。
satoko
常連さん
会議室デビュー日: 2006/05/06
投稿数: 35
お住まい・勤務地: 東京
投稿日時: 2007-05-09 21:56
ARRAY arrData = new ARRAY(strDesc, con, DataArray);
でarrDataをnewしているところで中を見たら、

charSet= CharacterSetUnknown (ID=1184)
oracleId= 832
rep= 1856

と出ていました。

charSet= CharacterSetUnknown
というのは、必要なCharacterSetが無いと言う事なのでしょうか??
zilloll
常連さん
会議室デビュー日: 2006/02/01
投稿数: 24
投稿日時: 2007-05-09 23:53
nls_charset12.zipにクラスパスは通っているでしょうか。
通っていないようでしたら、
DBサーバの「{ORACLE_HOME}/jdbc/lib」に配置されていますので、
アプリケーションのライブラリに追加してみてください。
また、JDBCドライバは、上記場所の「classes12.zip」を
使用するのがよいと思います。
satoko
常連さん
会議室デビュー日: 2006/05/06
投稿数: 35
お住まい・勤務地: 東京
投稿日時: 2007-05-10 03:51
引用:

nls_charset12.zipにクラスパスは通っているでしょうか。
通っていないようでしたら、
DBサーバの「{ORACLE_HOME}/jdbc/lib」に配置されていますので、
アプリケーションのライブラリに追加してみてください。
また、JDBCドライバは、上記場所の「classes12.zip」を
使用するのがよいと思います。



nls_charset12.jarと
classes12.jarを
ビルド・パスのライブラリに追加した所、
JAVAからORACLE配列ARRAY arrDataをPL/SQLに渡してINSERTが出来ました^^
ありがとうございます。

しかし、
"111"や"aaa"なら平気なのですが、"あああ"等の日本語をDataArrayに入れると、
UnicodeをOracleキャラクタにマップできません。
と怒られてしまいました。

エラー内容を調べたところ、
「プログラムが、Oracleキャラクタ・セットの文字にマップできないUnicode文字を使用しようとしました。」
とありました。
解決策は、
「無効な文字に対する個別の例外ハンドラを記述するか、またはwithReplacementメソッドをコールして無効な文字を有効な置換文字に置き換えてください」
とありましたが、参考になるHPが見つかりませんでした。。。

一応今のJAVAのソースを手短に置いてみます。

// さきほどの、charSet= CharacterSetUnknownを回避する為にCharacterSetを作りました。
int oracleId = CharacterSet.ASCII_CHARSET;
CharacterSet charset = CharacterSet.make(oracleId);

oracle.sql.CHAR[] DataArray = new CHAR[3];
DataArray[0] = new CHAR("aaa", charset); --これはOK
DataArray[1] = new CHAR("111", charset); --これもOK
DataArray[2] = new CHAR("あああ", charset); ---ここでエラー

try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection(oraurl,orauser,orapass);

// Oracleの配列(コレクション型(VARRAY型))への定義変数作成
ArrayDescriptor strDesc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", con);
ArrayDescriptor intDesc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", con);

// Oracle配列の作成
ARRAY arrAbs = new ARRAY(intDesc, con, AbsArray);
ARRAY arrRel = new ARRAY(intDesc, con, RelArray);
ARRAY arrFlg = new ARRAY(intDesc, con, FlgArray);
ARRAY arrData = new ARRAY(strDesc, con, DataArray);

// SQL文(ストアドファンクション実行文)の作成
sql = "BEGIN ? := TEST(?, ?, ?, ?); END;";

// ステートメントを作成
cstmt = con.prepareCall(sql);

// パラメータの設定
cstmt.registerOutParameter(1, Types.VARCHAR);
((OracleCallableStatement)cstmt).setARRAY(2, arrAbs);
((OracleCallableStatement)cstmt).setARRAY(3, arrRel);
((OracleCallableStatement)cstmt).setARRAY(4, arrFlg);
((OracleCallableStatement)cstmt).setARRAY(5, arrData);

// ストアードファンクション実行
cstmt.execute();




[ メッセージ編集済み 編集者: satoko 編集日時 2007-05-10 03:54 ]
zilloll
常連さん
会議室デビュー日: 2006/02/01
投稿数: 24
投稿日時: 2007-05-10 11:07
「あああ」はASCII_CHARSETではないです。
oracle.sql.CHARは使用したことが無いので、よくわかりませんが、
この場合、DBの文字コードによって、JA16EUC_CHARSETまたは
JA16SJIS_CHARSETを指定するのかなーと予想します。
※ライブラリを追加したことだし元のString配列を使うのが手っ取り早い気もしますが・・・

[ メッセージ編集済み 編集者: zilloll 編集日時 2007-05-10 11:13 ]
satoko
常連さん
会議室デビュー日: 2006/05/06
投稿数: 35
お住まい・勤務地: 東京
投稿日時: 2007-05-10 17:43
zillollさん、何度もありがとうございます。とても助かります。

引用:

この場合、DBの文字コードによって、JA16EUC_CHARSETまたは
JA16SJIS_CHARSETを指定するのかなーと予想します。



DBの文字コードはSJISだったので、JA16SJIS_CHARSETを指定したのですが、
DataArray[0] = new CHAR("aaa", charset);
ここで、同じエラーが出てしまっていました。

結局、
UTF8_CHARSETで正常に文字化けせずに動作しました。
ありがとうございます^^

そして、今
con = DriverManager.getConnection(oraurl,orauser,orapass);
と、直接DBを呼び出しているのですが、
普段コネクションプールを使用していて、
このメソッドだけ割り込んで繋げるわけにもいかないので、
コネクションプールで使えるようにしたいのです。

commons-dbcp-1.2.1.jar
commons-pool-1.2.jar
を使用していますが、正直コネクションプールは理解出来ていないので、
今調べているところです。

今回のORACLE配列を使用するにあたって、どのようにコネクションプールを使用すればよいのか、参考URLでもなんでも構わないので教えてください。

スキルアップ/キャリアアップ(JOB@IT)