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.