Yet another PRINT_TABLE – as a SQL Macro!

A few weeks ago Jonathan Lewis has published a note about Tom Kyte’s print_table – a small PL/SQL procedure to output each row in a table as a list of (column_name , value). And since this note has gained some comments with other implementations, here is my contribution. Guess how? Of course with a SQL macro.

So what we need is instead of this output:

SQL> select * from scott.emp where empno = 7369;

  EMPNO ENAME   JOB      MGR HIREDATE             SAL  COMM  DEPTNO
------- ------- ------ ----- ------------------- ---- ----- -------
   7369 SMITH   CLERK   7902 17.12.1980 00:00:00  800            20

Listing 1: a row in a table

a way to produce the output like this:

EMPNO                         : 7369
ENAME                         : SMITH
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-dec-1980 00:00:00
SAL                           : 800
COMM                          :
DEPTNO                        : 20
-----------------

Listing 2: the desired output: each column in a separate row

Why not just use UNPIVOT clause? Well the obvious problem with this approach is that you have to list the columns in the unpivot-in-clause:

That wouldn’t be a reusable dynamic approach but require considerable effort each time you want to produce such output.

Yet another problem is that all columns in the unpivot-in-clause should be of the same data type. Whereas the first SQL with just two numeric columns works fine, trying to include the column ENAME in the second one ends up with ORA-01790:

SQL> set echo on
SQL> select col_name, col_value
  2  from  scott.emp
  3        unpivot include nulls 
  4        (col_value for col_name in (empno, mgr)  );

COL_N  COL_VALUE
----- ----------
EMPNO       7369
MGR         7902
EMPNO       7499
MGR         7698
... 

SQL> select col_name, col_value
  2  from  scott.emp
  3        unpivot include nulls 
  4        (col_value for col_name in (empno, ename, mgr)  );

Error at Command Line : 53 Column : 42
Error report -
SQL Error: ORA-01790: expression must have same datatype 
            as corresponding expression

Listing 3: using UNPIVOT leads to ORA-01790

The workaround could be to cast all data types to string before doing unpivot like this:

SQL> select col_name, col_value
  2  from  (select to_char(empno) as empno
  3         ,      ename
  4         ,      to_char(mgr) as mgr
  5         from   scott.emp)
  6        unpivot include nulls 
  7        (col_value for col_name in (empno, ename, mgr)  );

COL_N COL_VALUE                               
----- -----------------
EMPNO 7369                                    
ENAME SMITH                                   
MGR   7902                                    
EMPNO 7499                                    
ENAME ALLEN                                   
MGR   7698   
...

Listing 4: the reference query

Now having this query as a reference we can try to implement a reusable function which will act on every table or subquery, whose structure we don’t need to know, which we can simple “call and forget”. Nice use case for SQL macros!

SQL Macro

First of all, it would be a table macro, because we plan to pass data into the function and to be able to select a transformed output from it.

Second, we’ll be passing a table parameter to it, making our macro a “polymorphic view”. By doing so we get the metadata about the structure of the used table via the data type DBMS_TF.TABLE_T: what columns do we have, what the data types are, and so on. Using this metadata we are able to consistently convert all data types to string and to dynamically build the UNPIVOT clause (lines 12-21). All we then have to do, is to put together a valid SQL statement and return it. Look how we are just referencing the table parameter T in the query (line 26). It will be substituted for us after we exit this function, returning the result string, with whatever was used as a parameter in the calling query: this can be a table name, a view, a subquery or maybe a table function. In fact, we don’t need to know what it actually was. For the macro function it’s just enough to know about the parameter T providing all the needed metadata.

SQL> CREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T ) 
  2  RETURN VARCHAR2 SQL_MACRO
  3  AS
  4   v_cols clob ;
  5   v_unpivot  clob ;
  6   v_str   varchar2(200);
  7   v_delimiter varchar2(9):= ',';
  8   v_name dbms_id;
  9   v_sql clob;
 10  BEGIN
 11    FOR I IN 1..t.column.count LOOP
 12      v_name := t.column(i).description.name;
 13      IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  
 14        v_str := v_name;
 15      ELSIF t.column(i).description.type = dbms_tf.type_number THEN
 16        v_str := 'to_char('||v_name||') as '||v_name;
 17      ELSIF t.column(i).description.type = dbms_tf.type_date THEN
 18        v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;
 19      END IF;
 20      v_cols := v_cols || v_delimiter || v_str;
 21      v_unpivot := v_unpivot || v_delimiter || v_name;
 22    END LOOP;
 23    v_cols := LTRIM(v_cols,',');
 24    v_unpivot := LTRIM(v_unpivot,',');
 25    v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||
 26           'FROM   (SELECT '|| v_cols ||' from t )'||
 27           ' UNPIVOT include nulls (col_value for col_name 
                                    in ('||v_unpivot||')  )';
 28    RETURN v_sql;         
 29  END;
 30  /

Function PRINT_TABLE compiled

SQL> with data as (select * from scott.emp where empno = 7369)
  2  select * from print_table(data);

COL_NAME COL_VALUE                               
-------- ----------------------------------------
EMPNO    7369                                    
ENAME    SMITH                                   
JOB      CLERK                                   
MGR      7902                                    
HIREDATE 1980-12-17 00:00:00                     
SAL      800                                     
COMM     (NULL)                                  
DEPTNO   20                                      

8 rows selected. 

SQL> select * from print_table(scott.dept);

COL_NA COL_VALUE                               
------ ----------------------------------------
DEPTNO 10                                      
DNAME  ACCOUNTING                              
LOC    NEW YORK                                
DEPTNO 20                                      
DNAME  RESEARCH                                
LOC    DALLAS                                  
DEPTNO 30                                      
DNAME  SALES                                   
LOC    CHICAGO                                 
DEPTNO 40                                      
DNAME  OPERATIONS                              
LOC    BOSTON                                  

12 rows selected. 

Listing 5: the implementation as a SQL macro works

Works fine! Well, almost fine. You don’t have to change anything to use the function with another table (DEPT). That’s why polymorphic. But this second query apparently needs some kind of separator between the data of different rows.

Adding a row separator

Since we don’t use PL/SQL to print things like the original print_table does but just SELECTing, our separator should just be the part of the query result. What we can do is to introduce another artificial column / value pair as if they were in every queried table and they will act as a separator. BTW, I never thought, I can use “——–” as a column name…

CREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T ) 
RETURN VARCHAR2 SQL_MACRO
AS
 v_cols clob ;
 v_unpivot  clob ;
 v_str   varchar2(200);
 v_delimiter varchar2(9):= ',';
 v_name dbms_id;
 v_sql clob;
BEGIN
  FOR I IN 1..t.column.count LOOP
    v_name := t.column(i).description.name;
    IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  
      v_str := v_name;
    ELSIF t.column(i).description.type = dbms_tf.type_number THEN
      v_str := 'to_char('||v_name||') as '||v_name;
    ELSIF t.column(i).description.type = dbms_tf.type_date THEN
      v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;
    END IF;
    v_cols := v_cols || v_delimiter || v_str;
    v_unpivot := v_unpivot || v_delimiter || v_name;
  END LOOP;
  v_cols := LTRIM(v_cols,',');
  v_unpivot := LTRIM(v_unpivot,',');
  v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||
         'FROM   (SELECT '|| v_cols ||', 
                  ''-------------'' as "--------" from t )'||
         ' UNPIVOT include nulls (col_value for col_name 
              in ('||v_unpivot||', "--------")  )';
  return v_sql;         
END;
/

SQL> select * from print_table(scott.dept);

COL_NAME COL_VALUE                               
-------- ----------------------------------------
DEPTNO   10                                      
DNAME    ACCOUNTING                              
LOC      NEW YORK                                
-------- -------------                           
DEPTNO   20                                      
DNAME    RESEARCH                                
LOC      DALLAS                                  
-------- -------------                           
DEPTNO   30                                      
DNAME    SALES                                   
LOC      CHICAGO                                 
-------- -------------                           
DEPTNO   40                                      
DNAME    OPERATIONS                              
LOC      BOSTON                                  
-------- -------------                           

16 rows selected. 

Listing 6: PRINT_TABLE with row separators

Now, you may ask, why we had used a subquery on line 35 of the Listing 5? Why not just select from the function and put the WHERE clause in the same query? If you are experienced with PTF’s (polymorphic table functions) introduced in 18c, you may think it should work. Finally, if print_table were a PTF, the behavior was advertised as an important performance feature: filtering, partition pruning, projection, etc. all can be done before passing the data into the PTF. But it wouldn’t work with a SQL macro:

SQL> select * from print_table(scott.emp) where empno = 7369;

SQL Error: ORA-00904: "EMPNO": invalid identifier

Listing 7: cannot filter in the same query

And it’s a major difference you should clearly understand. With SQL macros you don’t have any data flow into a macro. It’s all during parsing, so actually where we call print_table(scott.emp), the UNPIVOT query returned from the function will remain. And this query has only two columns: COL_NAME and COL_VALUE, so you cannot filter using EMPNO. Instead, you could filter using COL_NAME:

SQL> select * from print_table(scott.emp) where col_name = 'EMPNO';

COL_NAME COL_VALUE                               
-------- ----------------------------------------
EMPNO    7369                                    
EMPNO    7499                                    
EMPNO    7521               
...

Listing 8: you have only access to the columns remaining after the transformation made by SQL macro

Seeing this, it could be another reasonable requirement: how can we output only a subset of columns?

Printing a subset of columns

It’s pretty straightforward. All we need is an optional parameter of type DBMS_TF.COLUMNS_T and an IF condition to only consider the columns in list if a column list was not empty (line 14):

CREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T
                                     , cols DBMS_TF.COLUMNS_T DEFAULT NULL ) 
RETURN VARCHAR2 SQL_MACRO
AS
 v_cols clob ;
 v_unpivot  clob ;
 v_str   varchar2(200);
 v_delimiter varchar2(9):= ',';
 v_name dbms_id;
 v_sql clob;
BEGIN
  FOR I IN 1..t.column.count LOOP
    v_name := t.column(i).description.name;
    IF v_name MEMBER OF cols OR cols IS NULL THEN
        IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  
          v_str := v_name;
        ELSIF t.column(i).description.type = dbms_tf.type_number THEN
          v_str := 'to_char('||v_name||') as '||v_name;
        ELSIF t.column(i).description.type = dbms_tf.type_date THEN
          v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;
        END IF;
        v_cols := v_cols || v_delimiter || v_str;
        v_unpivot := v_unpivot || v_delimiter || v_name;
    END IF;    
  END LOOP;
  v_cols := LTRIM(v_cols,',');
  v_unpivot := LTRIM(v_unpivot,',');
  v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||
         'FROM   (SELECT '|| v_cols ||', 
                         ''-------------'' as "--------" from t )'||
         ' UNPIVOT include nulls (col_value for col_name in 
                      ('||v_unpivot||', "--------")  )';
  return v_sql;         
END;
/

SQL> select * from print_table(scott.emp, columns(empno, ename));

COL_NAME COL_VALUE                               
-------- -----------------
EMPNO    7369                                    
ENAME    SMITH                                   
-------- -------------                           
EMPNO    7499                                    
ENAME    ALLEN                                   
-------- ------------- 
...

Listing 9: PRINT_TABLE with optionally column subsets

Summary

Really easy and straightforward implementation, but useful and reusable. One of those use cases where we benefit from the provided metadata and can make the code dependent on the column’s data type.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

*