In order to do this some "generic" packages were created to create the XML based on the content of a RefCursor. Works like a charm. Just call the function with a RefCursor and get an XML back.
The problem they initially had was that the XML returned uses the ROWSET and ROW tags, while they wanted different tags. No problem, some REPLACE function would do the job, and that worked fine. Until last week that was...
First let me show you how the initial function works and what the XML result was they returned.
SQL> create function rc_to_xml (p_rc in sys_refcursor) 2 return xmltype 3 is 4 begin 5 return xmltype (p_rc); 6 end rc_to_xml; 7 / Function created.
The function has an input parameter which is a Ref Cursor and returns an XMLType. The XMLType constructor can take a Ref Cursor and transform that into an XMLType, pretty cool functionality. In order to test this functionality, we need two local variables - one for the Ref Cursor and one for the XMLType.
SQL> declare 2 refC sys_refcursor; 3 x xmltype; 4 begin 5 open refC for 6 select ename 7 , job 8 , sal 9 from emp 10 where rownum <= 2 11 ; 12 x := rc_to_xml (refC); 13 dbms_output.put_line (x.extract ('/*').getClobVal()); 14 end; 15 /
Using DBMS_OUTPUT to see the actual content of the XMLType, we will get this result:
SMITH CLERK 800
ALLEN SALESMAN 1600
A nice XMLType is the result. The only "problem" is the standard tags used by the XMLType. In this project we didn't want to have the standard ROWSET and ROW tags, we wanted to have more meaningful names.
Changing the function to change these tags on the fly would work
SQL> create or replace 2 function rc_to_xml (p_rc in sys_refcursor 3 ,p_rowset in varchar2 4 ,p_row in varchar2 5 ) 6 return xmltype 7 is 8 retval xmltype; 9 c clob; 10 begin 11 retval := xmltype (p_rc); 12 c := retval.getClobVal(); 13 c := replace (c, '
', '<'||p_rowset||'>'); 14 c := replace (c, '', '</'||p_rowset||'>'); 15 c := replace (c, '
', '<'||p_row||'>'); 16 c := replace (c, '', '</'||p_row||'>'); 17 retval := xmltype (c); 18 return retval; 19 end rc_to_xml; 20 / Function created.
Just to make sure it works as expected, a small testcase:
SQL> declare 2 refC sys_refcursor; 3 x xmltype; 4 begin 5 open refC for 6 select ename 7 , job 8 , sal 9 from emp 10 where rownum <= 2 11 ; 12 x := rc_to_xml (refC 13 ,'DEPT' 14 ,'EMP' 15 ); 16 dbms_output.put_line (x.extract ('/*').getClobVal()); 17 end; 18 / <DEPT>
SMITH CLERK 800 </DEPT> ALLEN SALESMAN 1600
And that did the trick for quite some time (years actually).
This technique was used quite heavily for reporting purposes, it even works fine with nesting one XMLType in another:
SQL> create or replace 2 function all_emps (p_deptno in dept.deptno%type) 3 return xmltype 4 is 5 rc sys_refcursor; 6 begin 7 open rc for select ename 8 , job 9 , sal 10 from emp 11 where deptno = p_deptno; 12 return rc_to_xml (rc 13 ,'EMPS' 14 ,'EMP' 15 ); 16 end all_emps; 17 / Function created.
In the below code sample you can see the above function being used in the Ref Cursor on line 8.
SQL> declare 2 refC sys_refcursor; 3 x xmltype; 4 begin 5 open refC for 6 select deptno 7 , dname 8 , all_emps (deptno) employees 9 from dept 10 where deptno = 10 11 ; 12 x := rc_to_xml (refC 13 ,'DEPTS' 14 ,'DEPT' 15 ); 16 dbms_output.put_line (x.extract ('/*').getClobVal()); 17 end; 18 / <DEPTS>
</DEPTS> PL/SQL procedure successfully completed. 10 ACCOUNTING CLARK MANAGER 2450 KING PRESIDENT 5000 MILLER CLERK 1300
Very flexible, used extensively throughout the reporting packages.
Last week problems started to occur with these packages however. XMLTypes were not being created correctly, strange characters started to appear causing the XML to be invalid. But not with all reports, not all the time. It appeared that if the reports became to large, this problem would occur.
After looking for the cause for quite some time, we had this feeling that it had to do with the datatype conversions going on between the XMLType and the Clob. This transformation is done in the REPLACE part of the function where the ROWSET and ROW tags are replaced.
First we started looking into creating a "REPLACE function for CLOB", which is according to the documentation not necessary... what a waste of time...
I couldn't help it, but I was getting the feeling that I wasn't the first one with this "problem". Surely there must be a way to rename the ROWSET and ROW tags when you create an XML based on a Ref Cursor?
Probably I was looking in the wrong direction, instead of treating the XML as a string, I should stay in the "XML Domain".
After more searching, finally I came across this procedure: setRowTag in the dbms_xmlgen package - just what the doctor ordered. All the convoluted code we used earlier was no longer necessary.
create or replace function rc_to_xml (p_rc in sys_refcursor ,p_rowset_name in varchar2 ,p_row_name in varchar2 ) return xmltype is retval xmltype; ctx dbms_xmlgen.ctxHandle; begin ctx := dbms_xmlgen.newContext (p_rc); dbms_xmlgen.setRowTag (ctx, p_row_name); dbms_xmlgen.setRowSetTag (ctx, p_rowset_name); retval := dbms_xmlgen.getXMLType (ctx); dbms_xmlgen.closeContext (ctx); return retval; end rc_to_xml; /
It's always good to realize that when you are trying to solve a certain problem someone must have come across your problem before and maybe even solved it. Hope my story will save you some time when you are trying to rename the ROWSET and ROW tags.
REPLACE; it also works with CLOB
DBMS_XMLGEN - all subprograms