3 Replies Latest reply: Mar 12, 2013 7:37 AM by keiji.k RSS

    UTL_FILE.FOPENの最大読み取りファイルサイズについて

    keiji.k
      いつもお世話になっております。

      現在、外部ファイルを読み込んで、テーブルにInsertする処理を考えております。
      その外部ファイルは、1行100バイト、行数にして50万行と大き目なファイルになっています。

      このファイルを以下の形式でInsertしようと思っています。

      vOPEN:= UTL_FILE.FOPEN(”C:\Hoge” ,"Hoge.csv",'r', 32767);
      LOOP
        UTL_FILE.GET_LINE(vOpen, vInput,32767);
        -- ここでインサート処理 (中略)
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- 最終行まで読み込んだ
        NULL;
      END;

      しかし、エラーにて帰ってきます。
      ----------------------------------------------------------------------------------
      ORA-06502: PL/SQL: 数値または値のエラー:
      文字列バッファが小さすぎます。が発生しました。
      ----------------------------------------------------------------------------------
      これは読み込もうとしているファイルサイズが大きすぎると思われますが、
      PLSQLで実装するためには、読み込みファイルを分割などするしか方法はないのでしょうか?
      また、サイズの大きいファイルをInsertする良い方法をご存知の方がいましたらご教示をお願い致します。

      ※調べてみましたが、32767は「1行当たりのサイズ上限」と書いているブログもありました。
      (真相は1行当たりのサイズではなく、1ファイルのサイズであっているのでしょうか・・・?)
      ttp://ameblo.jp/archive-redo-blog/entry-10034793463.html

      どうかよろしくお願い致します。
        • 1. Re: UTL_FILE.FOPENの最大読み取りファイルサイズについて
          hamadeguchi
          OS/DBバージョンは記載したほうがよいと思われます。バッファサイズなど異なることもありますし
          UTL_FILEで指定するサイズは行(改行コード0x0A)です。
          どの行でエラーが発生していますか?
          vInputのサイズが小さいということはありませんか?
          SQL*Loaderの使用は検討できませんか?
          • 2. Re: UTL_FILE.FOPENの最大読み取りファイルサイズについて
            asahide
            これは読み込もうとしているファイルサイズが大きすぎると思われますが、
            PLSQLで実装するためには、読み込みファイルを分割などするしか方法はないのでしょうか?
            また、サイズの大きいファイルをInsertする良い方法をご存知の方がいましたらご教示をお願い致します。
            普通はSQL*LOADERになるのではないでしょうか。。
            もしくは外部表として見せる、とか。
            ※調べてみましたが、32767は「1行当たりのサイズ上限」と書いているブログもありました。
            (真相は1行当たりのサイズではなく、1ファイルのサイズであっているのでしょうか・・・?)
            ttp://ameblo.jp/archive-redo-blog/entry-10034793463.html
            マニュアルによると、以下になります。

            -FOPEN
            max_linesize 改行文字を含むこのファイルの1行当たりの最大文字数(最小値は1、最大値は32767)。指定がない場合は、デフォルト値である1024が指定されます
            <<http://docs.oracle.com/cd/E16338_01/appdev.112/b56262/u_file.htm#i1003526>>
            -GET_LINE
            bufferパラメータの最大サイズは、より小さいサイズをFOPENに指定しないかぎり、32767バイトです。指定しない場合は、デフォルト値の1024が設定されます
            <<http://docs.oracle.com/cd/E16338_01/appdev.112/b56262/u_file.htm#i1003796>>
            • 3. Re: UTL_FILE.FOPENの最大読み取りファイルサイズについて
              keiji.k
              hamadeguchi様
              asahideO様
              ご返信ありがとうございます。
              vInputのサイズが小さいということはありませんか?
              ご指摘の通り、ここが問題になっていました。
              読み込み元のデータのエンコードや改行コードが想定していたものと違った行が連続してあったために、
              長い1行として読み込んでいた為に、VALUE_ERRORエラーが発生していました。

              ご教示頂きましたSQL*LOADERについては、上記エラーを修正後パフォーマンスが運用に耐えれないと判断した際に
              実装しようと考えていましたが、Insert処理+Insertしたデータを加工し出力するのに5分ほどだったのでSQL*LOADERについては
              見送る事になりました。

              マニュアル、参考にさせて頂いたブログ通り、1行当たりのバッファサイズ制限でした。

              ------------------
              max_linesize 改行文字を含むこのファイルの1行当たりの最大文字数(最小値は1、最大値は32767)。指定がない場合は、デフォルト値である1024が指定されます
              bufferパラメータの最大サイズは、より小さいサイズをFOPENに指定しないかぎり、32767バイトです。指定しない場合は、デフォルト値の1024が設定されます

              ご回答ありがとうございました。