Example on DBMS_XMLGEN in oracle apps using plsql procedure

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‘