二次开发中附件的使用实例.docx

Oralce ERP二次开发中附件的使用实例Oracle ERP二次开发中使用的方式有两种，一是通过标准功能，在系统管理员中定义即可，不用写代码，就可以使几乎任何Form具有附件功能，具体参考系统管理员文档，或者ucolor=#0000ffurl=/viewthread.php?tid=36637/viewthread.php?tid=36637/url/color/u二是通过PL/SQL Gateway，需要我们便写代码完成。该方式其实和上述方式一的后台实现是一样的。我写了个Package，大体可以演示改写的代码。简单说明如下： 1、Package功能，测试通过PL/SQL Gateway（MOD PL/SQL）完成文件上传下载2、本Package直接使用EBS的DAD，所以对应的Document表为APPS.fnd_lobs_document 在非EBS环境开发，需要自己定义DAD请参考9ias_plsql.pdf和9ias.pdf，步骤如下 a、创建Document表，参照fnd_lobs_document和fnd_lobs_documentpart，表名自己起 b、配置DAD，配置文件为$IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app c、参照fnd_gfm，改写成自己的包，或者简单点改写本Package也行3、需要把我们写的包在system administrator - security - Web PL/SQL里面注册一下， 不然通过IE打开会提示用户名和密码4、本测试把在“接口”fnd_lobs_document中的附件，按照EBS的做法保存到fnd_lobs， 也可以改写代码保存到自己的表，一般没必要，我们自己的表保存File_ID即可5、一个表中的BLOB等数据可以直接插入另一个表code CREATE OR REPLACE PACKAGE oracle_up_down 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 idselect 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的相应trigger4、查看File_IDSELECT 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脚本: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;: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;: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 oracle_up_down IS-add / to a stringFUNCTION 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) ISBEGIN 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_gram_name, confirm_gram_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( | 文件上传 | ); htp.hr; htp.p(htf.bold(文件上传完成。Select * from fnd_lobs flb where flb.file_id = | l_file_id); htp.br; htp.p( | 请关闭浏览器！ | ); 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 ASBEGIN - 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( | 文件上传 | ); htp.hr; htp.p(htf.bold(取消文件上传); htp.br; htp.p( | 您已经选择取消文件上传。 | ); htp.p( | 请关闭浏览器！ | ); 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(); htp.p( function processclick (cancel_url) if (confirm( | | 取消文件上传 | | ) parent.location=cancel_url ); htp.print(); htp.title(文件上传); htp.headclose; htp.bodyopen; htp.img2(/images/wwcban.jpg, calign = Center, calt = Logo); htp.br; htp.br; htp.p( | 文件上传 | ); htp.hr; htp.br; htp.print(); htp.formopen(curl = l_upload_action, cmethod= POST, cenctype = multipart/form-data); htp.tableopen(cattributes = border=0