XML/BI Publisher report based on PL/SQL Stored Procedure

Package Specification:

CREATE OR REPLACE PACKAGE xx_plsql_xml_pkg

AS

   PROCEDURE xxjs_main_proc (x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER);

END xx_plsql_xml_pkg;

/

Package Body:

CREATE OR REPLACE PACKAGE BODY xx_plsql_xml_pkg

AS

   PROCEDURE xx_main_proc (x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER)

   IS

      l_qryctx    DBMS_XMLGEN.ctxhandle;

      l_result    CLOB;

      l_no_rows   NUMBER;

   BEGIN

      fnd_file.put_line

                   (fnd_file.LOG,

                    'XML Generating Started ................................'

                   );

      l_qryctx := DBMS_XMLGEN.newcontext ('select * from emp');

                                                    ----Define your query here

      LOOP

         -- save the XML into the CLOB field

         l_result := DBMS_XMLGEN.getxml (l_qryctx);

         EXIT WHEN DBMS_XMLGEN.getnumrowsprocessed (l_qryctx) = 0;

         -- store the XML as an output

         fnd_file.put_line (fnd_file.output, l_result);

      END LOOP;

      DBMS_XMLGEN.closecontext (l_qryctx);

      fnd_file.put_line

                      (fnd_file.LOG,

                       'XML Generating End ..................................'

                      );

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Errors: ' || SQLERRM);

   END xx_main_proc;

END xx_plsql_xml_pkg;

/

       Create “Executable” for the report (XXPLSXMLREP)

Navigation: System Administrator or Application Development > Concurrent > Executable

Executable: XXPLSXMLREP

Short Name: XXPLSXMLREP

Application: XX Custom Application

Description: My Custom Report Using PL/SQL & BI Publisher

Executable Method: PL/SQL Stored Procedure

Executable File Name: XX_PLSQL_XML_PKG.XX_MAIN_PROC

         Create “Concurrent Program” and attach this executable to this concurrent program and define parameters if required

Navigation: System Administrator or Application Development > Concurrent > Program

Program: My Custom Report Using PL/SQL & BI Publisher

Short Name: XXJSPLSXMLREP

Application: XXJS Custom Application

Description: My Custom Report Using PL/SQL

Executable Name: XXJSPLSXMLREP

Output Format: XML

Create “Request Group” or Attach Concurrent Program to existing Request Group