First create a table and insert values into that table and press on Commit.
Step 1. Create a table
CREATE TABLE demo_products
( product_code NUMBER,
product_name VARCHAR2 (100));
Step 2. Insert Values
INSERT INTO demo_products
VALUES (100, ‘TEST DATA’);
Step 3.Commit
Create a PL/SQL Package
-- Package Specification
Step 1. Create a Package Spec & Body with a single Procedure
CREATE OR REPLACE PACKAGE APPS.XX_REPORT_PKG
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER);
END XX_REPORT_PKG;
-- Package Body:
CREATE OR REPLACE PACKAGE BODY APPS.XX_REPORT_PKG
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 XX_REPORT_PKG.report’);
END REPORT;
END XX_REPORT_PKG;
Step 2. Define Executable
Navigation: Application Developer > Concurrent > Executable
Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: XX_REPORT_PKG.REPORT
Step 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.
Step 4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.
Step 5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template
RTF can be created using the XML file from the Output of the concurrent Program.
Step 6. Run the Concurrent Program to see the output
Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure‘
