Wednesday, 28 October 2015

xml bursting using pl/sql stored procedure

create or replace procedure data_xml(errbuf out varchar2,retcode out number)
as
result boolean;
  --
  --Cursor to fetch the data
  --
  CURSOR data_cur
  IS
    --
    select * from emp;
  --
 -- output_row data_cur%rowtype;
BEGIN
  --
  --
  dbms_output.put_line('<?xml version="1.0" encoding="US-ASCII" standalone="no"?>');
  fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>');
  dbms_output.put_line('<OUTPUT>');
  fnd_file.put_line(fnd_file.output,'<OUTPUT>');
  --
 for i in data_cur
 loop
    --
    if   i.Minutes > i.max_running_time then
    --
    dbms_output.put_line('<ROW>');
    fnd_file.put_line(fnd_file.output,'<ROW>');
    --
    dbms_output.put_line('<ename>'||dbms_xmlgen.CONVERT(i.job)||'</ename>');
    fnd_file.put_line(fnd_file.output,'<ename>'||dbms_xmlgen.CONVERT(i.job)||'</ename>');
    --
    dbms_output.put_line('<empno>'||dbms_xmlgen.CONVERT(i.Parent_Req_ID )||'</empno>');
    fnd_file.put_line(fnd_file.output,'<empno>'||dbms_xmlgen.CONVERT(i.job)||'</empno>');
    --
    dbms_output.put_line('<job>'||dbms_xmlgen.CONVERT(i.Req_ID )||'</job>');
    fnd_file.put_line(fnd_file.output,'<job>'||dbms_xmlgen.CONVERT(i.job)||'</job>');
    --
        --
        --
    dbms_output.put_line('</ROW>');
    fnd_file.put_line(fnd_file.output,'</ROW>');
    --
    end if;
  END LOOP;
  
  --
  dbms_output.put_line('</OUTPUT>');
  fnd_file.put_line(fnd_file.output,'</OUTPUT>');
  --
  result:=BURSTING_CP_SUB_PKG.AfterReport();
  
  exception
  when others then
  dbms_output.put_line('error program');
  fnd_file.put_line(fnd_file.output,'error program');
END;

No comments:

Post a Comment