Saturday, 15 October 2011

DBMS_XMLGEN

I am trying to create xml output for a concurrent request, bypassing the report piece altogether. Are you familiar with the DBMS_XMLGEN package?

I have gotten so far as to be able to run my procedure and see the output in TOAD using dbms_output.put_line, but I can't seem to quite get the right link to convert this to Oracle Apps output.

I have a concurrent request setup to output XML.
This calls a procedure to return to the output file.
So far, all I am getting returned is 'XML document must have a top level element.' 


Here is the relevant code...

ctx DBMS_XMLGEN.ctxHandle; -- vars to convert SQL output to xml
xml clob;
xmlc varchar2(4000);
off integer := 1;
len integer := 4000;
i number := 0;
rc_data sys_refcursor;

begin
-- this calls simple open cursor for sql statement, returns rows
rc_data := fa10022_data(P_BOOK_TYPE_CODE
, P_STATE
, v_BEG_DATE_EFFECTIVE
, v_END_DATE_EFFECTIVE
, P_BEG_ASSET_ACCT
, P_END_ASSET_ACCT
, v_depr_per_ctr);

ctx := dbms_xmlgen.newContext(rc_data);
dbms_xmlgen.setRowsetTag(ctx, 'XXFA10022');

dbms_xmlgen.setRowTag(ctx, 'G_DATA');

xml := dbms_xmlgen.getXml(Ctx);

i := dbms_xmlgen.getNumRowsProcessed(ctx);

dbms_xmlgen.closeContext(Ctx);

apps.fnd_file.put_line(apps.fnd_file.output, xml);



No comments:

Post a Comment