5 Replies Latest reply: Sep 4, 2008 6:08 PM by 524753 RSS

    How to get xml file from plsql package

    633466
      Hi Frs,

      i have a requirement like converting existing program to xml file.

      They have used Custom package to generate output. but when i set type as XML in concurrent program window not getting xml output.

      how to get xml file from the Concurrent Program which has Type Plsql Stored Procedure.

      pls help.

      Can we call this package in Data template xml.

      Waiting for your suggestion.

      Thanks for spending time.

      Thanks
      Badsha
        • 1. Re: How to get xml file from plsql package
          stevencallan
          Are you trying to generate XML-formatted output from what gets output via a package? You can use xmlgen via SQL*Plus (a direct means) or generate the dbms_output (or file_io) to a table (and select from the table using the dbms built-in).

          To "convert program to xml" is not very clear.
          • 2. Re: How to get xml file from plsql package
            624420
            stevencallan, key word is *"concurrent program"*.
            Badsha, u need use fnd_file package

            fnd_file.put_line (fnd_file.output, p_line);

            In R12 u can see package CST_UninvoicedReceipts_PVT as example.
            • 3. Re: How to get xml file from plsql package
              633466
              Yes i need to get xml file from plsql pacakge.

              If u have any documents related to it pls forward me this is my mail id

              rajesh.mani555@gmail.com

              i am using apps version 11.5.0
              • 4. Re: How to get xml file from plsql package
                gareth.roberts
                fnd_file.put_line(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8" ?>');
                fnd_file.put_line(FND_FILE.OUTPUT,'<ROWSET>';
                fnd_file.put_line(FND_FILE.OUTPUT,'<ROW>';
                fnd_file.put_line(FND_FILE.OUTPUT,'<MYFIELD>abcd</MYFIELD>';
                fnd_file.put_line(FND_FILE.OUTPUT,'</ROW>';
                fnd_file.put_line(FND_FILE.OUTPUT,'<ROW>';
                fnd_file.put_line(FND_FILE.OUTPUT,'<MYFIELD>defg</MYFIELD>';
                fnd_file.put_line(FND_FILE.OUTPUT,'</ROW>';
                fnd_file.put_line(FND_FILE.OUTPUT,'</ROWSET>';

                or you could use xmlelement, xmlaggregate, xmlforest etc functions.
                and/or dbms_xmldom to write then copy to output file via fnd_file.OUTPUT.

                Gareth
                • 5. Re: How to get xml file from plsql package
                  524753
                  Generation of XML can be done in many ways

                  One :

                  SELECT xmlgen.getXml(
                  'SELECT empno "EMP_NO"
                  , ename "NAME"
                  , deptno "DEPT_NO"
                  FROM emp
                  WHERE deptno = 10'
                  , 0
                  ) FROM dual;

                  <?xml version = '1.0'?>
                  <ROWSET>
                  <ROW num="1">
                  <EMP_NO>7782</EMP_NO>
                  <NAME>CLARK</NAME>
                  <DEPT_NO>10</DEPT_NO>
                  </ROW>
                  <ROW num="2">
                  <EMP_NO>7839</EMP_NO>
                  <NAME>KING</NAME>
                  <DEPT_NO>10</DEPT_NO>
                  </ROW>
                  <ROW num="3">
                  <EMP_NO>7934</EMP_NO>
                  <NAME>MILLER</NAME>
                  <DEPT_NO>10</DEPT_NO>
                  </ROW>
                  </ROWSET>


                  Using XML-SQL Utility also xml can be generated.


                  But in Datatemplate , you can call queries, and then you can control the rows returned by using the filters , for this filters you can call function, pl/sql function. etc...

                  If you are thinikng you still want to use the pl/sql package in datatemplate,
                  Yes, we can use them in different ways, by using the oracle table function or Pipelined Table Functions.

                  Pl/sql package will return the collection variable , which you can cast it as table in the query..
                  sample

                  create type numset_t as table of number;
                  /

                  create function f1(x number) return numset_t pipelined is
                  begin
                  for i in 1..x loop
                  pipe row(i);
                  end loop;
                  return;
                  end;
                  /


                  select * from table(f1(3));

                  COLUMN_VALUE
                  ------------
                  1
                  2
                  3

                  In the datatemplate , you can just call the

                  select * from table(f1(3));

                  Or even the parameter can be passed to the function, in which your entire logic can be written, which will return the collection variable.

                  Datatemplate wil be more powerful , when used like this,