3 Replies Latest reply: Jun 26, 2011 9:46 PM by 870553 RSS

    バインド変数を使用したINSERTでORA-00001(一意制約違反)

    870553
      タイトル修正しました。元タイトル:プログラムからのINSERTでORA-00001(一意制約違反)
      ---------------------------------------------

      お世話になります。
      簡単なプログラムを作っていて、おかしい事象が出ており、識者の方にお聞きしたく思います。

      環境:
       プログラム側 VB.NET(2010)+ODP.NET(DB付属のものを参照設定して利用。)

      DB側:
       Oracle 11g R2 Standard Edition One on Windows Server 2008 R2

      テーブル: Test
       memo_id as varchar2(10),
      memo_no as number,
      memo_text as varchar2(2000),
      primary key (memo_id, memo_no)


      プログラム側で発行しているSQLは、memo_idごとにmemo_noを発行し、テキストを登録する、という内容です。

      INSERT INTO Test ( memo_id, memo_no, memo_text ) VALUES (
      :memoId
      ,COALESCE((SELECT MAX(memo_no) FROM TEST WHERE memo_id=:memoId), 0) + 1
      ,:memoText
      )

      このSQLで以下のようにデータ登録されるものと思っていました。(memo_id ごとに、枝番を発番するイメージ)

      1件目の時:
       パラメータ --> memoId='id-00001', memoText='メモ内容:00001-1'
       期待したデータ --> ('id-00001', 1, 'メモ内容:00001-1')

      2件目の時:
       パラメータ --> memoId='id-00001', memoText='メモ内容:00001-2'
       期待したデータ -->  ('id-00001', 2, 'メモ内容:00001-2')

      3件目の時:
       パラメータ --> memoId='id-00002', memoText='メモ内容:00002-1'
       期待したデータ -->  ('id-00002', 1, 'メモ内容:00002-1')

      4件目の時:
       パラメータ --> memoId='id-00001', memoText='メモ内容:00001-3'
       期待したデータ -->  ('id-00001', 3, 'メモ内容:00001-3)

      5件目の時:
       パラメータ --> memoId='id-00002', memoText='メモ内容:00002-2'
       期待したデータ -->  ('id-00002', 2, 'メモ内容:00002-2')


      他の環境、他のプログラムではこういうSQLを多用していたので、今回も問題ないだろうと思っていたのですが、
      うまくいかずにORA-00001(一意制約違反)のエラーとなってしまいました。


      テーブルの主キーを外して試してみると、実際に登録された内容は下のようになりました。

      1件目の時:
       パラメータ --> memoId='id-00001', memoText='メモ内容:00001-1'
       実際のデータ --> ('id-00001', 1, 'メモ内容:00001-1')

      2件目の時:
       パラメータ --> memoId='id-00001', memoText='メモ内容:00001-2'
       実際のデータ -->  ('id-00001', 1, 'メモ内容:00001-2')

      3件目の時:
       パラメータ --> memoId='id-00002', memoText='メモ内容:00002-1'
       実際のデータ -->  ('id-00002', 1, 'メモ内容:00002-1')

      4件目の時:
       パラメータ --> memoId='id-00001', memoText='メモ内容:00001-3'
       実際のデータ -->  ('id-00001', 1, 'メモ内容:00001-3)

      5件目の時:
       パラメータ --> memoId='id-00002', memoText='メモ内容:00002-2'
       実際のデータ -->  ('id-00002', 1, 'メモ内容:00002-2')


      SQLを下のようにいじって、memo_idごとに発番している部分を固定化すると1,2,3,4,5 と枝番がカウントアップしていくのですが、
      それだとシーケンスを使っているのと同じですし・・・

      INSERT INTO Test ( memo_id, memo_no, memo_text ) VALUES (
      :memoId
      ,COALESCE((SELECT MAX(memo_no) FROM TEST WHERE memo_id='id-00001'), 0) + 1
      ,:memoText
      )


      ちなみに、SQL*Plus で各パラメータを固定して実行すると、意図通りに期待したデータが入ります。

      何とか評価順を変えて、意図通りのデータが入れられるようにしたいのですが、SQLを少しいじってみてもうまくいきませんでした。

      なんとか評価順を変えて、枝番を発番しながらINSERTできる方法はありますでしょうか?


      以上です。よろしくお願いします。

      Edited by: user8848963 on 2011/06/23 1:50
        • 1. Re: プログラムからのINSERTでORA-00001(一意制約違反)
          weyk
           SQL*Plusで試した内容の「パラメータを固定して」というのが、Host変数を用いずにということであるとすると、「memo_idごとに発番している部分を固定化すると」の変更も含んでしまってるということでしょうか。もし、そうであるばあい、SQL*PlusからもHost変数を用いて、同じ形(SQLは同一でHost変数の内容だけかえて順次発行)で実行するとどうなりますか?
          (おそらくは、SQL*Plusではうまくゆくと思うのですが、念のため)

           また、いままでうまくいっていた環境にたいして、なにか新しい要素はあるのでしょうか?例えば、開発言語が違うとかミドルウェアが違うとかパラレル実行しているとかトランザクションを用いていない など。プログラムに依存しそうな雰囲気があるのですが、プログラムが一切かかれていないため、(第3者は)判断のしようがなくなっています。

           「評価順を変えて」というのは、ちょっと意味が分かりませんでした。各INSERT文を単一のトランザクションから順に実行している限りは(評価順とか関係なく)うまく行くはず・・・なんですよね?


          蛇足。
           ざっと、マニュアルを見ていた中で、トランザクションをREAD ONLYにしていると再現しそうな気がしました。もし、SQL*PlusでHost変数を用いた場合には期待通り動いた場合は、次にSQL*PlusとHost変数をもちたのと同じ手順を、SET TRANSACTION READ ONLYにして再現するか試してみてはいかがでしょうか。
          -- CMN v0.61β --
          • 2. Re: プログラムからのINSERTでORA-00001(一意制約違反)
            870553
            weyk さん、回答ありがとうございます。

            「パラメータを固定して」というのは、ご指摘の通り変数を用いずに、ということです。

            SQL*Plus において、Host変数を利用し、プログラムと同じSQLを実行した場合、プログラムと同じ結果(ORA-00001)になります。

            SQL*Plusで実行した内容:

             SQL> var memoId varchar2(10);
             SQL> var memoText varchar2(30);
             SQL> begin
             2 :memoId := 'id-00001';
             3 :memoText := 'メモ内容:00001-2'
             4 end;
             5 /
             SQL> INSERT INTO Test ( memo_id, memo_no, memo_text ) VALUES (
             SQL> :memoId
             SQL> ,COALESCE((SELECT MAX(memo_no) FROM TEST WHERE memo_id=:memoId), 0) + 1
             SQL> ,:memoText
             SQL> );

            (メッセージをコピーし忘れました・・・ORA-00001 のメッセージが出てました。)


            うまくいっていた環境、というのは、今手元にないのですが、10gまでは上のSQLで期待通りの結果だったような・・・
            (すみません、Oracle一筋じゃないので、記憶があいまいです。)


            今回の環境では、質問のSQLが期待通りに”動かなくなった”、のではなく、初めから期待通りに動いていません。

            評価順、と言ったのは私の想像で、INSERT文のコンパイル時に COALESCE句の部分が先に ”1” となってしまって、
            実行時にはCOALESCE句の評価が終わっていると考えましたので、COALESCE句を実行時に(遅延)評価するように
            させたい、といったニュアンスで使っていました。


            また、SQL*Plus上で、今単純なSELECTを試したところ、これもまた???な状況でした。

            試した内容は以下の通りです。

            前述のTest表にデータを登録:
             SQL> INSERT INTO TEST (memo_id, memo_no, memo_text) VALUES ('test', 1, 'test-1');
             SQL> INSERT INTO TEST (memo_id, memo_no, memo_text) VALUES ('test', 2, 'test-1');
             SQL> INSERT INTO TEST (memo_id, memo_no, memo_text) VALUES ('test', 3, 'test-1');

            普通にSELECT:
             SQL> SELECT MAX(memo_no) FROM TEST WHERE memo_id='test';

            MAX(MEMO_ID)
            ----------------------
            3

            バインド変数を利用して実行:

             SQL> VAR memoId VARCHAR2(10);
             2 BEGIN
             3 :memoId := 'test';
             4 END;
             5 /

            PL/SQLプロシージャが正常に完了しました。

             SQL> SELECT MAX(memo_no) FROM TEST WHERE memo_id=:memoId;


            MAX(MEMO_ID)
            ----------------------


            プログラムでも同じことを試してみましたが、結果(NULL)は同じでした。うむむ・・・

            Edited by: user8848963 on 2011/06/22 22:52
            • 3. Re: プログラムからのINSERTでORA-00001(一意制約違反)
              870553
              ご報告。

              とりあえず、新しいデータベースを作成、Test表を用意し、同じことを試行したところ、期待通りの結果となりました。

              元のデータベースは他人が用意していたものだったので、どこかの設定で件の現象が起きていたのかもしれません。

              時間が取れれば何が異なっているのか調べてみたいと思います。