Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

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
Share this article :

0 comments:

Post a Comment