欢迎使用CSDN-markdown编辑器

第一步：
–获取数据源sql
FOR r_col IN c_cols(p_table)
LOOP
IF l_sql <> ‘SELECT t.header_id, ’ THEN
l_sql := l_sql || ’ || ‘;
END IF;
IF r_col.data_type = ‘TEXT’ THEN
l_sql1 := ‘XMLElement(“’ || r_col.column_name || ‘”,’ || chr(10) ||’XMLAttributes(”’ || r_col.data_type || ”’ AS “TYPE”),’ ||chr(10) || ‘Xmlcdata(t.’ || r_col.column_name ||’)).getStringVal() || chr(10)’;
ELSE
l_sql1 := ‘XMLElement(“’ || r_col.column_name || ‘”,’ || chr(10) ||’XMLAttributes(”’ || r_col.data_type || ”’ AS “TYPE”),’ ||
chr(10) || ‘t.’ || r_col.column_name ||’).getStringVal() || chr(10)’;
END IF;
l_sql := l_sql || chr(10) || l_sql1;
END LOOP;
第二步：
–将数据源sql用xml标签包装后存入clob类型的变量
l_xml := to_clob(l_str); –转化成clob类型
dbms_lob.createtemporary(l_clob, TRUE); –创建临时的clob变量
dbms_lob.append(l_clob, l_xml); –将l_xml中的数据复制到l_clob
dbms_xslprocessor.clob2file(l_clob, p_loc, l_filename);
–文件格式在l_filename已指定 l_filename = xxxxx.xml

第三步：
–创建文件夹，对导出的xml文件进行归档。
mkdirs(l_path);
copy_file(r.from_file, l_path || ‘/’ || r.file_name);
cux_zip_utl.zip(r.full_path, l_path);–压缩文件

– 将压缩包传输到FTP中间机对应目录
ftp.put(p_conn => l_conn,
p_from_dir => g_local.export_dir,
p_from_file => l_zip_file_name,
p_to_file => g_ftp.dest_path || p_module_code || ‘/’ || l_zip_file_name);
–获取压缩包的名称并在oaf界面生成下载列表
–如果一天多次导出会生成带括号序号的文件
–例如20170628,20170628（1），20170628（2）等等
FUNCTION get_zip_full_name(p_folder_name VARCHAR2) RETURN VARCHAR2 IS
i NUMBER := 1;
l_path VARCHAR2(500) := g_local.export_path || p_folder_name;
BEGIN
IF cux_file_utl.is_exists(l_path || ‘.zip’) = FALSE THEN
RETURN l_path || ‘.zip’;
END IF;
LOOP
IF cux_file_utl.is_exists(l_path || ‘(’ || i || ‘).zip’) = FALSE THEN
RETURN l_path || ‘(’ || i || ‘).zip’;
END IF;
i := i + 1;
END LOOP;
END get_zip_full_name;

第一步： – 获取FTP中间机上传路径中所有文件信息
ftp.list(p_conn => l_conn,
p_dir => g_ftp.src_path,
p_list => l_files);
第二步：
– 将文件获取到数据库服务器
ftp.get(p_conn => l_conn,
p_from_file => g_ftp.src_path || l_name,
p_to_dir => g_local.import_dir,
p_to_file => l_name);

    -- 删除FTP中间机相应文件    ftp.delete(p_conn => l_conn,               p_file => g_ftp.src_path || l_name);

第三步：
–保存xml文件到数据库中
dbms_lob.fileopen(l_targetfile, dbms_lob.file_readonly);–打开xml文件

–将xml文件的数据源以clob类型保存到数据库
dbms_lob.loadclobfromfile(l_charcontent,
l_targetfile,
dbms_lob.getlength(l_targetfile),
l_src_offset,
l_dst_offset,
l_charset_id,
l_lang_ctx,
l_warning);
–关闭xml文件
dbms_lob.fileclose(l_targetfile);
第四步：
–将xml中的数据插入到接口表中
–拼接动态执行的sql语句
l_sequence := SUBSTR(UPPER(p_table_name),
1,
INSTR(UPPER(p_table_name), ‘_V’, -1, 1)) || ‘S’;
l_sql := ‘INSERT INTO ’ || p_table_name || ’ SELECT ‘;
FOR cr IN cr_tab
LOOP
IF cr.column_name = ‘LIST_ID’ THEN
l_sql := l_sql || chr(10) || p_list_id || ‘,’;
ELSIF cr.column_name = ‘INTERFACE_ID’ THEN
l_sql := l_sql || chr(10) || l_sequence || ‘.nextval,’;
ELSIF cr.data_type = ‘DATE’ THEN
l_sql := l_sql || chr(10) ||
‘FND_CONC_DATE.STRING_TO_DATE(extractValue(x.column_value, ”/’ ||
p_second_tag || ‘/’ || cr.column_name || ”’)) as ’ ||
cr.column_name || ‘,’;
ELSE
l_sql := l_sql || chr(10) || ‘extractValue(x.column_value, ”/’ ||p_second_tag || ‘/’ || cr.column_name || ”’) as ’ ||cr.column_name || ‘,’;
END IF;
END LOOP;
l_sql := rtrim(l_sql, ‘,’) ||’ FROM cux.CUX_CATIC_IMPORT_LIST_ALL s
, TABLE(XMLSequence(extract(XMLTYPE(s.IMPORT_FILE), ”/’ ||
p_first_tag || ‘/’ || p_second_tag || ”’))) x
WHERE s.LIST_ID = ’ || p_list_id;