SQL & PL/SQL

Prashant Dabral wrote:
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.

unwilling or incapable to use GOOGLE yourself

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

Something like this:

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

Not tested.

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.

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.

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

What difference will knowing the mime type make?

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.

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.

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.

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

Hi Prashant,

Same code as above was used  for extracting data from blob column and store to a text  file. But its not working in my case.When open filed getting @@@@@@@@@@ symbols .Can you let me know what changes to be done to code to open file in readable format.

Thanks in advance.

Hi @SaiRam_Bathula

I believe that you are not writing file with right extension and you are ended up opening the file with wrong application.

Or if you aren't aware about the actual mime type of your blob field then you can follow this link Detect Mime Type from BLOB stream

to detect mime type and then you can create a file with correct extension of it.

Hope this helps you.

Hi Prashant,

Thanks for your response.

The blob field has readings of one month of 15 mins interval.

My requirement is when I open a file I need all those readings info from blob.

With the above code I am getting file generated but with  below data

@ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @ @ @@ @

So can you please let me know how can I covert this data to correct format.

Why are you hijacking someone else's thread when you already have a thread open with the same exact question?