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.