DECLARE ?v_file_id NUMBER; ?url ??????VARCHAR2(500);BEGIN ?--Get the file_id of the file which you want to download in fnd_lobs ??v_file_id := xxxxxx; ?--Get The Download URL ?url := fnd_gfm.construct_download_url(fnd_web_config.gfm_agent, ???????????????????????????????????????v_file_id, ???????????????????????????????????????TRUE); ?fnd_utilities.open_url(url);END;

CREATE OR REPLACE PACKAGE cux_oracle_up_down_pkg AUTHID CURRENT_USER IS ?/******************************************** ?1、Package功能，测试通过PL/SQL Gateway（MOD PL/SQL）完成文件上传下载 ?2、本Package直接使用EBS的DAD，所以对应的Document表为APPS.fnd_lobs_document ???在非EBS环境开发，需要自己定义DAD请参考9ias_plsql.pdf和9ias.pdf，步骤如下 ???1、创建Document表，参照fnd_lobs_document和fnd_lobs_documentpart，表名自己起 ???2、配置DAD，配置文件为$IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app ???3、参照fnd_gfm，改写成自己的包，或者简单点改写本Package也行 ?3、需要把我们写的包在system administrator --> security --> Web PL/SQL里面注册一下， ???不然通过IE打开会提示用户名和密码 ?4、本测试把在“接口”fnd_lobs_document中的附件，按照EBS的做法保存到fnd_lobs， ???也可以改写代码保存到自己的表，一般没必要，我们自己的表保存File_ID即可 ?5、一个表中的BLOB等数据可以直接插入另一个表 ???********************************************/ ?/***************上传文件下载过程************* ?1、初始化access id ?select fnd_gfm.authorize(-1) from dual; ???2、准备url，下面是例子 ?SELECT fnd_web_config.trail_slash(fnd_profile.VALUE(‘APPS_WEB_AGENT‘)) || ??????‘oracle_up_down.upload_form?p_access_id=上面1的查询结果‘ ?FROM dual; ???3、用浏览器打开url即可。如果是通过Form打开url上传，那么把上面代码放入form的相应trigger ???4、查看File_ID ?SELECT fnd_web_config.trail_slash(fnd_profile.VALUE(‘APPS_WEB_AGENT‘)) || ??????‘oracle_up_down.upload_form?p_access_id=上面1的查询结果‘ ?FROM dual; ???4、把文件下载下来验证 ?SELECT fnd_web_config.trail_slash(fnd_profile.VALUE(‘APPS_WEB_AGENT‘)) || ??????‘oracle_up_down.download_file?p_file_id=上面4的查询结果‘ || chr(38) || ??????‘p_access_id=上面1的查询结果‘ ?FROM dual; ???5、可以直接在PL/SQL Developer 6以上中Select出来点击File_Data察看 ???Test脚本 ?http://hw321.huawei.com:8003/pls/scp/fnd_web.SHOWENV; ?select fnd_gfm.authorize(-1) from dual; ?select * from applsys.fnd_lob_access t where t.access_id = 354896931892; ?http://hw321.huawei.com:8003/pls/SCP/oracle_up_down.upload_form?p_access_id=354896931892; ?select * from applsys.fnd_lob_access t where t.access_id = 354896931892; ?select * from applsys.fnd_lobs_document t; ?http://hw321.huawei.com:8003/pls/SCP/oracle_up_down.download_file?p_file_id=3548970&p_access_id=354896931892; ?Select * from fnd_lobs flb where flb.file_id = 3548970; ?****************上传文件下载过程*****************/ ?g_agent ???????CONSTANT VARCHAR2(100) := fnd_web_config.trail_slash(fnd_profile.value(‘APPS_WEB_AGENT‘)); ?g_package_name CONSTANT VARCHAR2(100) := ‘oracle_up_down‘; ?g_upload_url ??CONSTANT VARCHAR2(100) := g_package_name || ‘.upload_file‘; ?g_download_url CONSTANT VARCHAR2(100) := g_package_name || ??????????????????????????????????????????‘.download_file‘; ?g_cancel_url ??CONSTANT VARCHAR2(100) := g_package_name || ‘.cancel_file‘; ?--完成上传：把在网关中的数据抓到自己的表 ?PROCEDURE upload_file(p_file_name IN VARCHAR2, p_access_id IN NUMBER); ?--显示HTML取消页面 ?PROCEDURE upload_cancel; ?--显示HTML上传页面 ?PROCEDURE upload_form(p_access_id IN NUMBER DEFAULT NULL); ?--下载文件 ?PROCEDURE download_file(p_file_id ??IN NUMBER, ?????????????????????????p_access_id IN NUMBER, ?????????????????????????p_purge ????IN VARCHAR2 DEFAULT NULL); ?PROCEDURE download_file_html(p_file_id ??IN NUMBER, ??????????????????????????????p_access_id IN NUMBER, ??????????????????????????????p_purge ????IN VARCHAR2 DEFAULT NULL);END;

CREATE OR REPLACE PACKAGE BODY cux_oracle_up_down_pkg IS ?--add ‘/‘ to a string ?FUNCTION trail_slash(p_val IN VARCHAR2) RETURN VARCHAR2 IS ???l_copy_val VARCHAR2(2000); ?BEGIN ???l_copy_val := p_val; ???WHILE (substr(l_copy_val, -1, 1) = ‘/‘) LOOP ?????l_copy_val := substr(l_copy_val, 1, length(l_copy_val) - 1); ???END LOOP; ???RETURN l_copy_val || ‘/‘; ?END; ?PROCEDURE err_msg(NAME VARCHAR2) IS ?BEGIN ???fnd_message.set_name(‘FND‘, ‘SQL_PLSQL_ERROR‘); ???fnd_message.set_token(‘ROUTINE‘, ‘FND_GFM.‘ || NAME); ???fnd_message.set_token(‘ERRNO‘, SQLCODE); ???fnd_message.set_token(‘REASON‘, SQLERRM); ?END err_msg; ?/* ?从fnd_gfm拷贝过来，原来的代码是删除整个fnd_lobs_document，不知道为何，现在改为仅删除上传的文件 ?*/ ?FUNCTION confirm_upload(access_id ??????NUMBER, ?????????????????????????file_name ??????VARCHAR2, ?????????????????????????program_name ???VARCHAR2 DEFAULT NULL, ?????????????????????????program_tag ????VARCHAR2 DEFAULT NULL, ?????????????????????????expiration_date DATE DEFAULT NULL, ?????????????????????????LANGUAGE ???????VARCHAR2 DEFAULT userenv(‘LANG‘), ?????????????????????????wakeup ?????????BOOLEAN DEFAULT FALSE) ???RETURN NUMBER IS ???fid ???????NUMBER := -1; ???fn ????????VARCHAR2(256); ???mt ????????VARCHAR2(240); ???bloblength NUMBER; -- bug 3045375, added variable to set length of blob. ?BEGIN ???IF (fnd_gfm.authenticate(confirm_upload.access_id)) THEN ?????SELECT fnd_lobs_s.nextval INTO fid FROM dual; ?????????fn := substr(confirm_upload.file_name, ??????????????????instr(confirm_upload.file_name, ‘/‘) + 1); ?????????-- bug 3045375, added select to get length of BLOB. ?????SELECT dbms_lob.getlength(blob_content), mime_type ???????INTO bloblength, mt ???????FROM fnd_lobs_document ??????WHERE NAME = confirm_upload.file_name ????????AND rownum = 1; ?????????-- bug 3045375, added if to check length of blob. ?????IF bloblength > 0 THEN ???????INSERT INTO fnd_lobs ?????????(file_id, ??????????file_name, ??????????file_content_type, ??????????file_data, ??????????upload_date, ??????????expiration_date, ??????????program_name, ??????????program_tag, ??????????LANGUAGE, ??????????file_format) ?????????(SELECT confirm_upload.fid, ?????????????????fn, ?????????????????ld.mime_type, ?????????????????ld.blob_content, ?????????????????SYSDATE, ?????????????????confirm_upload.expiration_date, ?????????????????confirm_upload.program_name, ?????????????????confirm_upload.program_tag, ?????????????????confirm_upload.language, ?????????????????fnd_gfm.set_file_format(mt) ????????????FROM fnd_lobs_document ld ???????????WHERE ld.name = confirm_upload.file_name ?????????????AND rownum = 1); ?????????????IF (SQL%ROWCOUNT <> 1) THEN ?????????RAISE no_data_found; ???????END IF; ?????????????UPDATE fnd_lob_access ??????????SET file_id = fid ????????WHERE access_id = confirm_upload.access_id; ?????????????IF wakeup THEN ?????????dbms_alert.signal(‘FND_GFM_ALERT‘ || to_char(access_id), ???????????????????????????to_char(fid)); ???????END IF; ???????-- bug 3045375, added else to return fid = -2. ?????ELSE ???????fid := -2; ?????END IF; ?????DELETE FROM fnd_lobs_document ld ??????WHERE ld.name = confirm_upload.file_name; ?????--delete from fnd_lobs_documentpart; ???END IF; ???RETURN fid; ?EXCEPTION ???WHEN OTHERS THEN ?????DELETE FROM fnd_lobs_document ld ??????WHERE ld.name = confirm_upload.file_name; ?????--delete from fnd_lobs_documentpart; ?????????err_msg(‘confirm_upload‘); ?????RAISE; ?END; ?PROCEDURE upload_file(p_file_name IN VARCHAR2, p_access_id IN NUMBER) IS ???l_file_id NUMBER; ?BEGIN ?????l_file_id := confirm_upload(access_id ???=> p_access_id, ???????????????????????????????file_name ???=> p_file_name, ???????????????????????????????program_name => g_package_name); ?????IF l_file_id NOT IN (-1, -2) THEN ?????-- File upload completed ?????htp.htmlopen; ?????htp.headopen; ?????htp.title(‘文件上传‘); ?????htp.headclose; ?????htp.bodyopen; ?????htp.img2(‘/images/wwcban.jpg‘, calign => ‘Center‘, calt => ‘Logo‘); ?????htp.br; ?????htp.br; ?????htp.p(‘<h4>‘ || ‘文件上传‘ || ‘</h4>‘); ?????htp.hr; ?????htp.p(htf.bold(‘文件上传完成。Select * from fnd_lobs flb where flb.file_id = ‘ || ????????????????????l_file_id)); ?????htp.br; ?????????htp.p(‘<h4>‘ || ‘请关闭浏览器！‘ || ‘</h4>‘); ?????htp.br; ?????htp.bodyclose; ?????htp.htmlclose; ???????ELSE ?????-- File upload failed. ?????htp.htmlopen; ?????htp.headopen; ?????htp.title(‘文件上传‘); ?????htp.headclose; ?????htp.bodyopen; ?????htp.img2(‘/images/wwcban.jpg‘, calign => ‘Left‘, calt => ‘Logo‘); ?????htp.br; ?????????htp.hr; ?????htp.p(htf.bold(‘文件上传失败！‘)); ?????htp.br; ?????htp.bodyclose; ?????htp.htmlclose; ???END IF; ???END; ?PROCEDURE upload_cancel AS ???BEGIN ?????-- Show a message page ???htp.htmlopen; ???htp.headopen; ???htp.title(‘取消文件上传‘); ???htp.headclose; ???htp.bodyopen; ???htp.img2(‘/images/wwcban.jpg‘, calign => ‘Center‘, calt => ‘Logo‘); ???htp.br; ???htp.br; ???htp.p(‘<h4>‘ || ‘文件上传‘ || ‘</h4>‘); ???htp.hr; ???htp.p(htf.bold(‘取消文件上传‘)); ???htp.br; ???htp.p(‘<h4>‘ || ‘您已经选择取消文件上传。‘ || ‘</h4>‘); ???htp.p(‘<h4>‘ || ‘请关闭浏览器！‘ || ‘</h4>‘); ???htp.br; ???htp.br; ???htp.br; ???htp.bodyclose; ???htp.htmlclose; ???END; ?PROCEDURE upload_form(p_access_id IN NUMBER DEFAULT NULL) IS ???l_upload_action VARCHAR2(2000); ?BEGIN ?????-- Set the upload action ???l_upload_action := fnd_gfm.construct_upload_url(g_agent, ???????????????????????????????????????????????????g_upload_url, ???????????????????????????????????????????????????p_access_id); ???-- Set page title and toolbar. ???htp.htmlopen; ???htp.headopen; ???htp.p(‘<SCRIPT LANGUAGE="JavaScript">‘); ???htp.p(‘ function processclick (cancel_url) { ????????????????if (confirm(‘ || ‘"‘ || ‘取消文件上传‘ || ‘"‘ || ‘)) ????????????????{ ???????????????????????parent.location=cancel_url ????????????????} ?????????????}‘); ???htp.print(‘</SCRIPT>‘); ???htp.title(‘文件上传‘); ???htp.headclose; ???htp.bodyopen; ???htp.img2(‘/images/wwcban.jpg‘, calign => ‘Center‘, calt => ‘Logo‘); ???htp.br; ???htp.br; ???htp.p(‘<h4>‘ || ‘文件上传‘ || ‘</h4>‘); ???htp.hr; ???htp.br; ???htp.print(‘</LEFT>‘); ?????htp.formopen(curl ????=> l_upload_action, ????????????????cmethod ?=> ‘POST‘, ????????????????cenctype => ‘multipart/form-data‘); ???htp.tableopen(cattributes => ‘ border=0 cellpadding=2 cellspacing=0‘); ???htp.tablerowopen; ???htp.tablerowclose; ?????htp.tablerowopen(cvalign => ‘TOP‘); ???htp.p(‘<TD>‘); ???htp.p(‘</TD>‘); ???htp.p(‘<label>文件</label>‘); ???htp.tabledata(‘<INPUT TYPE="File" NAME="p_file_name" SIZE="60">‘, ?????????????????calign => ‘left‘); ???htp.tablerowclose; ???htp.tableclose; ?????-- Send access is as a hidden value ???htp.formhidden(cname => ‘p_access_id‘, cvalue => to_char(p_access_id)); ?????htp.br; ???htp.tableopen(cattributes => ‘ border=0 cellpadding=2 cellspacing=0‘); ???htp.tablerowopen(cvalign => ‘TOP‘); ???htp.tabledata(‘<INPUT TYPE="Submit" VALUE="‘ || ‘确定‘ || ‘" SIZE="50">‘, ?????????????????calign => ‘left‘); ???htp.tabledata(‘<INPUT TYPE="Button" NAME="cancel" VALUE="‘ || ‘取消‘ || ‘"‘ || ?????????????????‘ onClick="processclick(‘‘‘ || g_cancel_url || ?????????????????‘‘‘) " SIZE="50">‘, ?????????????????calign => ‘left‘); ???htp.tablerowclose; ???htp.tableclose; ???htp.formclose; ?????htp.bodyclose; ???htp.htmlclose; ?END; ?PROCEDURE download_file(p_file_id ??IN NUMBER, ?????????????????????????p_access_id IN NUMBER, ?????????????????????????p_purge ????IN VARCHAR2 DEFAULT NULL) IS ?BEGIN ???fnd_gfm.download(p_file_id, p_access_id, p_purge); ?END; ?PROCEDURE download_file_html(p_file_id ??IN NUMBER, ??????????????????????????????p_access_id IN NUMBER, ??????????????????????????????p_purge ????IN VARCHAR2 DEFAULT NULL) IS ?BEGIN ???htp.print(‘ ???<html> ???<body> ?????<img src=‘ || g_download_url || ‘?p_file_id=‘ || ?????????????p_file_id || chr(38) || ‘p_access_id=‘ || p_access_id || ?????????????‘ border=0> ???</body> ???</html> ???‘); ?END;END;