SDSQL - Pre and Post Commands

  This is in the process of evolving but should be enough for anyone to give it a whirl.  The idea is that for any given statement you may want to run something before and possible after.

  Here's a trivial example.  I'm issuing 2 statements 1) a drop table and 2) creating a table of select * from v$mystat before each command.  Then after I'm doing sql that shows me the changes from that point to now.

SQL> select count(1) from emp;

Table MY_STATS dropped.


Table MY_STATS created.

  COUNT(1)
----------
        14

NAME                                                                  DELTA
---------------------------------------------------------------- ----------
logical read bytes from cache                                       1859584
session pga memory                                                  1507328
session uga memory                                                  1035504
KTFB alloc space (block)                                              65536
cell physical IO interconnect bytes                                   32768
physical write total bytes                                            24576
physical write bytes                                                  24576
redo size                                                             16200
.....

This is on the same connection you are executing on so whatever is being done will show in the stats as well. One thing still being worked on is the ability to clone and pass some context to another connect and have these pre and post things done on that connection keeping the main connection free of that noise.

The way to do this is the introduction of 2 new set commands. The names should be a clue as to which is which : set precommand and set postcommand. They can hold 1 or N commands to be run.

set precommand drop table my_stats; create table my_stats as select * from v$mystat;
set postcommand select s.name, ( m2.value - m.value ) delta from my_stats m, v$mystat m2, V$STATNAME s where m.STATISTIC#   = m2.STATISTIC# and m.STATISTIC#    = s.STATISTIC# and m.value != m2.value order  by 2 desc;

select count(1) from user_objects where object_name like 'E%';
~                                                                 

The current downside of this method is that it needs to be all on one line.  So, if there's multiple commands to be run it's kinda ugly.  Like in this precommand where I drop and create a table.  The great thing about this is that the command to be run could be @mypre.sql

This allows me to better format the sql and make it much much easier to read and maintain.

└─>cat pre.sql 
drop table my_stats;
create table my_stats as select * from v$mystat;

└─>cat post.sql 
select s.name, ( m2.value - m.value ) delta 
  from my_stats m, v$mystat m2, V$STATNAME s 
  where m.STATISTIC#   = m2.STATISTIC# 
    and m.STATISTIC#    = s.STATISTIC# 
    and m.value != m2.value order  by 2 desc;

Then the sql set command look just like expected.

set precommand @pre.sql
set postcommand @post.sql

Now if you want to mess with people throw something like this in their login.sql when they aren't looking

SQL> set postcommand select 'Better go tune that before Joel sees it' MOTD  from dual;


Then on every command they will get that message.

SQL> select 1 from dual;


         1
----------
         1

MOTD                                 
---------------------------------------
Better go tune that before Joel sees it

SQL>