QA@IT
この質問・回答は、@ITの旧掲示板からインポートされたものです。

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

いつも勉強させていただいています。
この業界に入ってまだ間もないので、色々とおかしい表現があるかと思いますが、
どうぞよろしくお願いします。
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配列をどのように記述して渡せばよいのか分かりません。

だらだらと長くなってしまいましたが、よろしくお願いします。

質問者:satoko

回答

同じ環境じゃないので恐縮ですが、
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 ]

投稿者:zilloll

編集 履歴 (0)

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

編集 履歴 (0)

ARRAY arrData = new ARRAY(strDesc, con, DataArray);
でarrDataをnewしているところで中を見たら、

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

と出ていました。

charSet= CharacterSetUnknown

というのは、必要なCharacterSetが無いと言う事なのでしょうか??

投稿者:satoko

編集 履歴 (0)

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

投稿者:zilloll

編集 履歴 (0)

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 ]

投稿者:satoko

編集 履歴 (0)

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

投稿者:zilloll

編集 履歴 (0)

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でもなんでも構わないので教えてください。

投稿者:satoko

編集 履歴 (0)

こんにちは

普段コネクションプールを使用していて、
このメソッドだけ割り込んで繋げるわけにもいかないので、

ということは、すでにシステム内にコネクションプールを使用する
仕組みが動いているいうことですよね。
※メソッド名とかから単体のテストサンプルかと思ってました
DB接続管理するクラスがすでにあるのであれば、それを利用すれば
よいと思います。
共通化されていない場合は、その部分と同じようにコネクションの
取得を行うようコーディングすればよいのではないでしょうか。
配列を渡すかどうかによってプーリングの利用方法が変わることは
ないと思います。

プーリングの場合、コネクションのクローズで実際にすぐに接続が切れないので、
statementやresultsetを使い終わったら確実にクローズするように、
より注意する必要はあると思いますが。

投稿者:zilloll

編集 履歴 (0)

zillollさんこんにちは〜

ありがとうございます、とりあえずですが出来ました。
どうもありがとうございました^^

投稿者:satoko

編集 履歴 (0)
ウォッチ

この質問への回答やコメントをメールでお知らせします。