10 Replies Latest reply: Dec 3, 2012 3:12 AM by OraclePSP RSS

    Download file from db server to local

    OraclePSP
      Hi all,

      after implementing the procedure i found here [http://docs.oracle.com/cd/E10513_01/doc/appdev.310/e10497/up_dn_files.htm#CHDECDAJ] i am trying to use a different procedure to download directly from an oracle directory that is
      PROCEDURE DOWNLOAD_PUBLIC_DIR (v_file_name varchar2) as
           v_length     number;
          bf BFILE;
          Lob_loc     blob;                     
          v_mime          varchar2(255);
          vexists BOOLEAN;
           vfile_length NUMBER;
           vblocksize NUMBER;
      begin
           UTL_FILE.FGETATTR('PUBLIC_DIR',v_file_name,vexists,vfile_length,vblocksize);
           if vexists then
                DBMS_LOB.createtemporary (Lob_loc, TRUE, DBMS_LOB.SESSION);
                bf := BFILENAME('PUBLIC_DIR', v_file_name);
                DBMS_LOB.FILEOPEN(bf,dbms_lob.file_readonly);
                DBMS_LOB.LOADFROMFILE(Lob_loc, bf, DBMS_LOB.GETLENGTH(bf));
                DBMS_LOB.FILECLOSE(bf);
               owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
               htp.p('Content-length: ' || v_length);
               htp.p('Content-Disposition: attachment; filename="' || v_file_name || '"');
               owa_util.http_header_close;
               wpg_docload.download_file( Lob_loc );
           end if;
       end;
      then run this
      GRANT EXECUTE ON DOWNLOAD_PUBLIC_DIR TO PUBLIC
      In my apex application i create a display only item and in the label i try to use this in the label
      <a href="DOWNLOAD_PUBLIC_DIR?v_file_name='myFileOnDBServer.xls'">Download</a>
      or this in the source (as i already do in others part of the app)
      select '<a href="pspappweb.DOWNLOAD_PUBLIC_DIR?v_file_name='||'myFileOnDBServer.xls'||'">Download</a>' from dual
      when i click the link the message in
      Not Found
      The requested URL /pls/apex/DOWNLOAD_PUBLIC_DIR was not found on this server.
      What i am obviously missing?
      Thanks to all.
      Alex

      Edited by: OraclePSP on 29-nov-2012 5.58
        • 1. Re: Download file from db server to local
          Christian Neumueller-Oracle
          Hi Alex,

          that's probably because of the request validation function, in your mod_plsql DAD, EPG or Apex Listener config. Watch out for wwv_flow_epg_include_modules.authorize. You can enable additional entry points by overwriting APEX_040200's wwv_flow_epg_include_mod_local procedure. It's described in apex/core/wwv_flow_epg_include_local.sql.

          Regards,
          Christian
          • 2. Re: Download file from db server to local
            OraclePSP
            Hi Christian,
            thanks for your reply but i am using this
            PROCEDURE DOWNLOAD_FILE (parFile_ID in number) as
                v_mime         varchar2(255);
                v_length     number;
                v_file_name varchar2(2000);
                Lob_loc     BLOB;
            BEGIN
                select files_mime_type, files_content, files_name, dbms_lob.getlength(files_content)
                into v_mime,lob_loc,v_file_name,v_length
                from myTableWithBlobField
                where files_id = parFile_ID;
                --
                -- 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="' || v_file_name || '"');
                -- close the headers
                owa_util.http_header_close;
                -- download the BLOB
                wpg_docload.download_file( Lob_loc );
            END DOWNLOAD_FILE;
            without problems to download file stored in a table.
            I need to check all the config you talk about?

            Regards,
            Alex
            • 3. Re: Download file from db server to local
              Christian Neumueller-Oracle
              Hi Alex,

              I also noticed that you didn't mention a public synonym for the DOWNLOAD_PUBLIC_DIR procedure. It could simply be a name resolution problem. If not, I'd check the configs. Maybe somebody already added an exception for DOWNLOAD_FILE.

              Regards,
              Christian
              • 4. Re: Download file from db server to local
                OraclePSP
                Hi Christian,

                i found the function you talk about and , via SQL Developer accessing as SYSTEM in APEX_010100, i edit it in this way
                create or replace
                function             wwv_flow_epg_include_mod_local(
                    procedure_name in varchar2)
                return boolean
                is
                begin
                    --return false; -- remove this statement when you modify this function
                    --
                    -- Administrator note: the procedure_name input parameter may be in the format:
                    --
                    --    procedure
                    --    schema.procedure
                    --    package.procedure
                    --    schema.package.procedure
                    --
                    -- If the expected input parameter is a procedure name only, the IN list code shown below
                    -- can be modified to itemize the expected procedure names. Otherwise you must parse the
                    -- procedure_name parameter and replace the simple code below with code that will evaluate
                    -- all of the cases listed above.
                    --
                    if upper(procedure_name) in (
                          'DOWNLOAD_PUBLIC_DIR') then
                        return TRUE;
                    else
                        return FALSE;
                    end if;
                end wwv_flow_epg_include_mod_local;
                After compiling it without errors i am still not able to call the proc using URL as i expect.
                After looking at the db i realize that
                - i am using SCHEMA1.DOWNLOAD_FILE@APEXDB procedure but as you can see there is no previous authorization for DOWNLOAD_FILE
                - i duplicate the proc as SCHEMA2.DOWNLOAD_FILE@APEXDB but if i modify the call the link don't work anymore

                I known that i am asking a lot but , if possible, i need some tips to verify the correct config/settings

                Regards,
                Alex
                • 5. Re: Download file from db server to local
                  Christian Neumueller-Oracle
                  Hi Alex,

                  you probably mean APEX_040100. Are you using mod_plsql, Apex Listener, EPG? Did you verify that wwv_flow_epg_include_modules.authorize is used in the config? Maybe you should connect as the DAD user in SQL Developer and try to execute the procedure. It may raise errors, but you'd see if the name resolution works ok.

                  Regards,
                  Christian
                  • 6. Re: Download file from db server to local
                    OraclePSP
                    Hi Christian,

                    after your replay i found and read those:
                    - [http://docs.oracle.com/cd/B14099_19/web.1012/b14010/concept.htm#i1012038]
                    - [http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php]
                    - [http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_epg.htm]
                    and after all i connect to my db APEXDB as system and run this
                    DECLARE
                      l_names  DBMS_EPG.varchar2_table;
                    BEGIN
                         DBMS_EPG.GET_DAD_LIST (dad_names     => l_names);
                      DBMS_OUTPUT.put_line('Mappings');
                      DBMS_OUTPUT.put_line('========');
                      FOR i IN 1 .. l_names.count LOOP
                        DBMS_OUTPUT.put_line(l_names(i));
                      END LOOP;
                    END;
                    and i expect to find at least */pls/apex* but no records was found

                    I also found in */ora10/HTTP/Apache/modplsql/conf/dads.conf*
                    <Location /pls/apex>
                       Order deny,allow
                       PlsqlDocumentPath docs
                       AllowOverride None
                       PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
                       PlsqlDatabaseConnectString myHOST:myPORT:mySID ServiceNameFormat
                       PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
                       PlsqlAuthenticationMode Basic
                       SetHandler pls_handler
                       PlsqlDocumentTablename wwv_flow_file_objects$
                       PlsqlDatabaseUsername APEX_PUBLIC_USER
                       PlsqlDefaultPage apex
                       PlsqlDatabasePassword xxxxxxxxxxxx
                    #   PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
                       Allow from all
                    </Location>
                    and remove # from PlsqlRequestValidationFunction but nothing change also after restarting Apache.

                    I am not using Apex Listener. In which other way i can find where and how the SCHEMA1.DOWNLOAD_FILE procedure is authorized?

                    Regards,
                    Alex
                    • 7. Re: Download file from db server to local
                      Christian Neumueller-Oracle
                      Hi Alex,

                      are you using mod_plsql or EPG then, or are you not sure? If you execute
                      begin
                          owa_util.print_cgi_env;
                      end;
                      in SQL Workshop, it should give you information about the web server.

                      Regards,
                      Christian
                      • 8. Re: Download file from db server to local
                        OraclePSP
                        Hi Christian,

                        this is the result
                        PLSQL_GATEWAY = WebDb
                        GATEWAY_IVERSION = 3
                        SERVER_SOFTWARE = Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server
                        GATEWAY_INTERFACE = CGI/1.1
                        SERVER_PORT = myPORT
                        SERVER_NAME = myHOST
                        REQUEST_METHOD = POST
                        PATH_INFO = /wwv_flow.show
                        SCRIPT_NAME = /pls/apex
                        REMOTE_ADDR = 123.45.67.890
                        SERVER_PROTOCOL = HTTP/1.1
                        REQUEST_PROTOCOL = HTTP
                        REMOTE_USER = APEX_PUBLIC_USER
                        HTTP_CONTENT_LENGTH = 301
                        HTTP_CONTENT_TYPE = application/x-www-form-urlencoded; charset=UTF-8
                        HTTP_USER_AGENT = Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20100101 Firefox/14.0.1
                        HTTP_HOST = myHOST:myPORT
                        HTTP_ACCEPT = text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
                        HTTP_ACCEPT_ENCODING = gzip, deflate
                        HTTP_ACCEPT_LANGUAGE = it-it,it;q=0.8,en-us;q=0.5,en;q=0.3
                        HTTP_REFERER = http://myHOST:myPORT/pls/apex/f?p=4500:1003:3289855477968001::NO:::
                        HTTP_ORACLE_ECID = 1354281086:123.45.67.890:59048002:0:50,0
                        WEB_AUTHENT_PREFIX = 
                        DAD_NAME = apex
                        DOC_ACCESS_PATH = docs
                        DOCUMENT_TABLE = wwv_flow_file_objects$
                        PATH_ALIAS = 
                        REQUEST_CHARSET = AL32UTF8
                        REQUEST_IANA_CHARSET = UTF-8
                        SCRIPT_PREFIX = /pls
                        Regards,
                        Alex
                        • 9. Re: Download file from db server to local
                          Christian Neumueller-Oracle
                          Hi Alex,

                          this is mod_plsql. You can ignore dbms_epg and related stuff, then. The PlsqlRequestValidationFunction you mentioned in the DAD setting contains the function which control what can be accessed. Since commenting the line didn't help, maybe you have several OHS installations, or you modified the wrong file?

                          Regards,
                          Christian
                          • 10. Re: Download file from db server to local
                            OraclePSP
                            Hi Christian,

                            if i modify the dads.conf that i found, the original SCHEMA1.DOWNLOAD_FILE dont work anymore and my SCHEMA2.DOWNLOAD_PUBLIC_DIR still not work. So i restore the dads.conf as it was and compile SCHEMA1.DOWNLOAD_PUBLIC_DIR. In this way both the procedures are working.
                            My original question is answered but finally it was not the real question.
                            As you can see (read) i am a newbie at this level of integration between Oracle and HTTP and thanks to your help i have a lot to study and to upgrade my skills.
                            I cant ask you to help me more than you already does and i thanks you again for the time you spent on this. I dont know exactly the installation and config of the istance so its hard to me to make other help me.
                            Anyway, suggestions and tips are always appreciated.

                            Regards,
                            Alex