在EBS中传统开发报表，我们使用Reports Developer10G来生成XML，然后利用Oracle XML Publisher Desktop 来设计模板，这样的好处是XML取数（SQL）和生成非常方便，我们不用担心。另一种开发方式是利用PLSQL生成XML，然后利用Oracle XML Publisher Desktop 来设计模板。还有一种方式是利用其它程序语言生成XML（PSLQL\JAVA等），利用XML Publisher API来读取XML数据流关联到模板。这种方式比较快速的打开，因为它不走EBS中的并发管理器。
这里我们看一下PLSQL中生成XML的一些问题。直接上图
之前描述错误，这里可以看到利用FND_FILE.PUT_LINE,针对中文的情况 ，请使用
OUT_PUT('<?xml version="1.0" ?>'); 不要使用-- <?xml version = ''1.0'' encoding = ''GBK''?>，而在Reports 工具中却要这么做。
更加方便请使用ORACLE中内置XML引擎来生成。如下，或者定义XMLTYPE来组合。
关于使用的示例链接：http://www.orafaq.com/wiki/DBMS_XMLGEN
更加详细的使用步骤：
Developing sample XML Publisher Report with Executable Method as 'PL/SQL Stored Procedure'
In my previous post http://orclapp.blogspot.in/2011/11/using-data-template.html i have explained developing XML Publisher Report using Data Template as Data Source
CREATE TABLE demo_products
( product_code NUMBER,
product_name VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
VALUES (569, 'Oracle Cost Management');
3. Issue Commit
1. Create a Package Spec & Body with a single Procedure
Spec:
CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER);
END MY_PACKAGE;
/
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER)
IS
l_qryCtx DBMS_XMLGEN.ctxHandle;
l_query VARCHAR2 (32000);
l_length NUMBER (10);
l_xmlstr VARCHAR2 (32000);
l_offset NUMBER (10) := 32000;
l_retrieved NUMBER (10) := 0;
l_result CLOB;
l_no_rows NUMBER;
BEGIN
l_query := 'SELECT product_code, product_name
FROM demo_products
WHERE product_code = ' || p_product_id;
l_qryCtx := DBMS_XMLGEN.newContext (l_query);
-- set rowset tag to PRODUCTS and row tag to PRO_DETAILS
DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');
-- now get the result
l_result := DBMS_XMLGEN.getXML (l_qryCtx);
l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);
l_length := NVL (DBMS_LOB.getlength (l_result), 0);
FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);
LOOP
EXIT WHEN l_length = l_retrieved;
IF (l_length - l_retrieved) < 32000
THEN
SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;
l_retrieved := l_length;
fnd_file.put_line (fnd_file.output, l_xmlstr);
ELSE
SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
INTO l_xmlstr
FROM DUAL;
l_retrieved := l_retrieved + l_offset;
fnd_file.put_line (fnd_file.output, l_xmlstr);
END IF;
END LOOP;
DBMS_XMLGEN.closeContext (l_qryCtx);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
raise_application_error (-20001, 'Error in procedure MY_PACKAGE.report');
END REPORT;
END MY_PACKAGE;
Navigation: Application Developer > Concurrent > Executable
Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT
3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program
Provide Program, Short name, Application, Description & also
- Executable Name as defined in the above step
- Output Format should be XML
- Define a Parameter p_product_id
- Associate Concurrent Program to the Request Group.
4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.
5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template
6. Run the Concurrent Program to see the output
Note:
If you need any extra help from Step2 refer my previous post http://orclapp.blogspot.in/2011/11/using-data-template.html
select dbms_xmlgen.getxml('select t.* from dba_users t where t.user_id<20 ') from dual;
select dbms_xmlgen.getxml('
2 select department_id, department_name,
3 cursor(select first_name, last_name
4 from employees e
5 where e.department_id = d.department_id) emp_row
6 from departments d
7 where rownum < 4
8* ') from dualOPEN CUR_INVOICE FOR
SELECT
PH.REQ_NUMBER,
PH.NAME,
PH.VENDOR_NAME,
PH.LAST_NAME,
PH.BANK_NAME,
PH.DEP,
PH.BANK_ACCOUNT_NUM,
DECODE(PH.AMOUNT_IN,0,'0',TO_CHAR(PH.AMOUNT_IN,'FM999,999,999,999,999.00')) AMOUNT_IN,
TO_CHAR(PH.CREATION_DATE,'YYYY-MM-DD') CRATE_DATE,
DECODE(PH.REQ_AMOUNT,0,'0',TO_CHAR(PH.REQ_AMOUNT,'FM999,999,999,999,999.00')) REQ_AMOUNT,
PH.MEANING1,
PH.MEANING2,
PH.MEANING3,
PH.DESCRIPTION,
PH.NAME "NAME1",
TO_CHAR(SYSDATE,'YYYY-MM-DD') "PRINT_DATE",
PH.INVOICE_CURRENCY_CODE,
Cux_Mea_Reports.AMOUNT_CONVERT(PH.AMOUNT_IN) AMOUNT_IN_F,
Cux_Mea_Reports.AMOUNT_CONVERT(PH.REQ_AMOUNT) REQ_AMOUNT_F
FROM CUX_PAYREQ_HEADERS_V PH
WHERE PH.DOC_TYPE_ID IN (0, 1)
AND PH.PAYREQ_HEAD_ID = P_ITEM_ID ;
--定义XML CLOB文件
L_QRYCTX := DBMS_XMLGEN.newContext (CUR_INVOICE);
--设置XML文件标签
DBMS_XMLGEN.setRowSetTag (L_QRYCTX, 'CUX_INVOICE');
DBMS_XMLGEN.setRowTag (L_QRYCTX, 'LIST_G_BANK_NAME');
--返回CLOB数据
L_RESULT := DBMS_XMLGEN.getXML (L_QRYCTX);
--判断是否有行
L_NO_ROWS:= DBMS_XMLGEN.getNumRowsProcessed(L_QRYCTX);
IF L_NO_ROWS=0 THEN GOTO NEXT_LABEL;END IF;
L_XMLSTR:=DBMS_XMLGEN.CONVERT(L_RESULT, 1);
OUT_PUT(L_XMLSTR);
<<NEXT_LABEL>>
DBMS_XMLGEN.closeContext (L_QRYCTX); */