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

thatjeffsmith SQL Developer 6 Comments

Tell Others About This Story:

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.

Just right-click!

Just right-click!

This will give you something like this…

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

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:

<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF8">
<!-- base href="http://apexdev.us.oracle.com:7778/pls/apx11w/" -->
<style type="text/css">
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;
}
</style>
<script type="text/javascript">
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<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;}
}</script>
</head><body onload="apex_search.init();">
<table border="0" cellpadding="0" cellspacing="0">
<tbody><tr><td><input type="text" size="30" maxlength="1000" value="" id="S" onkeyup="apex_search.search(event);" /><input type="button" value="Search" onclick="apex_search.lsearch();"/> 
</td></tr>
</tbody></table>
<br>
<table border="0" cellpadding="0" cellspacing="0">
<tr>	<th>PLAYER</th>
	<th>TEAM</th>
	<th>SUM(POINTS)</th>
</tr>
<tbody id="data">
 
	<tr>
<td>DIONNE, MARCEL</td>
<td>LAK</td>
<td align="right">1307</td>
	</tr>
	<tr>
<td>DIONNE, MARCEL</td>
<td>DET</td>
<td align="right">366</td>
	</tr>
	<tr>
<td>DIONNE, MARCEL</td>
<td>NYR</td>
<td align="right">182</td>
	</tr>
	<tr>
<td>FRANCIS, RON</td>
<td>HAR</td>
<td align="right">821</td>
	</tr>
	<tr>
<td>FRANCIS, RON</td>
<td>PIT</td>
<td align="right">700</td>
	</tr>
	<tr>
<td>FRANCIS, RON</td>
<td>CAR</td>
<td align="right">354</td>
	</tr>
	<tr>
<td>FRANCIS, RON</td>
<td>TOR</td>
<td align="right">50</td>
	</tr>
	<tr>
<td>GRETZKY, WAYNE</td>
<td>EDM</td>
<td align="right">1669</td>
	</tr>
	<tr>
<td>GRETZKY, WAYNE</td>
<td>LAK</td>
<td align="right">918</td>
	</tr>
	<tr>
<td>GRETZKY, WAYNE</td>
<td>NYR</td>
<td align="right">249</td>
	</tr>
	<tr>
<td>GRETZKY, WAYNE</td>
<td>STL</td>
<td align="right">123</td>
	</tr>
	<tr>
<td>MESSIER, MARK</td>
<td>EDM</td>
<td align="right">1034</td>
	</tr>
	<tr>
<td>MESSIER, MARK</td>
<td>NYR</td>
<td align="right">691</td>
	</tr>
	<tr>
<td>MESSIER, MARK</td>
<td>VAN</td>
<td align="right">162</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>PHI</td>
<td align="right">777</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>PIT</td>
<td align="right">385</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>MTL</td>
<td align="right">370</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>BOS</td>
<td align="right">168</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>ATL</td>
<td align="right">88</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>CAR</td>
<td align="right">71</td>
	</tr>
	<tr>
<td>RECCHI, MARK</td>
<td>TBL</td>
<td align="right">45</td>
	</tr>
</tbody></table><!-- SQL:
null--></body></html>

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:

Whiz-bang!

Whiz-bang!

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.

Tell Others About This Story:

Comments 6

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

    1. thatjeffsmith Post
      Author

Leave a Reply

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