file_loc := 1; enter_loc := 1;
--开始进行填充一行数据 ln_line_number := 0; Loop
ln_line_number := ln_line_number+1;
--fnd_message.debug('my_field_loc:'||my_field_loc); Find Enter Key Location
select dbms_lob.instr(file_data,utl_raw.cast_to_raw(v_row_sep),file_loc,1)
into enter_loc from fnd_lobs
where file_id = l_gfm_id;
if enter_loc > 0 then
select
trim(utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(file_data,enter_loc-file_loc,file_loc),to_charset,from_charset)))
--utl_raw.cast_to_varchar2/dbms_lob.substr只能处理2000个字符的varchar2 into temp_data from fnd_lobs
where file_id = l_gfm_id;
file_loc := enter_loc + 2; enter_loc := enter_loc + 2; else
select
trim(utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(file_data,1000,file_loc),to_charset,from_charset)))
into temp_data from fnd_lobs
where file_id = l_gfm_id;
file_loc := file_len + 2; enter_loc := file_len + 2; end if;
IF LENGTH(LTRIM(TEMP_DATA)) > 2 THEN ---------- 避免有一整列的空白行
tab_loc := 1; tab_loc_old := 1; my_field_loc := 1;
WHILE (my_field_loc <= my_field_cnt and tab_loc > 0) Loop
tab_loc := instr(temp_data, v_col_sep, tab_loc_old);
if tab_loc > 0 then
temp_tab := substr(temp_data, tab_loc_old, tab_loc - tab_loc_old); else
temp_tab := substr(temp_data, tab_loc_old); end if;
tab_loc_old := tab_loc + 1;
沒有略過資料
if ln_line_number > skip_rows then --略过第一行
if my_field(my_field_loc) <> 'xxx' then
copy( temp_tab, v_block || '.' || my_field(my_field_loc) ); end if; end if;
my_field_loc := my_field_loc + 1; END Loop; END IF;
--导入数据验证
validate(RECORD_SCOPE); IF NOT form_success THEN
fnd_message.set_string('本行数据导入错误,请检查数据格式后重新导入!'); fnd_message.show;
RAISE form_trigger_failure; END IF; --end add
Exit When file_loc >= file_len;
Next_Record;
End Loop; first_record; end if;
----------------
Delete temp data if l_gfm_id >= 0 then
update fnd_lobs
set file_data = empty_blob() where file_id = l_gfm_id;
delete from fnd_lobs where file_id = l_gfm_id;
--commit;
copy( '0', 'system.message_level' ); end if; end import_file;
END knd_import_pkg;
第二章 oracle form 上传图片
1.新建表cux_logo 其中一个栏位IMAGE_BLOB类型为BLOB
2.修改BLOCK cux_logo item IMAGE_BLOB 的类型为 image（图像） 3.添加包knd_import_pkg(第一章内容) 4.新建一个按钮(上传照片) 代码如下： DECLARE
l_gfm_id NUMBER; a_blob BLOB; BEGIN
commit_form;
do_key('execute_query');
l_gfm_id := knd_import_pkg.open_file;
SELECT file_data INTO a_blob FROM fnd_lobs fl WHERE fl.file_id = l_gfm_id;
UPDATE cux_logo SET IMAGE_BLOB = a_blob WHERE IMAGE_ID = :parameter.org_id; forms_ddl('COMMIT'); go_block('detail');
DO_KEY('execute_query'); EXCEPTION
WHEN OTHERS THEN
message(SQLERRM); END;
第三章 Oracle XML Publisher实现动态打印图片
1. 目标，动态打印公司logo标志,图片大小可以超出10K.（承接第二章） 2.实现方式
a.转换表类型BLOB 为CLOB /*图片转码*/
function cux_getlogo(p_ou_id number) return CLOB is l_result CLOB; begin
select cux_getbase64string(cl.IMAGE_BLOB) into l_result from cux_logo cl
where cl.IMAGE_ID=p_ou_id; RETURN l_result; END;
b.主程序打印报表
Procedure is l_logo CLOB; l_org_id NUMBER; l_length NUMBER; BEGIN
Fnd_File.Put_Line(Fnd_File.Output,
'');
Fnd_File.Put_Line(Fnd_File.Output, '
Fnd_File.Put_Line(Fnd_File.Output, '
取主组织org_id及LOGO
BEGIN
SELECT ood.OPERATING_UNIT
INTO l_org_id
FROM org_organization_definitions ood
WHERE ood.ORGANIZATION_ID = c1.organization_id AND rownum = 1;
l_logo := cux_common_pkg.cux_getlogo(l_org_id); --看大小
l_length := length(l_logo); --循环put EXCEPTION
WHEN OTHERS THEN
l_logo := NULL; END;
Fnd_File.Put_Line(Fnd_File.Output, '
|| fnd_file.Put(fnd_file.OUTPUT, substr(l_logo, 255 * i - 254, 255)); Fnd_File.Put_Line(Fnd_File.Output, '');
Fnd_File.Put_Line(Fnd_File.Output, ''); Fnd_File.Put_Line(Fnd_File.Output, ''); END;
C.XML Publisher 添加输出LOGO栏位:LOG
3.上传运行即可.