Developing XML Publisher Report - using Data Source as PL/SQL Stored Procedure

Background:

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

Prerequisite for the below Example:

1. Create a table
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;

/

2. Define Executable
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