Tuesday, January 03, 2006

I like online communities...

I like online communities – I like the entire concept of “community based support”. So, it makes me really happy to have someone send me an email like this:

I'm an Oracle developer, and I thought I'd share this with you. Using 10gR2, I figured out a way to convert any ref cursor to HTML output without explicitly diving into the data dictionary. Back in 2002, I wrote something that was like this with 500 lines of code to do this same thing. Now, it's about a tenth of that and faster.

Anyway, I want to share it with everybody but don't know where to go. So, please post it/use it/share it however you like (or not).

Thanks for all the advice. Happy Holidays to you and yours.

Bill Myers

It is all about giving back sometimes. I liked his idea for two reasons. First and foremost – he is giving back. That is the best part.

Second, this has some interesting implications for HTML DB and giving it the ability to process ref cursors. Basically, he has a routine to take in any ref cursor (any cursor really, I’ll call it from SQL directly) and convert it into an html table – of course, using the same concept, you can format it as any html you want at runtime:

lRetVal CLOB;
lHTMLOutput XMLType;

lXMLData XMLType;

-- get a handle on the ref cursor --
-- setNullHandling to 1 (or 2) to allow null columns to be displayed --
-- create XML from ref cursor --

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

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

END fncRefCursor2HTML;

And then we can see the “magic” by simply running:

ops$tkyte@ORA10GR2> variable x clob
ops$tkyte@ORA10GR2> declare
2 l_cursor sys_refcursor;
3 begin
4 open l_cursor for select *
from scott.dept where rownum = 1;
5 :x := fncRefCursor2HTML( l_cursor );
6 close l_cursor;
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> print x

<table border="1">
<tr bgcolor="cyan">
<td>NEW YORK</td>


Anyway, I’d like to thank Bill for sharing this – hope he keeps doing that (in forums, in discussions, at user group meetings, where ever). I learn something new everyday – and only because of exchanges of ideas like this…


Blogger Niall said....

I do like that a lot, simple, clear and tremendously useful. Thanks Bill.

Tue Jan 03, 03:31:00 PM EST  

Anonymous Anonymous said....

Hey Bill,
Thanks a lot for sharing,, I believe this piece helps me a lot for the small but significant application that I am developing now.

Although I don't know you,, you are in my list of wonderful people I ran into :-)

Tue Jan 03, 05:08:00 PM EST  

Anonymous Markku Uttula said....

Works like a wonder. I'm attempting to understand how it works ... so far it's all clear, but I don't know what is the function of "q"? Would someone feel like enlightening me?

Wed Jan 04, 09:30:00 AM EST  

Blogger Thomas Kyte said....

the q' is a new quoting mechanism in 10g - consider:

ops$tkyte@ORA10GR2> exec dbms_output.put_line( q'[ How's this, quotes can be 'easy' ]' );
How's this, quotes can be 'easy'

PL/SQL procedure successfully completed.

No more '' in a string to get a '

Wed Jan 04, 10:23:00 AM EST  

Blogger Gleisson Henrique said....

exec dbms_output.put_line( q'[Sweet]' );

PL/SQL procedure successfully completed.

Wed Jan 04, 01:59:00 PM EST  

Anonymous Anonymous said....

For those unlucky folks still stuck in 9i, is there an equivalent?

Thu Jan 05, 02:24:00 PM EST  

Anonymous Bill said....

I think you should be able to use everything here in 9i except for the q' syntax.

Thu Jan 05, 03:48:00 PM EST  

Blogger Thomas Kyte said....

For those unlucky folks still stuck in 9i, is there an equivalent?

I posted a 9i friendly version here...

Thu Jan 05, 04:16:00 PM EST  

Anonymous Markku Uttula said....

the q' is a new quoting mechanism in 10g

I believe it's time for me to read (at least some of) those "what's new" documents on the tools (yes, I indeed consider Oracle as a "tool") I use frequently.

10g is something I "toy with" a lot. However, since in my company, we're stuck with "the lowest common denominator" in database technology, I'm usually "forced" to work with "nothing but" 9i compatible functionality, so this feature has slipped through my fingers - although I must say that I did figure that it must be "something like magicquotes". It's interesting how unfriendly google can be when you search for "oracle q" :D

As you pointed out, it ain't too hard to overcome the 10g requirement on 9i versions. When I tested it (on 10g), it worked correctly from the beginning. With the "backwards compatibility fixes" and (because of my needs) with the possibility to provide the procedure with the XSL as one of the input variables added, this is definitely a procedure I'm very interested in implementing in my company's "tools"-package - you know; the one that has all those things that have yet to find a better place to reside in :)

So - one question still arises; the legal mumbo-jumbo :( I'd like to hear (preferably from Bill Myers, possibly from others to whom this may apply) what exactly is the licensing type required for this piece of code? Since it's being offered like it is, I believe it's supposed to be "public domain", but since this ain't stated anywhere... I'd just like to be sure - I believe all the developers understand my concern. Copyright law is a very nice thing to have around, but sometimes it can prove to be a burden.

Furthermore - as not only is this the matter of concern for Bill Myers, but for the developers of (for example) DBMS_XMLGEN; what are license details for that? And ... and ... - I think I'm heading for an endless road here :)

After all things said, I honestly hope I'm not making this thing too complicated. After all, K.I.S.S. A. T.I.T. - It's very often relatively easy to "Keep it simple, stupid". However, I still try to follow the abbreviation all the way to "and think it through".

Thu Jan 05, 11:17:00 PM EST  

Blogger Thomas Kyte said....

Anything posted here is in the "public domain". Bill sent it along with the expressed desire to share it:

Anyway, I want to share it with everybody but don't know where to go.

Every code snippet I put onto asktom or here is free to be used.

Fri Jan 06, 06:58:00 AM EST  

Anonymous Anonymous said....

The q' syntax is very cool, and will make code more readable, thanks (must have overlooked this in release notes); looks like it's equivalent to C#'s verbatim literals (using @ symbol prefix). On the topic of C# (or .NET), is there (or will there be) support for UDTs in 10g? as there is in java?

Thu Jan 12, 08:37:00 AM EST  

Blogger Thomas Kyte said....

User Defined Types (UDTs) have been available in the database since version 8.0.

Thu Jan 12, 09:42:00 AM EST  

Anonymous Anonymous said....

What I meant to ask was: Is (or will) there be support for UDTs when using ODP for a .NET (C#) application. Can I read a UDT instance directly into a C# object, and can I directly write a C# object (or graph of objects) directly into a UDT (as I can using the SQLData and/or ORAData interfaces, when I'm dealing with a java app)?

Thu Jan 12, 10:35:00 AM EST  

Anonymous Anonymous said....

This works great with a stored procedure that returns a cursor too, but only when the procedure is local.

When the procedure is remote, accessed through a synonym hiding a dblink, I get a weird XML error.

ORA-19202: Error occurred in XML processing ORA-01007: variable not in select list

This works, where ret_cursor is a local procedure populating one output param that is a ref cursor.

l_cursor sys_refcursor;
x clob;


x := fncRefCursor2Html( l_cursor );


This does not work, where local_ret_cursor is a synonym for ret_cursor@someOtherDB

l_cursor sys_refcursor;
x clob;


x := fncRefCursor2Html( l_cursor );


Wed Feb 21, 03:52:00 PM EST  

Anonymous Will said....

I don't think that ref_cursors and user defined types and objects worked over db_links?

Hopefully they will in later versions as they are potentially usefull.

Thu Apr 12, 06:46:00 PM EDT  


<< Home