QA@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

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