將 Oracle BLOB 欄位寫入游標或 For 循環結果空文件中的原始文件

我被困了一段時間，一直在Google搜尋答案，但似乎找不到任何……我有一個帶有BLOB欄位的 Oracle 9i 表，我需要將BLOB欄位提取到 LINUX 伺服器上的原始文件中，有數千行，所以我需要編寫一種循環來完成此操作。

我懂了 ：

DECLARE
   i    NUMBER := 0;
   l_file      UTL_FILE.FILE_TYPE;
   l_buffer    RAW(32767);
   l_amount    BINARY_INTEGER := 32767;
   l_pos       INTEGER := 1;
   l_blob      BLOB;
   l_blob_len  INTEGER;
BEGIN
   FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname like '%png')
   LOOP

           i := i + 1;
           --DBMS_OUTPUT.put_line ('Record ' || i || ' id ' || rec.id); 
           --DBMS_OUTPUT.put_line ('Record ' || i || ' fname ' || rec.fname);

       l_blob_len := DBMS_LOB.getlength(rec.fblob);

         -- Open dest file.
           l_file := UTL_FILE.fopen('BLOBDIR',rec.fname,'w', 32767);

         -- Read chunks of the BLOB and throw them to the file until done.
       WHILE l_pos < l_blob_len LOOP
               DBMS_LOB.read(rec.ATTACHMENT_BLOB, l_amount, l_pos, l_buffer);
               UTL_FILE.put_raw(l_file, l_buffer, TRUE);
               l_pos := l_pos + l_amount;
           END LOOP;
        END LOOP;

     -- DBMS_OUTPUT.put_line ('Procedure Looping Example is done');
  END;

我用 the 測試了 Loop，DBMS_OUTPUT它執行良好。然而：

• 當我執行程式碼時，所有 10 個 *.png 文件都顯示在我的 BLOBDIR 中 - 但這些文件是空的。
• 當我為每個單獨的文件（即 FOR rec IN (SELECT id, fname, fblob FROM tablename where fname ='filename.png')）執行程式碼時 - 我得到了文件並且它有數據。
• 當我在循環中僅使用 2 個文件執行程式碼時（即FOR rec IN (SELECT id, fname, fblob FROM tablename where fname in ('filename.png', 'filename2.png')） - 兩個文件都寫入 BLOBDIR - 但只有第一個文件有數據，但第二個文件顯示 0 數據。

我通過 TOAD 和 sqlplus 執行此程式碼 - 結果相同。有人可以幫我弄這個嗎 ？謝謝你。

這是我從一個有效的 Perl 腳本中獲取的程式碼。使用 utl_file 有 32k 的限制，我上次檢查過。您可能需要在每個循環中重置 v_curr_idx 變數，否則它將寫入第一個文件並跳過任何較小的文件。

DECLARE
  v_column_name VARCHAR2(50) := 'my_column';
  v_pkey_val    NUMBER       := 123;
  v_source_code BLOB;
  v_curr_idx    NUMBER       := 1;
  v_max_chunk   NUMBER       := 32760;
  v_lob_len     NUMBER;
  v_raw_bytes   RAW(32760);
  l_output      utl_file.file_type;
BEGIN
-- define output directory
  l_output      := utl_file.fopen( '$db_dir_name', '$file_name', 'WB' );
-- get length of blob
  SELECT dbms_lob.getlength( v_column_name )
    INTO v_lob_len
    FROM <my table>
   WHERE pkey_val = v_pkey_val;

-- save blob length
  v_lob_len := v_lob_len;

-- select blob into variable
  SELECT v_column_name
    INTO v_source_code
    FROM <my table>
   WHERE pkey_val = v_pkey_val;

  WHILE v_curr_idx < v_lob_len
  LOOP
     IF ( v_lob_len - v_curr_idx ) < v_max_chunk
     THEN
        v_max_chunk := ( v_lob_len - v_curr_idx )+2;
     END IF;

     DBMS_LOB.READ(    v_source_code, v_max_chunk, v_curr_idx, v_raw_bytes );
     UTL_FILE.PUT_RAW( l_output,      v_raw_bytes, TRUE );
     UTL_FILE.FFLUSH(  l_output );
     v_curr_idx := v_curr_idx + v_max_chunk;
  END LOOP;

  utl_file.fclose(l_output);
END;
/

我最終創建了 SP ：

    CREATE OR REPLACE procedure SP_Blob2Fl(pId integer, pFilename varchar2, pBlob blob) is
        l_blob_len INTEGER;
        l_buffer    RAW(32767);
        l_amount    BINARY_INTEGER := 32767;
        l_pos       INTEGER := 1;
        l_file      UTL_FILE.FILE_TYPE;

   BEGIN
        l_blob_len := DBMS_LOB.getlength(pBlob);
        -- Open dest file.
        l_file := UTL_FILE.fopen('BLOBDIR',pFilename,'w', 32767);

        -- Read chunks of the BLOB and throw them to the file until done.
        WHILE l_pos < l_blob_len LOOP
           DBMS_LOB.read(pBlob, l_amount, l_pos, l_buffer);
           UTL_FILE.put_raw(l_file, l_buffer, TRUE);
           UTL_FILE.FFLUSH(l_file );
           l_pos := l_pos + l_amount;
        END LOOP;     

        utl_file.fclose(l_file);

   END;

然後通過表循環呼叫SP；

   begin 
   for c in (SELECT Table.Id, Table.flname, Table.blobfld 
       FROM Table  where Table.flname like '%txt') LOOP
       SP_Blob2Fl(c.Id, c.flname, c.blob);
   end loop;
   end;

這似乎對我有用。

引用自：https://dba.stackexchange.com/questions/184009