More fun with SQL

Have you ever wondered if you can dynamically assign columns to a sql statement...using only sql and SQL*Plus commands?

Well...obviously it can be done or this would be one of the shortest blog entries in history!

I have been working on some statspack reports and have been frustrated by the change in sql statement tracking from 9i to 10g. A new column was added to one of the tables and this is the foreign key to another table. Faced with changing structures, the normal process is to create and maintain two separate versions of the code. Of course, since this was going to be a script used extensively and with a lot of information (think of it as my custom version of spreport) maintaining two (or more) versions was not going to be fun (especially when I lose track of what I am working on...).

I began to ponder using SQL*Plus variables as column names...and sure enough it can be done! A SQL*Plus variable can be dynamically assigned using a query and the NEW_VALUE clause of the COLUMN command.

First, issue the COLUMN command with the NEW_VALUE clause to assign the returned value to the variable. I also use the NOPRINT command to suppress any output.

COLUMN sql_summ_col_name NEW_VALUE sql_summ_col_name NOPRINT

Next, query the data dictionary to see if the 'new' column exists. Using the scalar subquery in the decode enables the query to always return a value. 1 if the column exists, 0 if it does not.

SELECT DECODE((SELECT COUNT(1)
               FROM   user_tab_columns
               WHERE  table_name = 'STATS$SQL_SUMMARY'
                 AND  column_name = 'OLD_HASH_VALUE'),
              1,'old_hash_value', 'hash_value') 
               sql_summ_col_name
FROM dual
/

Finally, use the variable in a query.

SQL> SELECT s1.hash_value         c_sql_hash_value,
  2         s1.&sql_summ_col_name c_sql_old_hash_value,
  3         s1.snap_id            c_snap_id
  4  FROM  stats$sql_summary s1
  5  WHERE rownum < 5
  6  /
old   2:        s1.&sql_summ_col_name c_sql_old_hash_value,
new   2:        s1.old_hash_value c_sql_old_hash_value,

C_SQL_HASH_VALUE C_SQL_OLD_HASH_VALUE  C_SNAP_ID
---------------- -------------------- ----------
      3159716790           3694268570         16
       864012087           4274598960         16
      1471956217           2065408759         16
       454710966           3665763022         16

You can see where the variable name is used in the column list and the result of the substitution of the value.

Let's see an example from a 9i database. Same setup, same query up to this one.

SQL> SELECT s1.hash_value         c_sql_hash_value,
  2         s1.&sql_summ_col_name c_sql_old_hash_value,
  3         s1.snap_id            c_snap_id
  4  FROM  stats$sql_summary s1
  5  WHERE rownum < 5
  6  /
old   2:        s1.&sql_summ_col_name c_sql_old_hash_value,
new   2:        s1.hash_value c_sql_old_hash_value,

C_SQL_HASH_VALUE C_SQL_OLD_HASH_VALUE  C_SNAP_ID
---------------- -------------------- ----------
      1353454894           1353454894       4533
      1355497416           1355497416       4533
      1356705287           1356705287       4533
      1356823499           1356823499       4533

As you can see, the column substituted is hash_value as old_hash_value does not exist in this version.

There is one more wrinkle to add to this. In 9i, the c_sql_hash_value and c_sql_old_hash_value columns are going to contain identical data. So...why not suppress the display of the redundant data! Once again, the COLUMN command comes to our rescue.

First define another column

COLUMN old_hv_print NEW_VALUE -
  old_hv_print NOPRINT

Change the data dictionary query slightly to include a call to PRINT or NOPRINT the column.

SELECT DECODE((SELECT COUNT(1)
               FROM   user_tab_columns
               WHERE  table_name = 'STATS$SQL_SUMMARY'
                 AND  column_name = 'OLD_HASH_VALUE'),
              1,'old_hash_value', 'hash_value') 
              sql_summ_col_name,
        DECODE((SELECT COUNT(1)
               FROM   user_tab_columns
               WHERE  table_name = 'STATS$SQL_SUMMARY'
                 AND  column_name = 'OLD_HASH_VALUE'),
              1,'PRINT', 'NOPRINT') old_hv_print
FROM dual
/

Now define the c_sql_old_hash_value COLUMN to include the PRINT/NOPRINT

COLUMN c_sql_old_hash_value  FORMAT 999999999 -
   HEADING 'Old Hash Value' &old_hv_print

Finally, reexcute the queries.
10g - The old_hash_value column will be displayed

SQL> SELECT s1.hash_value         c_sql_hash_value,
  2         s1.&sql_summ_col_name c_sql_old_hash_value,
  3         s1.snap_id            c_snap_id
  4  FROM  stats$sql_summary s1
  5  WHERE rownum < 5
  6  /
old   2:        s1.&sql_summ_col_name c_sql_old_hash_value,
new   2:        s1.old_hash_value c_sql_old_hash_value,

C_SQL_HASH_VALUE C_SQL_OLD_HASH_VALUE  C_SNAP_ID
---------------- -------------------- ----------
      3159716790           3694268570         16
       864012087           4274598960         16
      1471956217           2065408759         16
       454710966           3665763022         16

9i - the old_hash_value column will not be displayed

SQL> SELECT s1.hash_value         c_sql_hash_value,
  2         s1.&sql_summ_col_name c_sql_old_hash_value,
  3         s1.snap_id            c_snap_id
  4  FROM  stats$sql_summary s1
  5  WHERE rownum < 5
  6  /
old   2:        s1.&sql_summ_col_name c_sql_old_hash_value,
new   2:        s1.hash_value c_sql_old_hash_value,

C_SQL_HASH_VALUE  C_SNAP_ID
---------------- ----------
      1353454894       4533
      1355497416       4533
      1356705287       4533
      1356823499       4533