5 Replies Latest reply: Nov 3, 2011 6:05 PM by Takashi Matsuoka RSS

    ORA-06502 文字列バッファが小さすぎます。

    894733
      現在、JAVAからspringframeworkを用いてOracleストアドファンクションを呼び出す処理を作成しておりますが、
      ファンクションの引数の桁数問題にて、「ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました」が発生しています。
      対応策が分からず、悪戦苦闘しており、解決方法を知っていらっしゃる人がいましたらご教示ください。

      該当のFUNCTION定義(抜粋)
      -------------------------------------
      FUNCTION FUNCH_CRE_TEST(
           i_name          IN VARCHAR2,          -- 名
           o_tbl_name     OUT NOCOPY VARCHAR2,     -- テーブル名
           o_errmsg     OUT NOCOPY VARCHAR2)     -- エラーメッセージ
           RETURN          NUMBER               -- エラーコード
      IS
           retcode          NUMBER := 0;
           table_id     VARCHAR2(10);
           ora_msg          VARCHAR2(1000);
      BEGIN
           o_tbl_name := '';
           SELECT LPAD(SEQ_TEST.NEXTVAL,10,'0') INTO table_id FROM dual;
           o_tbl_name := UPPER(i_name) || '_' || table_id;
           RETURN retcode;
      EXCEPTION
           WHEN OTHERS
           THEN
                retcode := SQLCODE;
                o_errmsg := SQLERRM;
                RETURN retcode;
      END FUNCH_CRE_TEST;

      END TEST;
      -------------------------------------

      「i_name」を元に、シーケンスで取得した値を付けた値を戻す処理なのですが、
      「o_tbl_name := UPPER(i_name) || '_' || table_id;」の行にて、
      「ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました」が発生します。
      引数を変えて桁数を確認したところ、o_tbl_nameには、2ケタの値までしか入れられず、3ケタ以上の値は同様のエラーとなりました。
      o_tbl_nameの桁数の定義はファンクション側ではしていないのですが、
      FUNCTION側?またはOracle側?で桁数定義などをする方法はあるのでしょうか?
        • 1. Re: ORA-06502 文字列バッファが小さすぎます。
          896440
          o_tbl_nameにNOCOPY を付けた場合、参照渡しになるらしいので、
          呼出側の型宣言に縛られてしまうのではないでしょうか。
          きつく縛る必要がないなら、取りあえず外して、
          呼出側で戻り後にチェックしてはどうでしょう?
          • 2. Re: ORA-06502 文字列バッファが小さすぎます。
            894733
            返信、ありがとうございます。

            ご指摘頂いた「NOCOPY」についてですが、
            取り払った状態で実行してみましたが、エラー内容は変わりませんでした。

            また、「呼出側の型宣言」とありますが、
            呼出側(今回の場合は、JAVA?)で桁数宣言等をする必要があるのでしょうか?
            FUNCTIONを直接呼び出しているため、呼出側については意識しておりませんでした。
            ちなみに、Spring側の設定ファイル(xml)を確認した限りでは、同様に桁数指定などはしておりません。

            以下xml抜粋
            ----------------------
            <property name="sqlParameters">
                 <list>
                      <value>OUT,ret_code,NUMBER</value>
                      <value>IN,i_name,VARCHAR</value> <!-- 名 -->
                      <value>OUT,o_tbl_name,VARCHAR</value> <!-- テーブル名 -->
                      <value>OUT,o_errmsg,VARCHAR</value> <!-- エラーメッセージ -->
                 </list>
            </property>
            ----------------------
            • 3. Re: ORA-06502 文字列バッファが小さすぎます。
              Hiroyuki Nakaie-Oracle
              user12303560さん、こんにちは。

              ご提示のファンクションを動かしてみましたが、当方の環境では3桁以上の入力でも問題なく動きました。
              実際にはこのファンクション、パッケージの一部のようですので、このファンクションの呼び出し元を含め、
              SQL Developerみたいなデバッグ機能のある環境を使って地道に調査されるのがいいかと思います。
              SQL> var aaa varchar2(50)
              SQL> var bbb varchar2(50)
              SQL> var ccc number;
              SQL> exec :ccc := funch_cre_test('AB', :aaa, :bbb)
              
              PL/SQLプロシージャが正常に完了しました。
              
              SQL> print aaa
              
              AAA
              --------------------------------------------------------------------------------
              
              AB_0000000001
              
              SQL> exec :ccc := funch_cre_test('ABC', :aaa, :bbb)
              
              PL/SQLプロシージャが正常に完了しました。
              
              SQL> print aaa
              
              AAA
              --------------------------------------------------------------------------------
              
              ABC_0000000002
              
              SQL> exec :ccc := funch_cre_test('ABCDEFG', :aaa, :bbb)
              
              PL/SQLプロシージャが正常に完了しました。
              
              SQL> print aaa
              
              AAA
              --------------------------------------------------------------------------------
              
              ABCDEFG_0000000003
              余談ですが、ファンクションとして作成されるのであれば、生成された文字列を戻り値とし、
              エラーは呼び出し元のEXCEPTIONでCATCHするというのがスタンダードな作り方です。
              業務仕様もあるでしょうから、絶対こうでなければならない、ということはないですけど。

              あといくつか。ご存知でしたら申し訳ないです。
              ・OUT引数の利用をやめればSELECT文でファンクションを呼び出せるようになります。
              ・11gをお使いでしたら、SELECT文を使わなくても直接順序にアクセスできるようになっています。
              SQL> create or replace FUNCTION FUNCH_CRE_TEST(
                2    i_name IN VARCHAR2) -- 名
                3    RETURN VARCHAR2 -- エラーコード
                4  IS
                5  BEGIN
                6    RETURN UPPER(i_name) || '_' || LPAD(SEQ_TEST.NEXTVAL,10,'0');
                7  END FUNCH_CRE_TEST;
                8  /
              
              ファンクションが作成されました。
              
              SQL> select funch_cre_test('ORACLE') from dual;
              
              FUNCH_CRE_TEST('ORACLE')
              ---------------------------------------------------------------------------
              
              ORACLE_0000000004
              • 4. Re: ORA-06502 文字列バッファが小さすぎます。
                Blueloco
                Javaは詳しくありませんが、OUTの変数に直接値を代入している文でエラーになっていますので、呼び出し元のプログラム側に原因がある可能性が高いのではないでしょうか。
                Java側で

                FUNCH_CRE_TEST(引数1, 引数2, 引数3)

                のような形で呼び出しているはずですので、引数2に指定している部分が問題と考えられます。
                ※恐らく引数3も同様なのでは…
                • 5. Re: ORA-06502 文字列バッファが小さすぎます。
                  Takashi Matsuoka
                  Javaではありませんが...
                  .NETで、同様の事象になったことがあります。

                  文字列のOUTやRETVALは、呼び出し側でメモリを確保しておく必要がありました。
                  dbParameter.Size = 32767;