30 SQL Developer Posts in 30 Days, Day 30: Getting Your Data to the Web

You have a query.

You run a query.

Now how can I take that data and make it available in a web browser or application?

Export as HTML

You can of course export your resultset as HTML using the Grid, right mouse, Export, to HTML.

This will give you something like this…

An old-school HTML table display with some built-in javascript to do search/filtering

Don’t forget the hint!

Remember, lazy-dev method:

 SELECT /*html*/ player, team, SUM(points) FROM hockey_stats
WHERE player IN (
SELECT player FROM ( 
  SELECT SUM(POINTS) , PLAYER FROM HOCKEY_STATS
  GROUP BY PLAYER
  ORDER BY SUM(POINTS) DESC )
fetch FIRST 5 ROWS ONLY)
GROUP BY player, team
ORDER BY player, SUM(points) DESC;

And execute as script (F5) gives me this:

&lt;html&gt;&lt;head&gt;
&lt;meta http-equiv=&quot;content-type&quot; content=&quot;text/html; charset=UTF8&quot;&gt;
&lt;!-- base href=&quot;http://apexdev.us.oracle.com:7778/pls/apx11w/&quot; --&gt;
&lt;style type=&quot;text/css&quot;&gt;
table {
background-color:#F2F2F5;
border-width:1px 1px 0px 1px;
border-color:#C9CBD3;
border-style:solid;
}
td {
color:#000000;
font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;
font-size:9pt;
background-color:#EAEFF5;
padding:8px;
background-color:#F2F2F5;
border-color:#ffffff #ffffff #cccccc #ffffff;
border-style:solid solid solid solid;
border-width:1px 0px 1px 0px;
}
th {
font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;
font-size:9pt;
padding:8px;
background-color:#CFE0F1;
border-color:#ffffff #ffffff #cccccc #ffffff;
border-style:solid solid solid none;
border-width:1px 0px 1px 0px;
white-space:nowrap;
}
&lt;/style&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
window.apex_search = {};
apex_search.init = function (){
	this.rows = document.getElementById('data').getElementsByTagName('TR');
	this.rows_length = apex_search.rows.length;
	this.rows_text =  [];
	for (var i=0;i&lt;apex_search.rows_length;i++){
        this.rows_text[i] = (apex_search.rows[i].innerText)?apex_search.rows[i].innerText.toUpperCase():apex_search.rows[i].textContent.toUpperCase();
	}
	this.time = false;
}
 
apex_search.lsearch = function(){
	this.term = document.getElementById('S').value.toUpperCase();
	for(var i=0,row;row = this.rows[i],row_text = this.rows_text[i];i++){
		row.style.display = ((row_text.indexOf(this.term) != -1) || this.term  === '')?'':'none';
	}
	this.time = false;
}
 
apex_search.search = function(e){
    var keycode;
    if(window.event){keycode = window.event.keyCode;}
    else if (e){keycode = e.which;}
    else {return false;}
    if(keycode == 13){
		apex_search.lsearch();
	}
    else{return false;}
}&lt;/script&gt;
&lt;/head&gt;&lt;body onload=&quot;apex_search.init();&quot;&gt;
&lt;table border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot;&gt;
&lt;tbody&gt;&lt;tr&gt;&lt;td&gt;&lt;input type=&quot;text&quot; size=&quot;30&quot; maxlength=&quot;1000&quot; value=&quot;&quot; id=&quot;S&quot; onkeyup=&quot;apex_search.search(event);&quot; /&gt;&lt;input type=&quot;button&quot; value=&quot;Search&quot; onclick=&quot;apex_search.lsearch();&quot;/&gt; 
&lt;/td&gt;&lt;/tr&gt;
&lt;/tbody&gt;&lt;/table&gt;
&lt;br&gt;
&lt;table border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot;&gt;
&lt;tr&gt;	&lt;th&gt;PLAYER&lt;/th&gt;
	&lt;th&gt;TEAM&lt;/th&gt;
	&lt;th&gt;SUM(POINTS)&lt;/th&gt;
&lt;/tr&gt;
&lt;tbody id=&quot;data&quot;&gt;
 
	&lt;tr&gt;
&lt;td&gt;DIONNE, MARCEL&lt;/td&gt;
&lt;td&gt;LAK&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;1307&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;DIONNE, MARCEL&lt;/td&gt;
&lt;td&gt;DET&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;366&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;DIONNE, MARCEL&lt;/td&gt;
&lt;td&gt;NYR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;182&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;FRANCIS, RON&lt;/td&gt;
&lt;td&gt;HAR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;821&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;FRANCIS, RON&lt;/td&gt;
&lt;td&gt;PIT&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;700&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;FRANCIS, RON&lt;/td&gt;
&lt;td&gt;CAR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;354&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;FRANCIS, RON&lt;/td&gt;
&lt;td&gt;TOR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;50&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;GRETZKY, WAYNE&lt;/td&gt;
&lt;td&gt;EDM&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;1669&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;GRETZKY, WAYNE&lt;/td&gt;
&lt;td&gt;LAK&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;918&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;GRETZKY, WAYNE&lt;/td&gt;
&lt;td&gt;NYR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;249&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;GRETZKY, WAYNE&lt;/td&gt;
&lt;td&gt;STL&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;123&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;MESSIER, MARK&lt;/td&gt;
&lt;td&gt;EDM&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;1034&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;MESSIER, MARK&lt;/td&gt;
&lt;td&gt;NYR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;691&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;MESSIER, MARK&lt;/td&gt;
&lt;td&gt;VAN&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;162&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;PHI&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;777&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;PIT&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;385&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;MTL&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;370&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;BOS&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;168&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;ATL&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;88&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;CAR&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;71&lt;/td&gt;
	&lt;/tr&gt;
	&lt;tr&gt;
&lt;td&gt;RECCHI, MARK&lt;/td&gt;
&lt;td&gt;TBL&lt;/td&gt;
&lt;td align=&quot;right&quot;&gt;45&lt;/td&gt;
	&lt;/tr&gt;
&lt;/tbody&gt;&lt;/table&gt;&lt;!-- SQL:
null--&gt;&lt;/body&gt;&lt;/html&gt;

But the Internet wants more pictures!

Then let’s turn my query into a chart.

Create a new user defined report, paste the query in, and set the style as ‘Chart.’ With no additional formatting work, I can get this:

After creating and styling your report, simply right click on it in the reports tree and select ‘HTML..’ – that generates a directory with the html file, the images, and a javascript resource file.

Ok, but I need REAL HTML stuff…

Then you could also try sending your query to APEX as a new application. This has been available since version 1.2 or so.

I did this a long time ago, and I'm NOT an APEX developer...but you can do this pretty easily. — I did this a long time ago, and I’m NOT an APEX developer…but you can do this pretty easily.

But I just want to get JSON out to my web application

Then you’ll want to setup Oracle REST Data Services and use the SQL Developer integration to setup a restful endpoint for your query. Kris talks about that here.

This concludes our 30 day post binge!

Thanks for hanging around, and sorry if I have filled up your inbox or twitter stream with SQL Developer posts! Ok, I’m not really that sorry.

I’ll be back to posting 1-3x a week now. We’ll have plenty to talk about once v4.1 is ready for Early Adopter. I talked about what we’re doing at Open World Yesterday and it was video recorded. Once I have that link, I’ll share it here.

6 Comments

Mark 10 years ago Reply

Hi Jeff,
is there an easy way to get a collection of all the 30 tipps except for searching for them?
Something like a category or a summing up post?
If it’s there, I just did not find it :-))
Thanks a lot and regards, Mark
- thatjeffsmith Post Author 10 years ago
  
  I’ll do that first thing tomorrow 🙂
- Sabine Heimsath 10 years ago
  
  The summary:
  http://www.thatjeffsmith.com/archive/2014/10/30-posts-in-30-days-the-reckoning/
- Sabine Heimsath 10 years ago
  
  Searching for ’30 SQL Developer’ helps a little bit
  (Google gives you even more than you asked for)
John O'Toole 10 years ago Reply

Great series of posts Jeff. Now get back to those press-ups!
Keith Williams 10 years ago Reply

Nice one Jeff. I always forget the APEX option. Wish I was at OOW 2014 but I had other obligations this year.

Export as HTML

Don’t forget the hint!

But the Internet wants more pictures!

Ok, but I need REAL HTML stuff…

But I just want to get JSON out to my web application

This concludes our 30 day post binge!

30 SQL Developer Posts in 30 Days, Day 29: Preferences I Always Change on a New Install

Real Time SQL Monitoring Support in Oracle SQL Developer [Video]

Related Posts

Loading JSON data into both 19c & 23ai tables via REST APIs

Oracle SQL Developer Extension for VS Code: Reports!

A quick note on Creating PDBs using REST APIs with ORDS

6 Comments

Write A Comment Cancel Reply