Generate XML Output from a PL/SQL Concurrent Program

Use the utility DBMS_XMLGEN to generate XML output, as in the following 
procedure. An example of a query string used to display multiple nested 
levels of data (using the CURSOR function) is shown below the procedure.

-----------------------------------------------------------------------------
   PROCEDURE xml_output_p(p_query  IN VARCHAR2
                         ,p_rowset IN VARCHAR2
                         )
-- -----------------------------------------------------------------------------
IS
 
l_qryctx     dbms_xmlgen.ctxhandle;
l_length     NUMBER(10);
l_xmlstr     VARCHAR2(32000);
l_offset     NUMBER (10) := 32000;
l_result     CLOB;
l_retrieved  NUMBER (10) := 0;
l_num_rows   NUMBER;
 
BEGIN
 
  l_qryctx := dbms_xmlgen.newcontext(p_query);
 
  -- set rowset tag to ...
  dbms_xmlgen.setrowsettag(l_qryctx,p_rowset);
  dbms_xmlgen.setrowtag(l_qryctx,'ROW');
 
  -- generate the XML
  l_result := dbms_xmlgen.getxml(l_qryctx);
  l_num_rows := dbms_xmlgen.getNumRowsProcessed(l_qryctx);
  FND_FILE.PUT_LINE(FND_FILE.LOG, 'No of rows processed for XML output = ' || l_num_rows);
 
  -- format output for 32000 char maximum
  l_length := NVL(dbms_lob.getlength(l_result),0);
  FND_FILE.PUT_LINE(FND_FILE.LOG, 'XML CLOB Length = ' || l_length);
 
  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(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(FND_FILE.OUTPUT,l_xmlstr);
    END IF;
  END LOOP;
 
  dbms_xmlgen.closecontext(l_qryctx);
 
EXCEPTION
  WHEN OTHERS THEN
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error generating XML output data: '||SQLERRM);
    raise_application_error(-20001,'Error generating XML output data in c4fnd018_apps_cleanup_pkg.xml_output_p');
END xml_output_p;

Using the CURSOR function in the SQL query string:

l_query := ' 
             SELECT fcr.argument1   rep_mode
             ,      fcr.argument2   rep_appl
             ,      fcr.argument3   rep_obj_type
             ,      fcr.argument4   rep_srch_str
             ,      TO_CHAR(SYSDATE,''DD-MM-YYYY'')           rep_date
             ,      TO_CHAR(fcr.request_id,''999999999999'')  rep_request_id
             ,      CURSOR
                    (
                    SELECT cac.object_type
                    ,      cac.object_id
                    ,      cac.object_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',fcpv.user_concurrent_program_name
                                 ,''REQUEST GROUP UNIT'',frg.request_group_name
                                 )                          object_name
                    ,      cac.request_group_id
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',NULL
                                 ,''REQUEST GROUP UNIT'',cac.group_appl_short_name
                                 ,''REQUEST SET'',NULL
                                 ,''EXECUTABLE'',NULL
                                 ,NULL
                                 )                          group_appl_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',NULL
                                 ,''REQUEST GROUP UNIT'',NULL
                                 ,''REQUEST SET'',cac.request_set_appl_short_name
                                 ,''EXECUTABLE'',NULL
                                 ,NULL
                                 )                          request_set_appl_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',cac.conc_prog_appl_short_name
                                 ,''REQUEST GROUP UNIT'',NULL
                                 ,''REQUEST SET'',NULL
                                 ,''EXECUTABLE'',NULL
                                 ,NULL
                                 )                          conc_prog_appl_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',NULL
                                 ,''REQUEST GROUP UNIT'',NULL
                                 ,''REQUEST SET'',NULL
                                 ,''EXECUTABLE'',cac.executable_appl_short_name
                                 ,NULL
                                 )                          executable_appl_short_name
                    ,      cac.executable_appl_short_name
                    ,      cac.action_type
                    ,      cac.action_date
                    ,      fu.user_name                action_by
                    FROM   c4fnd018_apps_cleanup       cac
                    ,      fnd_user                    fu
                    ,      fnd_concurrent_programs_vl  fcpv
                    ,      fnd_request_groups          frg
                    WHERE  1 = 1
                    AND    cac.action_BY = fu.user_id
                    AND    DECODE(cac.object_type,''CONCURRENT PROGRAM'',cac.object_id,-1) = fcpv.concurrent_program_id(+)
                    AND    DECODE(cac.object_type,''REQUEST GROUP UNIT'',cac.request_group_id,-1) = frg.request_group_id(+)
                    ORDER BY cac.object_short_name ASC
                    ,      cac.object_type ASC
                    )      object_list
             FROM   fnd_concurrent_requests     fcr
             WHERE  fcr.request_id = fnd_global.conc_request_id
             ';