Generate the URL of a Remote File using PL/SQL

Question

I created a Concurrent Program that creates an Excel File from a long, parametrized query using PL/SQL. Once the Program successfully completes, the file is placed in the remote server's directory and is usually around 4 MB in Size. I'm thinking of an approach to notify the requestor and enable him/her to save the file to their local directory. However, I cannot use UTL_MAIL to attach and send the file via email due to the 32 Kilobyte Limitation. (Does UTL_MAIL have an attachment limit of 32k).

In the same post, Tom Kyte preferred approach would be to:

1. store the attachment to the database.
2. email a very small email with a link. the link points to my database - using a URL.

With that, i was thinking taking the same approach and use the block below to notify the requestor and enable him/her to download the said Excel file:

declare

    l_url_link  varchar2(100);  -- how can i get the URL of the File?

BEGIN 

    UTL_MAIL.SEND(sender     => 'xxx@oracle.com'
                , recipients => 'Migs.Isip.23@Gmail.com'
                , subject    => 'Testmail'
                , message    => 'Your File is Ready to be downloaded, click the link here: '||l_url_link);

END;

My Questions would be:

1. How can i generate the "URL" of the Remote file using PL/SQL?
2. Do the users need to be granted access to the remote server to download the file?

Thank you!

Oracle Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Answer 1

Here is a pl/sql function I wrote to retrieve the URL of either the concurrent log file or output file. If you write your Excel file to the concurrent output, this should work fine. Let me know how you get on. I have not checked to see if this will give the correct mime-type or extension - not sure how EBS handles this but the function itself will definitely compile as is for 12.1.3.

Spec

FUNCTION  get_concurrent_url (p_file_type   IN  VARCHAR2
                             ,p_request_id  IN  NUMBER
                             ,p_expiry      IN  NUMBER)
RETURN VARCHAR2;

Body

/*  Get a URL to view the log/output
    File Type is LOG or OUT 
    Request ID is the concurrent request ID
    Expiry is in minutes                                                */
FUNCTION  get_concurrent_url (p_file_type   IN  VARCHAR2
                             ,p_request_id  IN  NUMBER
                             ,p_expiry      IN  NUMBER)
RETURN VARCHAR2                             
IS 

CURSOR c_gwyuid
IS
    SELECT  profile_option_value
    FROM    fnd_profile_options         FPO
           ,fnd_profile_option_values   FPOV
    WHERE   FPO.profile_option_name     =   'GWYUID'
    AND     FPO.application_id          =   FPOV.application_id
    AND     FPO.profile_option_id       =   FPOV.profile_option_id; 

CURSOR c_two_task     
IS
    SELECT  profile_option_value
    FROM    fnd_profile_options         FPO
           ,fnd_profile_option_values   FPOV
    WHERE   FPO.profile_option_name     =   'TWO_TASK'
    AND     FPO.application_id          =   FPOV.application_id
    AND     FPO.profile_option_id       =   FPOV.profile_option_id;

l_request_id        NUMBER;
l_file_type         VARCHAR2 (3 BYTE);
l_expiry            NUMBER;
l_two_task          VARCHAR2 (100 BYTE);
l_gwyuid            VARCHAR2 (100 BYTE);
l_url               VARCHAR2 (1024 BYTE); 

BEGIN

l_request_id    :=  p_request_id;
l_file_type     :=  p_file_type;
l_expiry        :=  p_expiry;

FOR i IN c_gwyuid LOOP
    l_gwyuid := i.profile_option_value;
END LOOP;

FOR i IN c_two_task LOOP
    l_two_task := i.profile_option_value;
END LOOP;        

IF l_file_type = 'LOG' THEN  
    l_url := fnd_webfile.get_url
                (file_type      =>  fnd_webfile.request_log
                ,id             =>  l_request_id
                ,gwyuid         =>  l_gwyuid
                ,two_task       =>  l_two_task
                ,expire_time    =>  l_expiry);
ELSE 
    l_url := fnd_webfile.get_url
                (file_type      =>  fnd_webfile.request_out
                ,id             =>  l_request_id
                ,gwyuid         =>  l_gwyuid
                ,two_task       =>  l_two_task
                ,expire_time    =>  l_expiry);
END IF;

RETURN l_url;

END get_concurrent_url;

Answer 2

I was able to find a solution for this using a (slightly different) method using the FND_GFM File Uploader Package in Oracle EBS. FND_GFM is a package usually used in Oracle EBS when uploading files from the front-end application pages.

First, generate the Excel file (xlsx) using the code from the previous post: Create an Excel File (.xlsx) using PL/SQL,

Then the file is inserted into FND_LOBS and removed the from the OS (for good housekeeping), and finally sent as an email using UTL_FILE:

procedure generate_and_send_excel
is

    l_content   varchar2(250);
    l_file_url  varchar2(4000);  
    l_directory varchar2(250);
    l_filename  varchar2(250);
    l_message   clob;
    l_instance   varchar2(100);
    l_ebs_url    varchar2(100);

begin

    /* your excel generation code here */

    l_content   := 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    l_directory := 'EXT_TAB_DATA';
    l_filename  := 'report.xlsx';

    select  instance_name
    into    l_instance
    from    v$instance;

    select  home_url
    into    l_ebs_url
    from    icx_parameters;

    IMPORT_TO_LOB (p_file_name    => l_filename          -- this is the actual filename of the saved OS File
                 , p_directory    => l_directory         -- should be a defined directory in the Database
                 , p_content_type => l_content           -- standard for Excel Files
                 , p_program_name => 'your prog here'
                 , p_program_tag  => 'your prog here'
                 , p_file_url     => l_file_url);        -- this will be the generated URL of your File

    utl_file.fremove(l_directory, l_filename);

    l_message := l_message||'<h2 style="color: #5e9ca0;">'||l_title||'</h2>';
    l_message := l_message||'<h3 style="color: #2e6c80;">Report is Ready for Download: <a href="'||l_file_url||'">'||l_filename||'</a></h3>';
    l_message := l_message||'<p>File was generated on '|| sysdate ||' from <a href="'||l_ebs_url||'">'||l_instance||'</a></p>';
    l_message := l_message||'<strong>Regards,</strong><br/><strong>Sample Team</strong>';
    l_message := l_message||'<br/><a href="mailto:ERPSupport@alorica.com">Sample@sample.com</a>';


    UTL_MAIL.SEND(sender     => 'SAMPLE@SAMPLE.com'
                , recipients => 'Migs.Isip.23@gmail.com'
                , subject    => 'Hello message'
                , message    => l_message
                , mime_type  => 'text/html; charset=us-ascii');

end generate_and_send_excel;

Procedure below to insert into FND_LOBS (there's no available seeded API):

Procedure IMPORT_TO_LOB (p_file_name    IN  FND_LOBS.FILE_NAME%TYPE
                       , p_directory    IN  dba_directories.directory_name%type  
                       , p_content_type IN  FND_LOBS.file_content_type%type
                       , p_program_name IN  FND_LOBS.program_name%type
                       , p_program_tag  IN  FND_LOBS.program_tag%type
                       , p_language     IN  FND_LOBS.language%type      default 'US'
                       , p_file_format  IN  FND_LOBS.file_format%type   default 'binary'
                       , p_file_url     OUT varchar2)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    lBlob               BLOB;
    lFile               BFILE := BFILENAME(p_directory, p_file_name);
    L_ORA_CHARSET       VARCHAR2(100);
    P_COUNT             NUMBER;

BEGIN

    SELECT  value 
    into    l_ora_charset
    FROM    nls_database_parameters
    where   parameter = 'NLS_CHARACTERSET';

    insert into FND_LOBS 
    (
        file_id
    ,   file_name
    ,   file_content_type
    ,   file_data
    ,   upload_date
    ,   expiration_date
    ,   program_name
    ,   program_tag
    ,   LANGUAGE
    ,   oracle_charset
    ,   file_format
    )
    values
    (
        fnd_lobs_s.NEXTVAL        -- FILE_ID
    ,   p_file_name               -- FILE_NAME
    ,   p_content_type            -- FILE_CONTENT_TYPE
    ,   EMPTY_BLOB()              -- FILE_DATA
    ,   sysdate                   -- UPLOAD_DATE
    ,   NULL                      -- EXPIRATION_DATE
    ,   p_program_name            -- PROGRAM_NAME
    ,   p_program_tag             -- PROGRAM_TAG
    ,   p_language                -- LANGUAGE
    ,   l_ora_charset             -- ORACLE_CHARSET
    ,   p_file_format             -- FILE_FORMAT   
    )
    RETURNING file_data INTO lBlob;

    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                          SRC_LOB  => lFile,
                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));                              
    DBMS_LOB.CLOSE(lFile);
    DBMS_LOB.CLOSE(lBlob);        

    commit;

    p_file_url := fnd_gfm.construct_download_url (fnd_web_config.gfm_agent, fnd_lobs_s.currval);

END IMPORT_TO_LOB;

Note that this is an AUTONOMOUS_TRANSACTION so it needs to be committed before returning to the calling package/block.

Hope that Helps!