写PL/SQL, 按标准XML文件格式用fnd_file.put_line(fnd_file.output,)逐行输出得到XML数据源。
1. 编写GenerateXML包,包含三个Procedure,生成XML文件
create or replace package GenerateXML is
PROCEDURE output(p_content IN VARCHAR2);
PROCEDURE log_line(p_content VARCHAR2);
PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_user_name IN VARCHAR2); -- request parameter
end GenerateXML;
create or replace package body GenerateXML is
--封装utl_file.put_line()
PROCEDURE output(p_content IN VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.output, p_content);
END output;
PROCEDURE log_line(p_content VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.log, p_content);
END log_line;
--generate XML
PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_user_name IN VARCHAR2 -- request parameter
) IS
CURSOR user_cursor IS select user_name, description from fnd_user
where user_name = p_user_name; --定义游标
BEGIN
output('<?xml version="1.0" encoding="UTF-8"?>');
output('<G_XMLTEST>');
FOR temp in user_cursor LOOP --temp临时变量,隐式声明类型user_cursor%rowtype
output('<G_USER>');
output('<USER_NAME>' || temp.user_name || '</USER_NAME>');
output('<DESCRIPTION>' || temp.description || '</DESCRIPTION>');
output('</G_USER>');
END LOOP;
output('</G_XMLTEST>');
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
errbuf := '执行报错:' || SQLERRM;
log_line('执行报错:' || SQLERRM);
END main;
END GenerateXML;
2. 建立执行档
3. 建立请求
4. 定义数据源,制作RTF模板上传。(略)