Here is my Code:
这是我的代码：
I need to upload a file from the local machine to the file system Also, i got an error when opening the file attached in the oracle application (ORA-01403: no data found
我需要将文件从本地机器上传到文件系统另外，打开oracle应用程序中附加的文件时出错（ORA-01403：未找到数据）
FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403.)
FRM-40735：WHEN-BUTTON-PRESSED触发器引发未处理的异常ORA-01403。）
CREATE OR REPLACE DIRECTORY in_file_loc AS '/oracle/TEST';
CREATE TABLE test_files (p_id NUMBER,
pl_name VARCHAR2(100),
pl_pict BLOB);
DECLARE
x_blob BLOB;
fils BFILE := BFILENAME ('IN_FILE_LOC', 'aa.pdf');
blob_length INTEGER;
BEGIN
-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);
-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.
INSERT INTO test_files (p_id,pl_name,pl_pict)
VALUES (7, 'aa.pdf', EMPTY_BLOB ())
RETURNING pl_pict
INTO x_blob;
-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);
COMMIT;
-- Confirm insert by querying the database
-- for LOB length information and output results
blob_length := 0;
SELECT DBMS_LOB.getlength (pl_pict)
INTO blob_length
FROM test_files
WHERE pl_name = 'aa.pdf';
DBMS_OUTPUT.put_line ('Successfully inserted BLOB ''' ||'aa.pdf' || ''' of size ' || blob_length || ' bytes.');
END;
DECLARE
TYPE result_set_type IS REF CURSOR;
l_name VARCHAR2 (100);
l_doc_size NUMBER;
l_result_set_curr result_set_type;
x_access_id NUMBER;
x_file_id NUMBER;
p_file_name VARCHAR2 (100) := 'aa.pdf';
PROCEDURE load_file_details (p_name IN VARCHAR2, result_set_curr OUT result_set_type)
AS
l_error VARCHAR2 (2000);
BEGIN
INSERT INTO fnd_lobs_document
(NAME, mime_type, doc_size, content_type, blob_content)
SELECT pl_name, 'application/pdf', DBMS_LOB.getlength (pl_pict), 'BINARY', '(BLOB)'
FROM test_files
WHERE pl_name = 'aa.pdf';
OPEN result_set_curr FOR
SELECT blob_content
FROM fnd_lobs_document
WHERE NAME = p_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
l_error := 'LOAD_FILE_DETAILS - OTHERS' || SUBSTR (SQLERRM, 2000);
DBMS_OUTPUT.put_line (l_error);
END load_file_details;
PROCEDURE upload_file (v_filename IN VARCHAR2, x_access_id OUT NUMBER, x_file_id OUT NUMBER)
AS
v_access_id NUMBER;
v_file_id NUMBER;
x_errbuf VARCHAR2 (200);
BEGIN
v_access_id := fnd_gfm.authorize (NULL);
x_access_id := v_access_id;
DBMS_OUTPUT.put_line ('Access id :' || v_access_id);
-- The function fnd_gfm.confirm_upload return the file id
v_file_id :=
fnd_gfm.confirm_upload (access_id => v_access_id
, file_name => v_filename
, program_name => 'TEST'
, program_tag => 'TEST'
, expiration_date => NULL
, LANGUAGE => 'US'
, wakeup => TRUE
);
x_file_id := v_file_id;
DBMS_OUTPUT.put_line ('File id :' || x_file_id);
EXCEPTION
WHEN OTHERS
THEN
x_errbuf := 'Procedure upload_file errored out with the following error : ' || SQLERRM;
DBMS_OUTPUT.put_line (x_errbuf);
END upload_file;
BEGIN
fnd_global.apps_initialize (0, 20634, 401);
load_file_details (p_name => p_file_name, result_set_curr => l_result_set_curr);
upload_file (v_filename => p_file_name, x_access_id => x_access_id, x_file_id => x_file_id);
COMMIT;
END;
DECLARE
TYPE result_set_type IS REF CURSOR;
l_name VARCHAR2 (100);
l_doc_size NUMBER;
l_result_set_curr result_set_type;
x_access_id NUMBER;
x_file_id NUMBER;
p_file_name VARCHAR2 (100) := 'aa.pdf';
PROCEDURE attach_file (p_access_id IN NUMBER, p_file_Id in number, p_filename IN VARCHAR2)
IS
BEGIN
fnd_webattch.add_attachment (seq_num => 900
,category_id => 1
,document_description => 'Test1'
,datatype_id => 5
,text => NULL
,file_name => p_filename
,url => NULL
,function_name => 'INVIDITM'
,entity_name => 'MTL_SYSTEM_ITEMS'
,pk1_value => 85 --organization_id
,pk2_value => 10 --Inventory_item_Id
,pk3_value => NULL
,pk4_value => NULL
,pk5_value => NULL
,media_id => p_file_id
,user_id => 0
,usage_type => 'O'
);
DBMS_OUTPUT.put_line ('File Attached!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error in loading the attachement');
END attach_file;
BEGIN
fnd_global.apps_initialize (0, 20634, 401);
attach_file (p_access_id => x_access_id, p_file_id=>x_file_id,p_filename => p_file_name);
COMMIT;
END;
1 个解决方案
#1
0
I believe we are mixing context here. I assume Local machine here means the machine that you build the PL/SQL - only issue here is when you run it runs within the server context. So i would either FTP the file to the server or load your file content to an oracle table in a different way.
我相信我们在这里混合背景。我假设本地机器在这里意味着您构建PL / SQL的机器 - 这里唯一的问题是当您运行它在服务器上下文中运行时。所以我要将文件FTP到服务器或以不同的方式将文件内容加载到oracle表。
#1
0
I believe we are mixing context here. I assume Local machine here means the machine that you build the PL/SQL - only issue here is when you run it runs within the server context. So i would either FTP the file to the server or load your file content to an oracle table in a different way.
我相信我们在这里混合背景。我假设本地机器在这里意味着您构建PL / SQL的机器 - 这里唯一的问题是当您运行它在服务器上下文中运行时。所以我要将文件FTP到服务器或以不同的方式将文件内容加载到oracle表。