Oracle EBS file upload and download (I) - call standard API function

Business background

An article published before me< Oracle EBS uses CSV to import Oracle Form and BOM import API >When it comes to uploading imported data files, it can also be used for file upload and download.

Oracle EBS standard upload and download

Upload code:

/**Upload files
 Return value: ID of the uploaded file: fnd ﹣ lobs.file ﹣ ID
*/
Function UploadFile Return Number Is
  l_AccessId      Number;
  l_ServerUrl     Varchar2(20000);
  l_Url           Varchar2(20000);
  l_ButtonChoice  Number;
  l_FileId        Number;
  l_OracleCharset Varchar2(30);
Begin
  --Get Process ID
  l_AccessId := Fnd_Gfm.authorize(Null);
  --Get Web Page
  Fnd_Profile.Get('APPS_WEB_AGENT', l_ServerUrl);

  l_Url := Rtrim(l_ServerUrl, '/') ||
           '/fnd_file_upload.displayGFMform?access_id=' ||
           To_Char(l_AccessId) || Chr(38) || 'l_server_url=' || l_ServerUrl;

  If (l_Url Is Null) Then
    Fnd_Message.Set_String('call Url fail!');
    Fnd_Message.Show;
    Raise Form_Trigger_Failure;
  End If;
  --Open Web Page
  Fnd_Utilities.Open_Url(l_Url);
  Fnd_Message.Set_String('Please select the file to import when opening the web page. Click Yes to finish the upload! ');
  l_ButtonChoice := Fnd_Message.Question(button1     => 'Yes',
                                         button2     => null,
                                         button3     => 'No',
                                         default_btn => 1,
                                         cancel_btn  => 3,
                                         icon        => 'Question');
  --Get File ID
  l_FileId := Fnd_Gfm.Get_File_ID(l_AccessId);
  --Get client character set
  If l_FileId Is Not Null And l_FileId <> 0 Then
    Select oracle_charset
      Into l_OracleCharset
      From FND_LOBS
     Where FILE_ID = l_FileId;
    :Parameter.CLIENTCHARSET := Substr(:Parameter.CLIENTCHARSET,
                                       1,
                                       Instr(:Parameter.CLIENTCHARSET, '.')) ||
                                l_OracleCharset;
  End If;
  --Canceling the upload will delete the record of the uploaded file
  If (l_ButtonChoice = 3) Then
    Begin
      Delete From FND_LOBS Where FILE_ID = l_FileId;
    Exception
      When No_Data_Found Then
        Null;
    End;
    l_FileId := 0;
    Forms_Ddl('commit');
  End If;
  Return l_FileId;
End;

In fact, call the Oracle EBS standard API to upload the file. The uploaded file is saved in the field file data in the fnd ﹣ lobs table.

Download code

The reference code is as follows:

DECLARE
  l_FileId      Number;
  l_DownloadUrl Varchar2(2000);
BEGIN
  --Document identification, from FND_LOBS.FILE_ID
  l_FileId := :CONTROL.FILE_ID;
  --Get downloaded URL
  l_DownloadUrl := fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,
                                                  l_FileId,
                                                  TRUE);
  --Download File                                                
  fnd_utilities.open_url(l_DownloadUrl);
END;

The name of the file saved by the default download is: fnd_gfm.zip. You need to modify the file name and file type on your own line.

Advantages and disadvantages:

Advantages:
Calling Oracle EBS standard function to write code is simple and quick. It is more suitable to deal with bulk data import.
Disadvantages:
1. Because the documents are stored in the table of fnd ﹣ lobs in Oracle EBS standard, it is not suitable to upload a large number of single large documents, such as material pictures and technical drawings.
The download file name is fnd ﹤ gfm.zip by default, which is inconvenient for users.

Tags: Oracle

Posted on Fri, 31 Jan 2020 10:15:46 -0500 by spider