2

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.

0

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;
/
  • I'm sorry, I'm new to Oracle and PSQL code, but I don't see the loop through the table to get the next file. I see the loop through the length of the blob and read it into raw file. Is the loop through the table done at the Perl script level and pass it into "$file_name" ? – lmbNbirt Aug 21 '17 at 20:39
  • This will read in one blob. If you want to read in more, you should change the code accordingly. For example, you can create a PL/SQL collection to hold the primary key for each record, do a bulk insert into and loop through that collection reading the BLOB for each record and writing it to disk. In fact you can turn this into a PL/SQL stored procedure that you call from the procedure that reads the all of the records and calls this procedure for each record. Which is another way to say that your code should be focused on one task and modular. – Gandolf989 Aug 22 '17 at 13:50
  • Thank you. I "turn this into a PL/SQL stored procedure that you call from the procedure that reads the all of the records and calls this procedure for each record." – lmbNbirt Sep 1 '17 at 15:20
0

I ended up with creating the 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;

Then call the SP through the table loop;

    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;

This seem to work for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.