Forum Stats

  • 3,839,329 Users
  • 2,262,481 Discussions
  • 7,900,934 Comments

Discussions

Unloading BLOB to Original file like pdf, doc, xml, xls, rtf etc

Utsav
Utsav Member Posts: 859 Silver Badge
edited Feb 14, 2017 9:23AM in SQL & PL/SQL

Hi,

We have application, from where User can upload his analysis in pdf, xml, doc, xls etc

These files stored in table T_DOC which have blob, file_name column.

Now, I want to write a program which can export these blob to database directory.

Tagged:
Kalpataru

Best Answer

  • Utsav
    Utsav Member Posts: 859 Silver Badge
    edited Jan 6, 2017 4:42AM Answer ✓

    I was in doubt whether writing BLOB directly using UTL_FILE would make it work, so should I do something out of the box to perform this activity.

    I achieved it using UTL_FILE itself, as others have posted code and what I got from googling is in alignment.

    Lesson Learn't: Unless you ended with search and basic code test, Only afterwards raise questions in forums

    CREATE OR REPLACE PROCEDURE BLOBTOFILE(PI_BLOB BLOB, PI_DIRECTORY VARCHAR2, PI_FILENAME VARCHAR2)ISL_FILE UTL_FILE.FILE_TYPE;L_BUFFER RAW(32767);L_AMOUNT BINARY_INTEGER := 32767;L_POS INTEGER := 1;L_BLOB_LEN INTEGER;BEGIN        L_BLOB_LEN := DBMS_LOB.GETLENGTH(PI_BLOB);        -- OPEN THE DESTINATION FILE.        L_FILE := UTL_FILE.FOPEN(PI_DIRECTORY,PI_FILENAME,'WB', L_AMOUNT);        -- READ CHUNKS OF THE BLOB AND WRITE THEM TO THE FILE        -- UNTIL COMPLETE.        WHILE L_POS < L_BLOB_LEN LOOP        DBMS_LOB.READ(PI_BLOB, L_AMOUNT, L_POS, L_BUFFER);        UTL_FILE.PUT_RAW(L_FILE, L_BUFFER, TRUE);        L_POS := L_POS + L_AMOUNT;        END LOOP;        -- CLOSE THE FILE.        UTL_FILE.FCLOSE(L_FILE);EXCEPTIONWHEN OTHERS THEN-- CLOSE THE FILE IF SOMETHING GOES WRONG.IF UTL_FILE.IS_OPEN(L_FILE) THENUTL_FILE.FCLOSE(L_FILE);END IF;RAISE;END BLOBTOFILE;
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 5, 2017 9:10AM
    Prashant Dabral wrote:Hi, We have application, from where User can upload his analysis in pdf, xml, doc, xls etcThese files stored in table T_DOC which have blob, file_name column.Now, I want to write a program which can export these blob to database directory.

    unwilling or incapable to use GOOGLE yourself

    https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+write+blob+to+file

    Utsav
  • Saubhik
    Saubhik Member Posts: 5,805 Gold Crown
    edited Jan 5, 2017 9:17AM

    Something like this:

    DECLARE<br/>  b      BLOB;<br/>  amount      BINARY_INTEGER;<br/>  file_handle UTL_FILE.FILE_TYPE;<br/>  l_pos        INTEGER            := 1;<br/>  l_blob_len   INTEGER;<br/>  l_buffer     RAW (32767);<br/>  l_amount     BINARY_INTEGER     := 32767;<br/>BEGIN<br/>  SELECT blob_data INTO b FROM blobdata WHERE id = 1;<br/>  file_handle := UTL_FILE.FOPEN('BLOB2FILE',<br/>                                'my_file.pdf',<br/>                                'wb' ----Note this line<br/>                                );<br/>  l_blob_len := DBMS_LOB.getlength (b);<br/>  -- Read chunks of the BLOB and write them to the file<br/>-- until complete.<br/>   WHILE l_pos < l_blob_len<br/>   LOOP<br/>      DBMS_LOB.READ (b, l_amount, l_pos, l_buffer);<br/>      UTL_FILE.put_raw (file_handle, l_buffer, FALSE);<br/>      l_pos := l_pos + l_amount;<br/>   END LOOP;<br/>   UTL_FILE.FCLOSE(file_handle);<br/>END;

    Not tested.

    Utsav
  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Jan 5, 2017 9:25AM

    create or replace procedure BlobToFile(pBlob blob, pOraDir varchar2, pFilename varchar2) is

      vFile    utl_file.file_type;

      vBuffer  raw(32767);

      vAmount  binary_integer := 32767;

      vPos     integer := 1;

      vBlobLen integer;

    begin

      vBlobLen := dbms_lob.getlength(pBlob);

      vFile := utl_file.fopen(pOraDir, pFilename, 'W', 32767);

      while vPos < vBlobLen

      loop

        dbms_lob.read(pBlob, vAmount, vPos, vBuffer);

        utl_file.put_raw(vFile, vBuffer, true);

        vPos := vPos + vAmount;

      end loop;

      utl_file.fclose(vFile);

    exception

      when OTHERS then

        if utl_file.is_open(vFile) then

          utl_file.fclose(vFile);

        end if;

        raise;

    end;

    /

    Then simply call the proc.

    Utsav
  • Unknown
    edited Jan 5, 2017 12:03PM
    We have application, from where User can upload his analysis in pdf, xml, doc, xls etcThese files stored in table T_DOC which have blob, file_name column.Now, I want to write a program which can export these blob to database directory.

    Make sure you already know what type of file (pdf, xml, doc, xls etc) it is supposed to be because a BLOB does NOT have a mime type.

    Detect Mime Type from BLOB stream

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jan 5, 2017 12:20PM

    What difference will knowing the mime type make?

  • Unknown
    edited Jan 5, 2017 12:39PM
    What difference will knowing the mime type make? 

    See that other thread I gave the link to.

    The extension of a file is often related to the mime type. If a BLOB represents a PDF file you would want to create a file with a PDF extension.

    The point is that the BLOB alone can't be counted on to determine the mime type or the 'normal' extension that should be used.

    You already know all of that. A file extension is fine for files but mime types are the more general indicator for byte streams used for web streaming.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jan 5, 2017 12:47PM

    Yes I do know all that, but the OP has a column with the file name already. They didn't say anything about a web server, just about creating a file.

  • Unknown
    edited Jan 5, 2017 2:37PM
    Yes I do know all that, but the OP has a column with the file name already.

    Yes - but that didn't say anything about having the extension, mime-type, file type or whatever.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jan 5, 2017 3:08PM

    Fair enough, but to me, the filename includes the full filename, including the extension. If that's not the case, then yes, being able to determine the mime type would be a good thing - it would even potentially be a good thing to have a column in the table that stores the mime type

  • Utsav
    Utsav Member Posts: 859 Silver Badge
    edited Jan 6, 2017 4:42AM Answer ✓

    I was in doubt whether writing BLOB directly using UTL_FILE would make it work, so should I do something out of the box to perform this activity.

    I achieved it using UTL_FILE itself, as others have posted code and what I got from googling is in alignment.

    Lesson Learn't: Unless you ended with search and basic code test, Only afterwards raise questions in forums

    CREATE OR REPLACE PROCEDURE BLOBTOFILE(PI_BLOB BLOB, PI_DIRECTORY VARCHAR2, PI_FILENAME VARCHAR2)ISL_FILE UTL_FILE.FILE_TYPE;L_BUFFER RAW(32767);L_AMOUNT BINARY_INTEGER := 32767;L_POS INTEGER := 1;L_BLOB_LEN INTEGER;BEGIN        L_BLOB_LEN := DBMS_LOB.GETLENGTH(PI_BLOB);        -- OPEN THE DESTINATION FILE.        L_FILE := UTL_FILE.FOPEN(PI_DIRECTORY,PI_FILENAME,'WB', L_AMOUNT);        -- READ CHUNKS OF THE BLOB AND WRITE THEM TO THE FILE        -- UNTIL COMPLETE.        WHILE L_POS < L_BLOB_LEN LOOP        DBMS_LOB.READ(PI_BLOB, L_AMOUNT, L_POS, L_BUFFER);        UTL_FILE.PUT_RAW(L_FILE, L_BUFFER, TRUE);        L_POS := L_POS + L_AMOUNT;        END LOOP;        -- CLOSE THE FILE.        UTL_FILE.FCLOSE(L_FILE);EXCEPTIONWHEN OTHERS THEN-- CLOSE THE FILE IF SOMETHING GOES WRONG.IF UTL_FILE.IS_OPEN(L_FILE) THENUTL_FILE.FCLOSE(L_FILE);END IF;RAISE;END BLOBTOFILE;
This discussion has been closed.