EBS开发附件上传和下载功能

来源：互联网发布：mac虚拟机打游戏编辑：程序博客网时间：2024/05/22 14:29

上传

Oracle ERP二次开发中使用的方式有两种，一是通过标准功能，在系统管理员中定义即可，不用写代码，就可以使几乎任何Form具有附件功能，具体参考系统管理员文档；二是通过PL/SQL Gateway，需要我们便写代码完成。该方式其实和上述方式一的后台实现是一样的

程序说明

1、Package功能，测试通过PL/SQLGateway（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等数据可以直接插入另一个表

下载

上传的文件时存到了fnd_lobs表中以BLOB数据的形式存储，现在要把BLOB数据以原文件的格式读取出来，并且给最终用户提供下载该文件的功能

方法如下

declarev_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 URLurl := fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,v_file_id,TRUE);fnd_utilities.open_url(url);end;

以上的方法就可以轻松的实现下载存储在fnd_lobs中的文件，只要告诉fnd_gfm.construct_download_url在fnd_lobs表中文件的file_id，就可以轻松取得URL，使用fnd_utilities.open_url就可以下载该文件；不过现在还有一个问题就是要在工作流发送的消息找到一个东西（比如说一个按钮、超链接）来执行下载文件的方法可以在document类型的ATTRIBUTE里设置一个超链接，然后让这个超链接的地址指向我已经取得的下载文件的URL

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 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 oracle_up_down 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;/

0 0