This discussion is archived
10 Replies Latest reply: Dec 3, 2012 1:12 AM by OraclePSP RSS

Download file from db server to local

OraclePSP Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points