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.