Using HTML to Mark Up Your Data in Oracle SQL Developer

thatjeffsmith SQL Developer 13 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.

Tell Others About This Story:

Comments 13

  1. This still exists:
    however when I run the SQL in SQLDeveloper with F9, I get an outline with the red X in a box. Is there some sort of setting in Sql Developer that must be turned on?

      1. thatjeffsmith Post
        Author
        1. I’m using sql developer Version 4.1.5.21. When I type the three UNION lines above and run (ctrl+enter), I get the second and third lines rendering as shown, however the wvu.png line comes up as a red X in a square, like the old HTML days when a graphic file was not at the address coded in the img src tag. However, entering the img src url in a browser itself does render the wvu.png file, so I know it’s there. SQL Developer just won’t render it, no matter what size height and width I give it in the sql.

          1. thatjeffsmith Post
            Author

            Well I wrote this post post 5 years ago, it’s possible things have changed. Are you wanting to render an actual image in a data grid using this trick? Let’s try with that vs the WVU logo…although I have no problem supporting my Mountaineers.

          2. Hmm.. Interestingly enough, perhaps in a cyber “Backyard Brawl”, I have no problems running the code using a Pitt Panthers logo at:
            web-smg.athletics.pitt.edu / images/ siteimages/ Pitt_Script_Gold.jpg

  2. 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.

  3. 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
  4. 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 🙂

  5. 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 *