1 2 Previous Next 18 Replies Latest reply on Nov 8, 2018 7:39 PM by fac586

    Download multiple Blob contents from Oracle

    Jian-cdo

      Hello,

       

      I can upload/download blob contents (such as pdf file) into Oracle through APEX without issue.  The first question is whatthe best practice is to get multiple blobs contents (such as thousands of files) in one transaction and save them to the network drive?  The second question is that we have a legacy web application, which is developed in Coldfusion, it stores a lots of files as blob into our Oracle database, somehow there is decoding issue when we try to download them by Utl_FILE, say, can't open these files after downloading.  So I wonder what the difference would be when we use different programming language to store blobs into Oracle? 

       

      Thanks.

        • 1. Re: Download multiple Blob contents from Oracle
          fac586

          Jian-cdo wrote:

           

          I can upload/download blob contents (such as pdf file) into Oracle through APEX without issue. The first question is whatthe best practice is to get multiple blobs contents (such as thousands of files) in one transaction and save them to the network drive?

          Unclear.

           

          • Are you talking about uploading or downloading?
          • Where are the files originally stored?
          • What is the relationship of the "network drive" to the database server?
          • What initiates the upload/download? When does this occur?

           

          Note that for security reasons HTTP can only download a single file in response to a request. Retrieving "thousands" of files would require them to be packaged into a single archive file prior to downloading, or using a different protocol.

          The second question is that we have a legacy web application, which is developed in Coldfusion, it stores a lots of files as blob into our Oracle database, somehow there is decoding issue when we try to download them by Utl_FILE, say, can't open these files after downloading. So I wonder what the difference would be when we use different programming language to store blobs into Oracle?

          UTL_FILE cannot be used to "download" files. Please explain exactly what you are doing in more detail.

           

          • What type(s) of files are involved?
          • What "can't open these files after downloading"?
          • Are the files stored with the relevant MIME type? Is this specified in the HTTP header generated by the download process?

           

          Oracle Forms was known to corrupt JPG files as they were loaded (Forms could still display them afterwards, nothing else could). It's possible (but unlikely) that ColdFusion is also somehow responsible for altering the files.

          • 2. Re: Download multiple Blob contents from Oracle
            Alli Pierre Yotti

            Jian-cdo wrote:

             

            Hello,

             

            I can upload/download blob contents (such as pdf file) into Oracle through APEX without issue.  The first question is whatthe best practice is to get multiple blobs contents (such as thousands of files) in one transaction and save them to the network drive?  The second question is that we have a legacy web application, which is developed in Coldfusion, it stores a lots of files as blob into our Oracle database, somehow there is decoding issue when we try to download them by Utl_FILE, say, can't open these files after downloading.  So I wonder what the difference would be when we use different programming language to store blobs into Oracle?

             

            To download multiple PDF, you need to Zip it all and download it. Take a look here

            https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_zip.htm#AEAPI29942

             

            Example:

             

            create     or replace procedure export_pdf is
                l_zip_file   blob;
                v_filename   varchar2(255);
            begin
                v_filename   := 'export.zip';
                for l_file in ( select filename,
                                       content
                                  from my_table
                ) loop
                    apex_zip.add_file(
                        p_zipped_blob   => l_zip_file,
                        p_file_name     => l_file.filename || '.pdf',
                        p_content       => l_file.content
                    );
                end loop;
                apex_zip.finish(p_zipped_blob   => l_zip_file);
                owa_util.mime_header(
                    coalesce(
                        'application/zip',
                        'application/octet'
                    ),
                    false
                );
                htp.p('Content-length: ' || dbms_lob.getlength(l_zip_file) );
                htp.p('Content-Disposition:  attachment; filename="'|| v_filename || '"');
                owa_util.http_header_close;
                wpg_docload.download_file(l_zip_file);
            end export_pdf;
            
            • 3. Re: Download multiple Blob contents from Oracle
              Jian-cdo

              Both questions target at how to download the blobs that were already saved on Oracle.  File formats so far include *.pdf, *.xls, *.doc. 

              To make the problem simpler, we will say, only download the files onto Oracle server.

              The files stored by Coldfusion has a column that indicates mine type, but good question on "Is this specified in the HTTP header generated by the download process", need to check that later

              For downloaded files, when I tried to open it, it either complains:  the file is in a different format that specified by the file extension; or Adobe Reader could not open it because it's either not a supported file tyhpe or because the file has been damaged;  or ask to select Text encoding...

              But on the web application developed by Coldfusion, user can still download the file without issue.

               

              Thanks!

              • 4. Re: Download multiple Blob contents from Oracle
                Jian-cdo

                Thanks, will try.  But one more question: the blobs contain pdf, spreadsheet and word files, do we need to zip them all?

                • 5. Re: Download multiple Blob contents from Oracle
                  fac586

                  Jian-cdo wrote:

                   

                  Both questions target at how to download the blobs that were already saved on Oracle. File formats so far include *.pdf, *.xls, *.doc.

                  To make the problem simpler, we will say, only download the files onto Oracle server.

                  The files stored by Coldfusion has a column that indicates mine type, but good question on "Is this specified in the HTTP header generated by the download process", need to check that later

                  For downloaded files, when I tried to open it, it either complains: the file is in a different format that specified by the file extension; or Adobe Reader could not open it because it's either not a supported file tyhpe or because the file has been damaged; or ask to select Text encoding...

                  But on the web application developed by Coldfusion, user can still download the file without issue.

                  We need clarity on exactly what you mean by "download". In standard APEX usage this means delivering a file from the database or web server to a client browser via HTTP in response to a request initiated by a user. The information provided here in respect of "we try to download them by UTL_FILE" and "only download the files onto Oracle server" hints at something else. Please describe the entire requirement from end to end, and post the source code for the download process.

                  • 6. Re: Download multiple Blob contents from Oracle
                    Alli Pierre Yotti

                    Jian-cdo wrote:

                     

                    Thanks, will try.  But one more question: the blobs contain pdf, spreadsheet and word files, do we need to zip them all?

                     

                    You can zip them all if you want. You can also extend the above code to download each format separatly.

                    • 7. Re: Download multiple Blob contents from Oracle
                      fac586

                      Jian-cdo wrote:

                       

                      Thanks, will try. But one more question: the blobs contain pdf, spreadsheet and word files, do we need to zip them all?

                      To download more than one in response to a single request? Yes.

                      • 8. Re: Download multiple Blob contents from Oracle
                        Mike Kutz

                        fac586 wrote:

                         

                        Jian-cdo wrote:

                         

                        Thanks, will try. But one more question: the blobs contain pdf, spreadsheet and word files, do we need to zip them all?

                        To download more than one in response to a single request? Yes.

                        To be clear:

                        • 1 Web Request can only receive 1 Web Response.
                        • When you make a request, you can only retrieve 1 file.
                        • you need to create a zip file that contains all of the files.

                         

                        Other options

                        create and incorporate a download manager

                        • the Web Response is actually the parameter file/JSON for the Download Manager of "what to download and where to place it/name it".

                         

                        MK

                        • 9. Re: Download multiple Blob contents from Oracle
                          Jian-cdo

                          Thanks for all your replies (including other experts)!   Our IT tried with UTL_FILE to download the files, which means they still do it as sysdba, not an application developer, so, no web request. 

                          • 10. Re: Download multiple Blob contents from Oracle
                            fac586

                            Jian-cdo wrote:

                             

                            Thanks for all your replies (including other experts)! Our IT tried with UTL_FILE to download the files, which means they still do it as sysdba, not an application developer, so, no web request.

                            At this point I don't understand if there's still a problem, and if there is, what form it takes. So, back to basics:

                             

                            • What version and edition of the database are you using?
                            • Does this have anything to do with APEX? If so:
                              • What APEX version are you using?
                              • Where does this "download" that involves "no web request" fit in to the APEX application?
                            • What is actually happening?
                              • Files are stored as BLOBs in the database.
                              • Files are "downloaded" by "IT" using unidentified programs based on UTL_FILE that run as SYSDBA
                                • What is your relationship to "IT"?
                                • Where are these files "downloaded" to?
                                • Why is this process run with SYSDBA privileges?
                                • What does this UTL_FILE code look like? Many UTL_FILE API methods are for character data and are unsuitable for reading/writing binary data from BLOBs. Writing the data to a file in character mode would certainly result in the issues reported above.
                            • 11. Re: Download multiple Blob contents from Oracle
                              Jian-cdo

                              IT means IT department of the company, who is the database administrator.  Here is the sample code they follow to get the blob from Oracle (on which we are sure the blob contents are not corrupted).  So I wonder what else that cold-fusion application did to the blob contents.

                               

                              create or replace procedure

                              LoadFileIntoBLOB

                              (

                              myfilename IN varchar2,

                              mydirloc IN varchar2

                              )

                              I S

                              out_blob blob;

                              in_file bfile := bfilename(mydirloc, myfilename);

                              blob_length integer;

                              BEGIN

                              /* Obtain the size of the blob file */

                              dbms_lob.fileopen(in_file, dbms_lob.fi le_readonly);

                              blob_length:=dbms_lob.getlength(in_file);

                              dbms_lob.fileclose(in_file);

                              /* Insert a new record into the table containing the

                              filename you have specified and a LOB LOCATOR.

                              Return the LOB LOCATOR and assign it to out_bl ob. */

                              insert into lob_table values (myfilename, empty_blob ())

                              returning blobdata into out_blob;

                              /* Load the image into the database as a BLOB */

                              dbms_lob.open(in_file, dbms_lob.lob_readonly);

                              dbms_lob.open(out_blob, dbms_lob.lob_readwrite );

                              dbms_lob.loadfromfile(out_blob, in_file, blob_le ngth);

                              /* Close handles to blob and file */

                              dbms_lob.close(out_blob);

                              dbms_lob.close(in_file);

                              commit;

                              /* Confirm insert by querying the database

                              for LOB length information and output resu lts */

                              blob_length := 0;

                              select dbms_lob.g etlength(blobdata) into blob_length

                              from lob_table where filename = myfilename;

                              dbms_output.put_line('Successfully inserted BLOB ''' || myfilename || ''' of size ' || blob_length

                              || ' bytes.');

                              exception when others then

                              dbms_output.put_line('Er ror occurred while inserting BLOB: '|| sqlerrm);

                              end;

                              • 12. Re: Download multiple Blob contents from Oracle
                                Jian-cdo

                                Unfortunately, zip doesn't solve the problem.

                                • 13. Re: Download multiple Blob contents from Oracle
                                  Mike Kutz

                                  Jian-cdo wrote:

                                   

                                  Unfortunately, zip doesn't solve the problem.

                                  Are you wanting the multiple files to be downloaded via web request?

                                  Or -- are you OK asking your DBAs to extract the files themselves and email the results?

                                   

                                  MK

                                  • 14. Re: Download multiple Blob contents from Oracle
                                    fac586

                                    Jian-cdo wrote:

                                     

                                    IT means IT department of the company, who is the database administrator. Here is the sample code they follow to get the blob from Oracle (on which we are sure the blob contents are not corrupted). So I wonder what else that cold-fusion application did to the blob contents.

                                     

                                    create or replace procedure

                                    LoadFileIntoBLOB

                                    (

                                    myfilename IN varchar2,

                                    mydirloc IN varchar2

                                    )

                                    I S

                                    out_blob blob;

                                    in_file bfile := bfilename(mydirloc, myfilename);

                                    blob_length integer;

                                    BEGIN

                                    /* Obtain the size of the blob file */

                                    dbms_lob.fileopen(in_file, dbms_lob.fi le_readonly);

                                    blob_length:=dbms_lob.getlength(in_file);

                                    dbms_lob.fileclose(in_file);

                                    /* Insert a new record into the table containing the

                                    filename you have specified and a LOB LOCATOR.

                                    Return the LOB LOCATOR and assign it to out_bl ob. */

                                    insert into lob_table values (myfilename, empty_blob ())

                                    returning blobdata into out_blob;

                                    /* Load the image into the database as a BLOB */

                                    dbms_lob.open(in_file, dbms_lob.lob_readonly);

                                    dbms_lob.open(out_blob, dbms_lob.lob_readwrite );

                                    dbms_lob.loadfromfile(out_blob, in_file, blob_le ngth);

                                    /* Close handles to blob and file */

                                    dbms_lob.close(out_blob);

                                    dbms_lob.close(in_file);

                                    commit;

                                    /* Confirm insert by querying the database

                                    for LOB length information and output resu lts */

                                    blob_length := 0;

                                    select dbms_lob.g etlength(blobdata) into blob_length

                                    from lob_table where filename = myfilename;

                                    dbms_output.put_line('Successfully inserted BLOB ''' || myfilename || ''' of size ' || blob_length

                                    || ' bytes.');

                                    exception when others then

                                    dbms_output.put_line('Er ror occurred while inserting BLOB: '|| sqlerrm);

                                    end;

                                    That loads a file into an Oracle BLOB rather than downloading it from the database (to a so far unspecified location). There is also no sign of any use of UTL_FILE.

                                    1 2 Previous Next