- - PR -
配列をPL/SQLに渡してPL/SQL内でInsert処理を行う
| 投稿者 | 投稿内容 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
投稿日時: 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(); } // ＳＱＬエラー 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配列をどのように記述して渡せばよいのか分かりません。 だらだらと長くなってしまいましたが、よろしくお願いします。 | ||||||||||||||||
|
投稿日時: 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 ] | ||||||||||||||||
|
投稿日時: 2007-05-09 21:21
zillollさん返信ありがとうございます＾＾。
これは値は入っています。
引数で渡してある配列でも、メソッド内で初期化した配列でも、入りませんでした。 もうちょっと調べてみたところ、 // ステートメントを作成 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); の所で、値が入っていないみたいです。 ストアドに値が渡されていないのは確認しました。
実行してみたところ、正常にどちらの値もINSERTされました。 やはり、ストアド側に値がうまく渡っていないようです。 JDBCのドライバはthinドライバの最新を入れました。
これはミスでした。直しておきます。 | ||||||||||||||||
|
投稿日時: 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が無いと言う事なのでしょうか？？ | ||||||||||||||||
|
投稿日時: 2007-05-09 23:53
nls_charset12.zipにクラスパスは通っているでしょうか。
通っていないようでしたら、 DBサーバの「{ORACLE_HOME}/jdbc/lib」に配置されていますので、 アプリケーションのライブラリに追加してみてください。 また、JDBCドライバは、上記場所の「classes12.zip」を 使用するのがよいと思います。 | ||||||||||||||||
|
投稿日時: 2007-05-10 03:51
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 ] | ||||||||||||||||
|
投稿日時: 2007-05-10 11:07
「あああ」はASCII_CHARSETではないです。
oracle.sql.CHARは使用したことが無いので、よくわかりませんが、 この場合、DBの文字コードによって、JA16EUC_CHARSETまたは JA16SJIS_CHARSETを指定するのかなーと予想します。 ※ライブラリを追加したことだし元のString配列を使うのが手っ取り早い気もしますが・・・ [ メッセージ編集済み 編集者: zilloll 編集日時 2007-05-10 11:13 ] | ||||||||||||||||
|
投稿日時: 2007-05-10 17:43
zillollさん、何度もありがとうございます。とても助かります。
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）
Copyright © ITmedia, Inc. All Rights Reserved.
著作権はアイティメディア株式会社またはその記事の筆者に属します。
当サイトに掲載されている記事や画像などの無断転載を禁止します。
著作権はアイティメディア株式会社またはその記事の筆者に属します。
当サイトに掲載されている記事や画像などの無断転載を禁止します。