Using HTML to Mark Up Your Data in Oracle SQL Developer

thatjeffsmith SQL Developer 7 Comments

Tell Others About This Story:

Looking at data can get tedious. Your eyes start to hurt. Your mind starts to wander. You start humming the theme song to The A-Team. Wouldn’t it be nice if the data you started your day with could be jazzed up a bit?

We’ve seen in an earlier post how you can use gauges and hyperlinks in your queries and grids. But maybe you didn’t realize that just plain jane HTML tags would also work.

Yes folks, you can get Comic Sans MS in your grids.

Here’s an example:

SELECT FIRST_NAME ,
LAST_NAME ,
EMAIL ,
PHONE_NUMBER ,
HIRE_DATE ,
JOB_ID ,
CASE
          WHEN Salary > 5000
          THEN '<html><strong><font color="green" face="Comic Sans MS" size=5>'
               ||SALARY
               ||'</font></strong>'
          ELSE '<html><font color="blue" face="Comic Sans MS" size=5>'
               ||SALARY
               ||'</font>'
     END salary
FROM HR.EMPLOYEES
fetch FIRST 20 ROWS ONLY

Running this in a SQL Developer worksheet with F9 or ctrl+enter will produce output that looks like this:

Punch it up a bit!

So what else can it render? Let’s find out!

SELECT '<HTML><li>My Data</li></html>' MoreFunStuff FROM dual
UNION
SELECT '<HTML><img src="http://www.wvu.edu/images/wvu.png" height=23 width=134></HTML>' MoreFunStuff FROM dual
UNION
SELECT '<HTML><table cols="3" border=2><th>Tables</th><th>Inside of</th><th>Tables</th></table></HTML>' MoreFunStuff FROM dual;

Images, bullets, and table tags – oh my!

What’s This Really Good For Though?

This might fall more on the trick side of the tip vs trick scale. But, if you get creative with reports like @ba6dotus has done here with a Log Switch Frequency Map, then I think you might see some true potential with this.

Don’t Forget to Permanently Save These For Later

After you’ve built a few of these, save them as Snippets. Select the code, right click, ‘Save as Snippet…’ Use the ‘Custom’ category, then you can recall them on demand and not have to worry about pulling them from your SQL History list.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 7

  1. This is a great way to enhance data and reports. Is there any way to keep the colors when exporting the data? I am frequently sending the data out via Excel so it would be great if the colors/formatting would export.

  2. Is there a way to automatically adjust the column width when a column is formated with html?
    It seesm that SQL Developer adjusts the width not only to the data, but also to the html markup, which makes the column much wider than necessary.

    Thank’s for the cool tip.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  3. In previous comment I was referring to HTML in general not only to colors, but I must say that the colors stuck to my mind 🙂

  4. It’s a very nice feature! Colors are a very nice addition for emphasizing specific data.

    It would be really great if when exporting to HTML, EXCEL would keep the colors.

Leave a Reply

Your email address will not be published. Required fields are marked *