摘要
开发工具:oracle 10g form ,developer
系统版本:oracle EBS R12I
作者：胡瑞昌 时间：2011-08-02
邮箱：hrc122@163.com
第一章oracle form上传EXCEL
1.实现目标
点击form上的按钮,直接上传EXCEL到form栏位. 2.实现方式和代码
Form 中关键代码
Buttom ：CONTROL.SELECT_IMPORT_DATA('WHEN-BUTTON-PRESSED');
说明：代码(lv_fields:=)后面参数为form界面显示的栏位.并和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,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 constant 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
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 := RTRIM(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_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);
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_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_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: 'x,y,z' -> 'x', 'y', 'z')
dbms_utility.comma_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( 'Item : ' || v_block||'.'||my_field(i) || ' is not exist !!' ); raise form_trigger_failure; end if; end if; end; end loop;
if upper(v_col_separate) = '
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) = '
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_file;
if l_gfm_id > 0 then
copy( '20', 'system.message_level' );
Get File Length
select dbms_lob.getlength(file_data) into file_len from fnd_lobs
where file_id = l_gfm_id;
go_block ( v_block );
Select Data