Sokrates on Oracle

Tom’s print_table as a SQL Macro

Posted by Matthias Rogel on 5. May 2021

I have used Tom Kyte’s “print_table” (Jonathan Lewis has archived the code on https://jonathanlewis.wordpress.com/2020/11/26/print_table ) quite a lot during the last nearly 20 years. There are many use cases where it comes in handy to have a resultset of a query formatted as one row “per column per row”. I have adapted the above mentioned code into a pipelined table function fprint_table, so I can do a

select * from table(fprint_table(q'| <whatever query> |'))

, which I find even more useful. However it is a pain to parse back again the column-names of the original-query (using substr), so I always wished there was sometimes some native method to have the “one row per column per row” – result in the form “ROW / COLUMN / VALUE”. Having watched Chris Saxon’s fantastic presentation on Pattern Matching and SQL Macros ( google for “sql macro chris saxon” – code on https://livesql.oracle.com/apex/livesql/file/content_KPBT70LOJM9EIEWT5WWQTMXRN.html ) it was clear to me that SQL Macros could do that.

Since I am currently ill (learnt a new word this week – “lumboischialgia” – which is no fun especially when it is bilateral !), I decided to play around a bit with Oracle 21 (and especially SQL Macros) on my Oracle Always Free account and see how to implement that.

First of all, it is clear that different columns of a query can have different datatypes and in order to have “VALUE” support all those datatypes, it either has to be an ANYDATA or we have to transform other datatypes than VARCHAR2 and CHAR into VARCHAR2. I decided to go for the latter option (ANYDATA is a bit bulky IMO and Tom’s print_table also simply converts everything into VARCHAR2, so I should be also allowed to do so 🙂). So, first we need a SQL Macro to convert all columns of a table into VARCHAR2s. Here we go

create or replace function tab_to_char(p_tab dbms_tf.table_t)
  return clob sql_macro(table)
is
  ret clob;
begin
  ret := 'select ';
  for i in 1 .. tab_to_char.p_tab.column.count loop
    ret := 
	  ret || 
	  'to_char(' || tab_to_char.p_tab.column(i).description.name || 
          ') as ' || trim(both '"' from tab_to_char.p_tab.column(i).description.name) ||
	  case when i < tab_to_char.p_tab.column.count then ', ' end;
  end loop;
  ret := ret || ' from p_tab';
  
  -- dbms_output.put_line(ret);
  return ret;
end tab_to_char;
/ 

In short, we just apply to_char() to every column (works for DATE, NUMBER, TIMESTAMP, is not needed but also works for VARCHAR2, works also for INTERVAL). Let’s try it out ! Of course, we need some sample data, so I collected some of the titles of my favourite football team 1. FC Kaiserslautern into a table.


create table titles_kaiserslautern(
  what 			varchar2(30),
  when 			date,
  spectators 	number,
  remark 		varchar2(100)
);

insert into titles_kaiserslautern values('German Championship', date'1951-06-30', 85000, '2:1 against Preußen Münster in Berlin');
insert into titles_kaiserslautern values('German Championship', date'1953-06-21', 80000, '4:1 against VfB Stuttgart in Berlin');
insert into titles_kaiserslautern values('German Cup Winner', date'1990-05-19', 76391, '3:2 against Werder Bremen in Berlin');
insert into titles_kaiserslautern values('German Championship', date'1991-06-15', 55000, '6:2 against 1. FC Köln in Köln');
insert into titles_kaiserslautern values('German Supercup', date'1991-08-06', 8000, '3:1 against Werder Bremen in Hannover');
insert into titles_kaiserslautern values('German Cup Winner', date'1996-05-25', 75800, '1:0 against Karlsruher SC in Berlin');
insert into titles_kaiserslautern values('German Championship', date'1998-05-02', 38000, '4:0 against VfL Wolfsburg in Kaiserslautern');
  
commit;

Now, let’s see what tab_to_char does to it – for the sake of briefness let’s restrict to the German championships –

SQL> with championships as (select * from titles_kaiserslautern where what = 'German Championship' order by when)
  2  select * from tab_to_char(championships);

WHAT                   WHEN               SPECTATORS   REMARK
---------------------- ------------------ ------------ --------------------------------------------------
German Championship    30-JUN-51          85000        2:1 against Preußen Münster in Berlin
German Championship    21-JUN-53          80000        4:1 against VfB Stuttgart in Berlin
German Championship    15-JUN-91          55000        6:2 against 1. FC Köln in Köln
German Championship    02-MAY-98          38000        4:0 against VfL Wolfsburg in Kaiserslautern

Hmm, does not look other than the result of a

select * from titles_kaiserslautern where what = 'German Championship' order by when;

of course, since everything is converted to VARCHAR2 (note that the calls to to_char in tab_to_char use the current NLS-settings of the session rather than set explicitly the NLS_DATE_FORMAT as Tom did in the above mentioned code) and hence it makes no difference for our eyes. However, it is needed at that step as in the next step we will use UNPIVOT to unpivot all columns into a single column (and this needs them to be of the same datatype !). Note also that the next step is a SQL Macro calling our first SQL Macro. Here it is:

create or replace function print_table(p_tab dbms_tf.table_t)
  return clob sql_macro(table)
is
  ret clob;
begin
  ret := 'select 
  * 
from 
  (select * from (select rownum as "ROW", t.* from tab_to_char(p_tab) t)) 
  unpivot include nulls (
    value
    for "COLUMN" in
    (
';
  for i in 1 .. print_table.p_tab.column.count loop
    ret := 
	  ret || 
	  '      ' || trim(both '"' from print_table.p_tab.column(i).description.name) 
	  || ' as ''' || trim(both '"' from print_table.p_tab.column(i).description.name) || '''' ||
	  case when i < print_table.p_tab.column.count then ', ' end || chr(10);
  end loop;
  ret := 
    ret || '
    )
  )';
  
  -- dbms_output.put_line(ret);
  return ret;
  
end print_table;
/ 

and in all its beauty we get

SQL> select * from print_table(titles_kaiserslautern);

       ROW COLUMN     VALUE
---------- ---------- ---------------------------------------------
         1 WHAT       German Championship
         1 WHEN       30-JUN-51
         1 SPECTATORS 85000
         1 REMARK     2:1 against Preußen Münster in Berlin
         2 WHAT       German Championship
         2 WHEN       21-JUN-53
         2 SPECTATORS 80000
         2 REMARK     4:1 against VfB Stuttgart in Berlin
         3 WHAT       German Cup Winner
         3 WHEN       19-MAY-90
         3 SPECTATORS 76391
         3 REMARK     3:2 against Werder Bremen in Berlin
         4 WHAT       German Championship
         4 WHEN       15-JUN-91
         4 SPECTATORS 55000
         4 REMARK     6:2 against 1. FC Köln in Köln
         5 WHAT       German Supercup
         5 WHEN       06-AUG-91
         5 SPECTATORS 8000
         5 REMARK     3:1 against Werder Bremen in Hannover
         6 WHAT       German Cup Winner
         6 WHEN       25-MAY-96
         6 SPECTATORS 75800
         6 REMARK     1:0 against Karlsruher SC in Berlin
         7 WHAT       German Championship
         7 WHEN       02-MAY-98
         7 SPECTATORS 38000
         7 REMARK     4:0 against VfL Wolfsburg in Kaiserslautern

28 rows selected.

The argument of print_table does not need to be a table, it also can be a subquery:

SQL> with s as (select * from titles_kaiserslautern where spectators < 50000 order by spectators)
  2  select * from print_table(s);

       ROW COLUMN     VALUE
---------- ---------- ---------------------------------------------
         1 WHAT       German Supercup
         1 WHEN       06-AUG-91
         1 SPECTATORS 8000
         1 REMARK     3:1 against Werder Bremen in Hannover
         2 WHAT       German Championship
         2 WHEN       02-MAY-98
         2 SPECTATORS 38000
         2 REMARK     4:0 against VfL Wolfsburg in Kaiserslautern

8 rows selected.

Finally, I learnt a new Oracle-exception. Of course I could not resist to use a subquery using print_table to be the input for print_table. However:

SQL> with s as (select * from print_table(titles_kaiserslautern))
  2  select * from print_table(s);
with s as (select * from print_table(titles_kaiserslautern))
*
ERROR at line 1:
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported

My conclusion: SQL macros are really cool stuff ! Great we have them available !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: