oracle form上传excel或图片及XML Publisher实现动态打印图片

1、oracle form上传excel或图片及xml publisher实现动态打印图片 摘要 开发工具:oracle 10g form ,developer 系统版本:oracle ebs r12i 作者：胡瑞昌 时间：2011-08-02 邮箱： 第一章oracle form上传excel 1.实现目标 点击form上的按钮,直接上传excel到form栏位. 2.实现方式和代码 form 中关键代码 buttom ：control.select_import_data(when-button-pressed); 说明：代码(lv_fields:=)后面参数为for

2、m界面显示的栏位.并和cvs文件栏位顺序对应. 注意本地系统字符集和服务器系统字符集转换. skip_rows constant number := 1; 的作用是删除excel第一行题头 package body control is procedure select_import_data(p_event in varchar2) is lv_fields varchar2(4000); begin if p_event = when-button-pressed then lv_fields:= quotation_type,item_number,saleman,sale_group,

3、sale_phone,sale_mail,sale_fax; knd_import_pkg.import_file(v_block = po_quotation_temp ,v_field = lv_fields ,v_col_separate = , ,v_row_separate =); end if; end select_import_data; end; package knd_import_pkg is -字符集转换 to_charset constant varchar2(120) := american_the netherlands.utf8; from_charset co

4、nstant varchar2(120) := simplified chinese_china.zhs16gbk; skip_rows constant number := 1; -打开文件导入界面 function open_file return number; -导入文件并将文件展示在forms界面 procedure import_file(v_block varchar2, v_field varchar2, v_col_separate varchar2, v_row_separate varchar2); end; package body knd_import_pkg is

5、function open_file return number is access_id number; l_server_url varchar2(20000); l_url varchar2(20000); l_gfm_id number; button_choice number; begin get process id access_id := fnd_gfm.authorize(null); get web server address fnd_profile.get(apps_web_agent,l_server_url); call web page l_url := rtr

6、im(l_server_url, /) | /fnd_file_upload.displaygfmform?access_id= | to_char(access_id) | chr(38) | l_server_url= | l_server_url; if (l_url is null) then raise form_trigger_failure; end if; open web page fnd_utilities.open_url(l_url); this action is waiting for user select file, and check upgrade -fnd

7、_message.set_name(fnd,atchmt-file-upload-complete); fnd_message.set_string(请在打开的web页面上选择上传的文件，成功上传后点击“是”确认！); button_choice := fnd_message.question( button1 = yes, button2 = null, button3 = no, default_btn = 1, cancel_btn = 3, icon = question); get file id l_gfm_id := fnd_gfm.get_file_id(access_id);

8、 if l_gfm_id is not null and l_gfm_id 0 then if button_choice = 1 then return l_gfm_id; - file is selected and upgrade else return 0; - file is selected but not upgrade end if; else return -1; - file is not selected end if; end open_file; - procedure import_file(v_block varchar2, v_field varchar2, v

9、_col_separate varchar2, v_row_separate varchar2) is l_gfm_id number; v_file varchar2(20000); v_row_sep varchar2(1); v_col_sep varchar2(1); v_comma_exist boolean := false; my_field dbms_utility.uncl_array; my_field_cnt binary_integer; my_field_loc binary_integer := 1; i number; file_len number; file_

10、loc number; - file location temp_data varchar2(20000); temp_tab varchar2(20000); enter_loc number; - enter_key location on temp buffer tab_loc number; - tab_key location on temp buffer tab_loc_old number; - tab_key location on temp buffer ln_line_number number; begin sperate v_field to my_field( ex:

11、 x,y,z - x, y, z) dbms_ma_to_table( v_field, my_field_cnt, my_field ); for i in 1 . my_field_cnt loop check item is exist ? declare item_id item; begin 沒有略過資料 if my_field(i) xxx then item_id := find_item( v_block|.|ltrim(rtrim(my_field(i) ); if id_null(item_id) then fnd_message.debug( ite

12、m : | v_block|.|my_field(i) | is not exist ! ); raise form_trigger_failure; end if; end if; end; end loop; if upper(v_col_separate) = then v_col_sep := chr(09); elsif upper(v_col_separate) null then v_col_sep := v_col_separate; else v_col_sep := chr(0); end if; get row separate char if upper(v_row_separate) = then v_row_sep := chr(13); elsif upper(v_row_separate) null then v_row_sep := v_row_separate; else v_row_sep := chr(0); end if; - open file -打开文件 l_gfm_id := open_f