第一步： –获取数据源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;
