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 ';