Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure’


1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));

2. Insert Values

INSERT INTO demo_products
     VALUES (100, ‘TEST DATA’);

1. Create a Package Spec & Body with a single Procedure



   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);



   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
      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;
      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);

         EXIT WHEN l_length = l_retrieved;

         IF (l_length – l_retrieved) < 32000
            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);
            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);
         raise_application_error (-20001, ‘Error in procedure’);

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

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

RTF can be created using the XML file from the Output of the concurrent Program.

6. Run the Concurrent Program to see the output

Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure


XML Bursting- Mails to multiple mail ids

Mails can be sent to multiple users by separating the mail ids using comas.

Following is a sample Bursting file…

<?xml version=”1.0″ encoding=”UTF-8″ ?>

<xapi:requestset xmlns:xapi=”” type=”bursting“>

<xapi:request select=”/MPLNRGPR“>


<xapi:email server=”” port=”25” from=”PQR@XXX.COM” reply-to=””>

  <xapi:message id=”123” to=”,” attachment=”true” subject=”Busting Report“>FYI</xapi:message>



<xapi:document output=”NRGP” output-type=”pdf” delivery=”123“>

  <xapi:template type=”rtf” location=”xdo://JA.MPLNRGPR.en.00/?getSource=true” filter=”” />




Note :If the address has to be passed dynamically from the tags then at the rdf level concatenate the mail id with a  comma (,) at the mail id with out any space.

XML Bursting

Oracle Bursting Mechanism enables us to deliver a single XML Publisher report/letter to multiple destinations simultaneously. This is a very helpful feature especially if you are trying to send the output of a report to an email address or multiple email address.The output is sent via email. User does not need to login to Oracle to get the report.



1.SMTP configuration

2.Change the temporary directory location.

  • A temporary directory has to be provided for XML Publisher engine to use for bursting.
  • Responsibility: XML Publisher Administrator
  • Navigation: Administration tab

3. Attach Seeded Program to the Responsibility: XML Publisher Report Bursting Program

The following Bursting File needs to be attached to the Data Definition of the BI Publisher report that you are trying to burst :

<!–?xml version=”1.0″ encoding=”UTF-8″?>

<xapi:requestset xmlns:xapi=””&gt;

<xapi:request select=”/PERSON/LIST_G_FIRST_NAME”>


<xapi:email server=”” port=”25″ from=“” reply-to =“”>

<xapi:message id=”123″ to=“${EMAIL}” attachment=”true” subject=“Busting Report”>

Busting Report Body




<xapi:document output=”${EMPLOYEE_NUMBER}” output-type=”excel” delivery=”123″>

<xapi:template type=”rtf” location=”xdo://PER.XX_PERSON.en.00/?getSource=true” filter=””>




Explanation of the bursting template

XML Tags


xapi:request select= This tag in the control file indicates from which tag/node in the concurrent program output XML should separated for bursting
xapi:delivery This node contains all email related attributes
xapi:email This tag contains the email attributes like,server or the email server IP or nameport or the email server portfrom or the email address from which the server should send the emailreply-to or the email address to which the replies should be sent
xapi:message This tag contains the attributes about the email like,To or the email address where the email should be sentSubject or the email subjectattachment , i.e. if the email should contain an attachmentThe mail body will be enclosed within the tags
xapi:document This node contains the attachment document and XML template related tags and attributes.The attributes in this tag are related to the email attachmentOutput or attachment file nameOutput-type or attachment file type
xapi:template The node contains the template information like,Type or the file type of the template

Attach the Bursting File to Data Definition
Responsibility: XML Publisher Administrator
Navigation: Data Definition


The bursting process is now complete.
Automatic execution of XML Publisher Report Bursting Program
Since the bursting program, XML Publisher Report Bursting Program, has to be run manually this might not be feasible for all users to use. The bursting program can be executed automatically if we use a rdf report to generate the data for the XML publisher report. In a rdf report we can add the following piece of code in the After Report trigger in the report to execute the bursting program as soon as the output has been generated by the report.

function AfterReport return boolean is

req_id NUMBER;

err varchar2(4000);




req_id := fnd_request.submit_request (‘XDO’, — application

‘XDOBURSTREP’,– program short name

”, — description

”, — start time

FALSE, — sub request

‘N’, — argument1

: P_CONC_REQUEST_ID, — argument2

‘N’, — argument3

NULL, — argument4

NULL, — argument5

NULL, — argument6

CHR (0) — represents end of arguments

) ;

exception when others then

err:= sqlerrm;

srw.message(100,’exception ‘ || err);

end ;

if req_id = 0 then

srw.message(101,’failed to submit bursting program ‘);


srw.message(102,’request id for bursting program is ‘||req_id);

end if;

return (TRUE);