Oracle XML Report - Using Data Source as PL/SQL Stored Procedure

1. Create a table
CREATE TABLE XXAA_PLSQL_XMLP_REPORT

( product_code NUMBER,

  product_name VARCHAR2 (100)

);

2. Insert Values
INSERT INTO XXAA_PLSQL_XMLP_REPORT VALUES (569, 'Oracle Cost Management');

3. Issue COMMIT;

4. Create a Package Spec & Body with a single Procedure
--> Spec:

CREATE OR REPLACE PACKAGE APPS.XXAA_PLSQL_XMLP_REPORT

AS

PROCEDURE REPORT

     (errbuf OUT VARCHAR2,

     retcode OUT VARCHAR2,

     p_product_id IN NUMBER

     );

END XXAA_PLSQL_XMLP_REPORT;

/

--> Body:

CREATE OR REPLACE PACKAGE BODY APPS.XXAA_PLSQL_XMLP_REPORT

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 XXAA_PLSQL_XMLP_REPORT

                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 XXAA_PLSQL_XMLP_REPORT.REPORT');

END REPORT;

END XXAA_PLSQL_XMLP_REPORT;

/