Thursday, January 05, 2006

A 9i-10g version....

A 9i/10g version of fncRefCursor2HTML. It was asked for, so here it is. Really the only big change is the removal of the 10g “quoting” feature on the style sheet.



CREATE OR REPLACE
FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)
RETURN CLOB
as
lHTMLOutput XMLType;
lXSL long;
lXMLData XMLType;
lContext DBMS_XMLGEN.CTXHANDLE;
BEGIN
-- get a handle on the ref cursor --
lContext := DBMS_XMLGEN.NEWCONTEXT(rf);

-- setNullHandling to 1 (or 2) to allow null columns
-- to be displayed
DBMS_XMLGEN.setNullHandling(lContext,1);

-- create XML from ref cursor --
lXMLData := DBMS_XMLGEN.GETXMLTYPE
(lContext,DBMS_XMLGEN.NONE);

-- this is a generic XSL for Oracle's default
-- XML row and rowset tags --
-- " " is a non-breaking space --
lXSL :=
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<xsl:for-each select="/ROWSET/ROW[1]/*">
<th><xsl:value-of select="name()"/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="/ROWSET/*">
<tr>
<xsl:for-each select="./*">
<td><xsl:value-of select="text()"/> </td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>';

-- XSL transformation to convert XML to HTML --
lHTMLOutput := lXMLData.transform(XMLType(lXSL));

-- convert XMLType to Clob --
return lHTMLOutput.getClobVal();
END fncRefCursor2HTML;
/
POST A COMMENT

24 Comments:

Blogger Mark Wooldridge said....

Great idea to leverage xml for this.

Noticed that the context is not closed. Is there a limit to opened context? or any issues with not closing the context?

Also you can set the xsl on the context and then use dbms_xmlgen.getxml and the result is already translated. Remember to make the xsl variable xmltype and use xmltype to construct.

...
dbms_xmlgen.setxslt(v_ctx, v_xsl);

v_clob := dbms_xmlgen.getxml(v_ctx);

dbms_xmlgen.closecontext(v_ctx);

return v_clob;

I also have a version of the stylesheet with templates in place of the for-each lines. I could not post, got error:

"Your HTML cannot be accepted: PHP, ASP, and other server-side scripting is not allowed."

How do you post reviews with html in them?

Thu Jan 05, 04:54:00 PM EST  

Anonymous Anonymous said....

As per Mark (above), I notice that the cursor is also not closed. Does it get closed for you (as it has already been walked to the end anyway).

I've tried (honest!) to find some decent documentation on dbms_xmlgen but failed. Can you point me at some please?

Cheers,
Justin.

Fri Jan 06, 06:47:00 AM EST  

Blogger Thomas Kyte said....

thanks for the context thing - that should be added for sure (to close it).

How to post HTML/XML to the comment section - you have to use entities, instead of "less than", you use "ampersand lt semi-colon"

<

Fri Jan 06, 06:53:00 AM EST  

Blogger Thomas Kyte said....

The cursor is owned by the client - they should close any cursor they open. (eg: if you add an "if rf%isopen" in the example, you'll find the cursor is still open)

Fri Jan 06, 06:56:00 AM EST  

Anonymous Anonymous said....

(cursor closing)

Of course.

I've been playing with this all morning while I wait for some documentation to arrive. It's a really nice introduction to generating xml in the database.

Thanks to Bill Myers and Tom.

Justin.

Fri Jan 06, 07:24:00 AM EST  

Blogger Thomas Kyte said....

to find some decent documentation on dbms_xmlgen but failed.

Does this help

I like the "virtual book" search. Especially a link like Examples using...

Fri Jan 06, 07:34:00 AM EST  

Blogger Gleisson Henrique said....

Tom, you beat me to it. I was trying to write a version for 9i (9.2.0.1.0) which I have at work, but I couldn't find an equivalent for the function setNullHandling of the DBMS_XMLGEN package. I was surprised when I saw on your code. I checked the documentation and setNullHandling is not there for 9.2.0.1.0. When I tried your code and I got an error PLS-00302: component 'SETNULLHANDLING' must be declared. Is there a work around ?
I must be doing something wrong.
And this function is so amazing and so useful that I really want to put it to work. This function is like one of those things that you never knew existed, but now that you do you can't live without it.

Fri Jan 06, 07:41:00 AM EST  

Blogger Thomas Kyte said....

setnullhandling

I did this in 9206 - the current patch set.

9205 has it.
9204 has it.
9203 does not have it.

So, you would need to patch up to at least 9204 to have the setnullhandling - else you'll have to comment that out.

Fri Jan 06, 07:51:00 AM EST  

Blogger Gleisson Henrique said....

Patches huh ??? I don't think my DBA has ever heard of such things. He is one of those that believes we are better off staying one version behind because new versions are so buggy. Thanks Tom you really are amazing.

Fri Jan 06, 08:05:00 AM EST  

Anonymous Bill said....

Yeah, the code should be closing the context and the ref cursor. I am usually pretty good about that kind of stuff. Sorry for any confusion.

Fri Jan 06, 10:30:00 AM EST  

Blogger Mark Wooldridge said....

just in case anyone wants it, how about a template that supports nested cursors.

create or replace
function refcur2html2(p_cursor in sys_refcursor)
return clob is

v_clob clob;
v_xsl xmltype;

v_ctx dbms_xmlgen.ctxhandle;

begin
v_xsl := xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html"/>

<xsl:template match="/">
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<xsl:apply-templates select="/ROWSET/ROW[1]/*" mode="header"/>
</tr>
<xsl:apply-templates select="/ROWSET/ROW" mode="detail"/>
</table>
</body>
</html>
</xsl:template>


<xsl:template match="*" mode="header">
<!--
case
when node_name like %_SET
then <table><xsl:apply-templates mode="header"/></table>start new table
when node_name like %_SET_ROW
then <tr><xsl:apply-templates mode="header"/></tr>
else <th>nodename</th>
end case
-->
<xsl:choose>
<xsl:when test="contains(name(), ''_SET_ROW'')"></xsl:when>

<xsl:when test="contains(name(), ''_SET'')">
<th>
<table border="1">
<tr>
<th>
<xsl:value-of select="name()"/>
</th>
</tr>
<xsl:apply-templates select="node()[1]" mode="header"/>
</table>
</th>
</xsl:when>

<xsl:otherwise>
<th><xsl:value-of select="name()"/></th>
</xsl:otherwise>
</xsl:choose>

</xsl:template>


<xsl:template match="ROW" mode="detail">
<tr><xsl:apply-templates mode="detail"/></tr>
</xsl:template>

<xsl:template match="*" mode="detail">
<!--
when at _set_row
then create header
-->
<xsl:choose>
<xsl:when test="contains(name(), ''_SET_ROW'')">
<tr><xsl:apply-templates mode="detail"/></tr>
</xsl:when>

<xsl:when test="contains(name(), ''_SET'')">
<td align="center">
<table border="1">
<tr bgcolor="cyan">
<xsl:apply-templates select="./*[1]/*" mode="header"/>
</tr>
<xsl:apply-templates mode="detail"/>
</table>
</td>
</xsl:when>

<xsl:otherwise>
<td><xsl:value-of select="text()"/> </td>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>
');

v_ctx := dbms_xmlgen.newcontext(p_cursor);
dbms_xmlgen.setnullhandling(v_ctx, 1);
dbms_xmlgen.setxslt(v_ctx, v_xsl);

v_clob := dbms_xmlgen.getxml(v_ctx);

dbms_xmlgen.closecontext(v_ctx);

return v_clob;

end;
/

show errors


select refcur2html2(
cursor(select object_id, object_name,
cursor(select object_id, object_name from user_objects where rownum <= 2) dept_set,
cursor(select 1 id, 2 nam, 3 val from user_objects where rownum <= 3) m_set
from user_objects
where rownum <= 1))
from dual
/


you must name the nested cursor with a name then ends with '_SET'. I needed a way to identify when a new set would occur that is different than the normal rows of the main select.

I also have a version that puts the headers for the nested items in with the main header. This one needs some work to get the colspan value correct.

I also have a version of the original that is more recursive and uses templates in place of the for-each calls. Similar to how this template works.

Just to clarify, the original closed the cursor just not the xml context.

Fri Jan 06, 12:41:00 PM EST  

Anonymous Markku Uttula said....

Patches huh ??? I don't think my DBA has ever heard of such things. He is one of those that believes we are better off staying one version behind because new versions are so buggy.

Hmm... I'd say you're in desperate need of a new DBA :)

Yes, latest versions possibly have bugs in them - or introduce ones that haven't been there before. Still - there's a reason they're new versions; they're supposed to fix bugs that affect most of the clientele...

Most of Oracle bugs I've encoutered are such that patches have fixed. When the errors in execution have not been fixed, the most propable reason has been that the algorithm attempted to implement has been faulty to begin with.

Not to say that this would always be the case, but most often it has been. Common bugs in Oracle releases have very seldom been of the "critical" type; failing to address them on the other hand very often has been.

Which is better in a long run; a database that might have an error in it compared to one that quite possibly has the error? Well, of course, that's always up to the database administrator. However - my personal belief is that if there's something I can do to make things work smoother for all of the development team, then it's usually worth doing it. If we're depending on an engine bug for our software to work, that can't be too long a road to take (in the big picture) - but that's just my view of things; the next man might have (and most likely does have) a different opinion.

Sat Jan 07, 10:18:00 PM EST  

Anonymous Anonymous said....

Tom,,
Where are you? How come no new items yet!!! I am sure the hits to your site may not have gone down, though :-)

Sun Jan 08, 06:02:00 PM EST  

Anonymous Anonymous said....

Hi Tom.

Thank you for this very useful function! However I am having a bit of difficulty utilizing it when the cursor contains CASE statements.

For Example:

#### TEST CASE ####

SELECT Fncrefcursor2html
(CURSOR(
SELECT DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
COUNT(CASE WHEN a.object_type = 'TABLE' THEN 1 ELSE NULL END) "TABLES",
COUNT(CASE WHEN a.object_type = 'INDEX' THEN 1 ELSE NULL END) "INDEXES",
COUNT(CASE WHEN a.object_type = 'PACKAGE' THEN 1 ELSE NULL END) "PACKAGES",
COUNT(CASE WHEN a.object_type = 'SEQUENCE' THEN 1 ELSE NULL END) "Sequences",
COUNT(CASE WHEN a.object_type = 'TRIGGER' THEN 1 ELSE NULL END) "TRIGGERS",
COUNT(CASE WHEN a.object_type NOT IN
('PACKAGE','TABLE','INDEX','SEQUENCE','TRIGGER') THEN 1 ELSE NULL END) "Other",
COUNT(CASE WHEN 1 = 1 THEN 1 ELSE NULL END) "Total"
FROM DBA_OBJECTS a
GROUP BY ROLLUP(a.owner)))
FROM dual;

that call gives the following error dump:
#### ERROR ###
ORA-19202: Error occurred in XML processing
ORA-24347: Warning of a NULL column in an aggregate function
ORA-06512: at "SYS.DBMS_XMLGEN", line 237
ORA-06512: at "SYS.DBMS_XMLGEN", line 271
ORA-06512: at "ACDBA.FNCREFCURSOR2HTML", line 17


As I am new to XML this puzzles me as the query runs quite nicely when not used by the function. Is there a logical explanation?

Thanks Again!

Fri Feb 23, 11:39:00 AM EST  

Anonymous Anonymous said....

This is a bug in 10g R2.

The Bug Number is 5911073 and is being investigated.

Thank you again for the awesome function!!! IT really is a great tool.

Fri Mar 02, 10:55:00 AM EST  

Anonymous Anonymous said....

it turns out that i am a victim of auto-formatting.

My apologies. TOAD auto Formatted all of the xsl key words and this was the problem.

Thank you again.

Fri Mar 02, 12:23:00 PM EST  

Blogger colemarc said....

SQL only variant:

var stylesheet varchar2(4000)

begin
:stylesheet := 'blah-blah';
end;
/

select XMLType.createXML(cursor(select N from (select rownum N from dual connect by rownum <= 4))).transform(XMLType(:stylesheet)).getClobVal() from dual;

Wed Nov 07, 09:43:00 AM EST  

Anonymous Anonymous said....

getting the error

xml parsing failed. incorrect stylesheet. the node is not valid.

pls help to resolve the issue.

Wed Apr 08, 02:49:00 AM EDT  

Blogger Thomas Kyte said....

@anonymous

no version information at all :(

how about a cut and paste from sqlplus of the command and the error stack as well - so we can see what you did.

Wed Apr 08, 03:33:00 AM EDT  

Anonymous Jalpesh Shah said....

sorry tom, i m using oracle 9i. yes i do cut and paste the code and incorporate ref cursor inside the code.

Wed Apr 08, 06:41:00 AM EDT  

Blogger Thomas Kyte said....

@Jalpesh Shah

I meant for YOU to post YOUR cut and paste so we can verify that you are doing exactly what you said you are...

cut and paste from YOUR sqlplus session.

Wed Apr 08, 07:47:00 AM EDT  

Anonymous Jalpesh Shah said....

i wont be able to upload the code as its showing me that your html cannot be accepted.

tom will brief you about what changes i made in your code.
i have remove the ref cursor from the parameters. and declare the ref cursor inside the code as local variable. rest of the code remain same.
query i used for cursor is

SELECT TRXNDATE, OUTSTANDINGBAL, STGENERAL, RATE, PMTAMOUNT FROM LOANAMORTIZATION WHERE loanserno=296959 AND trxnmsgtype ='PCAP'


pls let me know if any other information you required.

Wed Apr 08, 09:05:00 AM EDT  

Blogger Thomas Kyte said....

you can post < html > you just need to escape it (I did, that is how I posted it in the first place...)

I'd need a full test case - your create table, insert into, etc - showing step by step to reproduce.

Wed Apr 08, 09:19:00 AM EDT  

Blogger dayneo said....

If you encounter the nasty "ORA-24347: Warning of a NULL column in an aggregate function" error, you can work around it by using decodes.
An example can be found here: Work around for ORA-24347: Warning of a NULL column in an aggregate function
The workaround caters for SUM,COUNT and AVG aggregate functions.

Fri Jan 13, 03:23:00 AM EST  

POST A COMMENT

<< Home