2 Replies Latest reply: Mar 18, 2013 6:19 PM by user7862894 RSS

    Downloading blob content from a custom table

    user7862894
      In our hosted Apex application, the following code from the Application Express Developer's Guide works great for allowing a user to download blob content from one of our custom tables via a download button. However, the code doesn't work on the Oracle Cloud because the "owa_util" package is no longer available. The code is as follows:

      CREATE OR REPLACE PROCEDURE download_my_file(p_file in number) AS
      v_mime VARCHAR2(48);
      v_length NUMBER;
      v_file_name VARCHAR2(2000);
      Lob_loc BLOB;
      BEGIN
      SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
      INTO v_mime,lob_loc,v_file_name,v_length
      FROM file_subjects
      WHERE id = p_file;
      --
      -- set up HTTP header
      --
      -- use an NVL around the mime type and
      -- if it is a null set it to application/octect
      -- application/octect may launch a download window from windows
      owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );

      -- set the size so the browser knows how much to download
      htp.p('Content-length: ' || v_length);
      -- the filename will be used by the browser if the users does a save as
      htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
      -- close the headers
      owa_util.http_header_close;
      -- download the BLOB
      wpg_docload.download_file( Lob_loc );
      end download_my_file;
      /

      Besides using web services, does anyone know of a way to do this? Is there a way to add access to the "owa_util" package in the cloud? I have also tried apex_util.get_blob_file_src but that is also unavailable in the Oracle Cloud.

      Thanks,

      Steve
        • 1. Re: Downloading blob content from a custom table
          joelkallman-Oracle
          Hi Steve,

          1) sys.owa_util is definitely available. You should be able to issue this block just fine from SQL Commands:

          begin
          owa_util.print_cgi_env;
          end;

          2) I suspect the problem is that you have created a procedure which both a) can't be granted execute to PUBLIC, and b) can't be called directly from the URL (due to a properly locked down APEX Listener which restricts the entry points, i.e.,. which procedures can be called directly from the URL).

          Even if the restriction against granting something to PUBLIC didn't exist, you'd still never get by the restriction on calling your own entry point from the URL. Also in a non-Cloud APEX installation (and even when using mod_plsql), this same whitelist of entry points is enforced via the PlsqlRequestValidationFunction (http://docs.oracle.com/cd/E23943_01/web.1111/e10144/under_mods.htm#HSADM698).

          A couple ways around this:

          1) You could define an on-demand process that, when called, downloads the requested file

          or

          2) You could consider exposing this functionality via a Media Resource RESTful Service, although at this time, I'm not necessarily sure of the details how you would secure access to it.

          I hope this helps.

          Joel
          • 2. Re: Downloading blob content from a custom table
            user7862894
            Following Joel's advice:

            The way I solved this was to split the code between two page processes and one application process. The download button first calls a page process to move the report data into a blob column and then calls another page process which is of "run application process" type. This calls the application level process where the download code, shown below, is called.

            Notice the following changes to the code from the one posted earlier (also from Joel)

            1) added sys.htp.init;
            2) "sys." to all htp, owa and wpg_docload calls
            3) added apex_application.stop_apex_engine; after the wpg_docload statement at the bottom of the script.

            Now the download button launches a "save as" dialog box and the report content is downloaded to the client.

            The code now looks like:

            CREATE OR REPLACE PROCEDURE download_my_file(p_file in number) AS
            v_mime VARCHAR2(48);
            v_length NUMBER;
            v_file_name VARCHAR2(2000);
            Lob_loc BLOB;
            BEGIN
            SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
            INTO v_mime,lob_loc,v_file_name,v_length
            FROM oehr_file_subject
            WHERE id = p_file;
            --
            -- set up HTTP header
            --
            -- use an NVL around the mime type and
            -- if it is a null set it to application/octect
            -- application/octect may launch a download window from windows

            sys.htp.init;

            sys.owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );

            -- set the size so the browser knows how much to download
            sys.htp.p('Content-length: ' || v_length);
            -- the filename will be used by the browser if the users does a save as
            sys. htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
            -- close the headers
            sys.owa_util.http_header_close;
            -- download the BLOB
            sys.wpg_docload.download_file( Lob_loc );

            apex_application.stop_apex_engine;

            end download_my_file;
            /



            Thanks Joel for your help.

            Steve