Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file

bloboracle

I am stuck at this for a while and had been Googling for answer but can't seem to find any….
I have an Oracle 9i table with BLOB field that I need to extract the BLOB field into a raw file on the LINUX server, there are thousand of rows so I need to write a kind of loop to accomplish this.

I got this :

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;

I tested the Loop with the DBMS_OUTPUT and it worked fine. However:

  • When I ran the code all 10 of the *.png files show up in my BLOBDIR – but the files is empty.
  • When I ran the code for each individual file (i.e. FOR rec IN (SELECT id, fname, fblob FROM tablename where fname ='filename.png')) – I got the file and it has data.
  • When I ran the code with only 2 files in the loop (i.e. FOR rec IN (SELECT id, fname, fblob FROM tablename where fname in ('filename.png', 'filename2.png')) – both files got written to the BLOBDIR – but only the first file has data, but the second file show 0 data.

I ran this code through TOAD, and through sqlplus – and same result. Can someone help me with this ?
Thank you.

Best Answer

This is code that I took from a working Perl script. There is a limit of 32k for using utl_file, last I checked. You probably need to reset the v_curr_idx variable with every loop, otherwise it would write the first file and skip any smaller files.

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;
/
Related Question