4 Replies Latest reply: Jun 3, 2014 12:25 AM by HawkerHunter RSS

    Creating XML report using PL/SQL Stored Procedure

    HawkerHunter

      Hi Friends,

       

      I am working on an xml report with the xml source as PL/SQL Stored Procedure.

      I am referring the exercise shown in the following link to understand the process:

       

         http://orclapp.blogspot.com/2012/02/developing-xml-publisher-report-using.html

       

      In the example shown in the above link I could not understand the following:

       

      1) In the following procedure, the out parameter 'retcode' is not used at all.

         What is the importance of this parameter.

       

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

       

      2)  After the xml data is prepared and put to 'l_result' Clob variable, the following

          Loop is executed. I am not able to appreciate why the following loop is required.

       

           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;

       

       

      3) In the example it is not explained how the concurrent program gets the xml data?

         I assume it is written to a file using the following line of code:

       

          fnd_file.put_line (fnd_file.output, l_xmlstr);

        

         I would appreciate if anyone can throw some light into my above queries so that I can understand the process clearly.

      Thanks & Regards

      Hawker

        • 1. Re: Creating XML report using PL/SQL Stored Procedure
          Robert Angel

          Hi,

           

          On your Part 1, retcode is used to pass back whether the report has terminated successful, with warning or errored. (I forget the numbers, but it will be in the developers guide for e-Business suite), errbuff is used to pass any error message you need the end user to see.

           

          On part 2 fnd_file.put_line is the Oracle e-Business equivalent of dbms_output.put_line, so yes, it will write the XML in either the log or output area.

           

          Is this all you needed?

          • 2. Re: Creating XML report using PL/SQL Stored Procedure
            HawkerHunter

            Hi Robert,

            thanks for your reply and clearing point 1.

            In point 2, I have pasted a part of the code with a loop which was given in the link I have provided.

            I am not able to understand why this loop is used even after taking the entire xml data into a clob variable l_result before writing the xml to log.

            I would be thankful to you if you can throw some light on this loop part of the code.

            Regards

            Hawker

            • 3. Re: Creating XML report using PL/SQL Stored Procedure
              Robert Angel

              Hi 32000 in the code is a 'safe' size smaller than the max available 32767, the loops purpose is to move through the entire thing in chunks that will be manageable in the limits of the data type.

               

              Btw; if you are in Oracle e-business suite then you can also use Oracle reports very simply to create XML output.

               

              If you have reports developer all you need to do is put raw sql (without any 'artifice' to create XML) in the report SQL and then set the reports output to XML in the program definition in Oracle e-business.

               

               

              best regards,

               

              Robert.

               


              • 4. Re: Creating XML report using PL/SQL Stored Procedure
                HawkerHunter

                Hi Robert,

                 

                thanks for helping me to understand the code.

                Yes I know that in Oracle EBS we can generate the XML file lot more easier with the help of RDF and that's the way I have been generating all the required reports so far .

                But I wanted to learn an alternate way for doing the same.

                 

                Thanks again.

                 

                Regards

                Hawker