A little SQL trick

I’ve been toying with the idea of a wrapper script for generating output in html format with the key goal being to quickly plot the output of a SQL statement as a time-series chart. To this end I wanted to figure out a way where I can execute a query, with some flexibility to control the output format. It was at that point that I figured out a little trick. This may be common knowledge, but to me it was new, so humour me.

In this example I’m using the method to geneare a csv file, not very imaginative and there are easier and more flexible ways of doing this, but it’s a simple place to start.

Here we go. First you have a file called “load_query.sql” which looks like the one below:

with q as
(
@&1
)

Pretty simple stuff. Next we have a file called “csv.sql”, which starts by defining some of the sqlplus output display properties.

-- Parameters
-- 1. Script to execute
-- 2. Output File

-- Settings
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS';
set heading on
set underline off
set pagesize 50000
set linesize 144
set feedback off
set echo off
set trimout on
set verify off

spool &2

-- Load query and execute
@load_query.sql &1
select q.time || ',' || q.value from q;

spool off

Line 20 & 21 above is where the “trick” comes into play.
In line 20 we execute the load_query.sql script, passing the name of the script that we want to generate csv output for. Note that this script has the following properties:

• It returns two columns. As the end-goal will be time-series data, we only need time and value columns
• The query doesn’t end with a semi-colon, that is key, because line 21 is a continuation of the same SQL statement

As an example of a SQL script that can be passed as a parameter when executing csv.sql we can use the query below which shows the number of log switches per hour:

select trunc(first_time, 'hh24') as time ,count(*) as value
from v$log_history
group by trunc(first_time, 'hh24')
order by trunc(first_time,'hh24')

So basically, the csv.sql and load_query.sql files work together to turn the query into the following output:

with q as
(
select trunc(first_time, 'hh24') as time ,count(*) as value
from v$log_history
group by trunc(first_time, 'hh24')
order by trunc(first_time,'hh24')
)
select q.time || ',' || q.value from q;

This forms the basis of a system for working with your script-set in a more dynamic way, giving you control of the form that the output takes. In the next post I’ll show how to use the same principal to create a dygraph plot from query output.