Question: Generate the URL of a Remote File using PL/SQL

Question

Generate the URL of a Remote File using PL/SQL

Answers 1
Added at 2016-12-30 10:12
Tags
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
Answers
nr: #1 dodano: 2017-01-02 13:01

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;
Source Show
◀ Wstecz