{"id":1649,"date":"2020-12-20T23:57:12","date_gmt":"2020-12-20T21:57:12","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1649"},"modified":"2024-01-07T13:13:18","modified_gmt":"2024-01-07T11:13:18","slug":"yet-another-print_table-as-a-sql-macro","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/yet-another-print_table-as-a-sql-macro\/","title":{"rendered":"Yet another PRINT_TABLE – as a SQL Macro!"},"content":{"rendered":"\n

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

So what we need is instead of this output:<\/p>\n\n\n

\nSQL> select * from scott.emp where empno = 7369;\n\n  EMPNO ENAME   JOB      MGR HIREDATE             SAL  COMM  DEPTNO\n------- ------- ------ ----- ------------------- ---- ----- -------\n   7369 SMITH   CLERK   7902 17.12.1980 00:00:00  800            20\n<\/pre><\/div>\n\n\n
Listing 1: a row in a table <\/em><\/strong><\/p>\n\n\n\n
 a way to produce the output like this:<\/p>\n\n\n
\nEMPNO                         : 7369\nENAME                         : SMITH\nJOB                           : CLERK\nMGR                           : 7902\nHIREDATE                      : 17-dec-1980 00:00:00\nSAL                           : 800\nCOMM                          :\nDEPTNO                        : 20\n-----------------\n<\/pre><\/div>\n\n\n
Listing 2: the desired output: each column in a separate row<\/em><\/strong><\/p>\n\n\n\n
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<\/strong><\/em>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
That wouldn’t be a reusable dynamic approach but require considerable effort each time you want to produce such output. <\/p>\n\n\n\n
Yet another problem is that all columns in the  unpivot-in-clause<\/strong><\/em>  should be of the same data type. Whereas the first SQL with just two numeric columns works fine, trying to include the column ENAME<\/code><\/strong> in the second one ends up with ORA-01790:<\/p>\n\n\n
\nSQL> set echo on\nSQL> select col_name, col_value\n  2  from  scott.emp\n  3        unpivot include nulls \n  4        (col_value for col_name in (empno, mgr)  );\n\nCOL_N  COL_VALUE\n----- ----------\nEMPNO       7369\nMGR         7902\nEMPNO       7499\nMGR         7698\n... \n\nSQL> select col_name, col_value\n  2  from  scott.emp\n  3        unpivot include nulls \n  4        (col_value for col_name in (empno, ename, mgr)  );\n\nError at Command Line : 53 Column : 42\nError report -\nSQL Error: ORA-01790: expression must have same datatype \n            as corresponding expression\n<\/pre><\/div>\n\n\n
Listing 3: using UNPIVOT leads to ORA-01790<\/em><\/strong><\/p>\n\n\n\n
The workaround could be to cast all data types to string before doing unpivot like this:<\/p>\n\n\n
\nSQL> select col_name, col_value\n  2  from  (select to_char(empno) as empno\n  3         ,      ename\n  4         ,      to_char(mgr) as mgr\n  5         from   scott.emp)\n  6        unpivot include nulls \n  7        (col_value for col_name in (empno, ename, mgr)  );\n\nCOL_N COL_VALUE                               \n----- -----------------\nEMPNO 7369                                    \nENAME SMITH                                   \nMGR   7902                                    \nEMPNO 7499                                    \nENAME ALLEN                                   \nMGR   7698   \n...\n<\/pre><\/div>\n\n\n
Listing 4: the reference query <\/strong><\/em><\/p>\n\n\n\n
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!<\/p>\n\n\n\n
SQL Macro<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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<\/strong><\/code>: 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<\/code><\/strong> 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<\/em><\/strong> 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<\/em><\/strong> providing all the needed metadata.<\/p>\n\n\n
\nSQL> CREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T ) \n  2  RETURN VARCHAR2 SQL_MACRO\n  3  AS\n  4   v_cols clob ;\n  5   v_unpivot  clob ;\n  6   v_str   varchar2(200);\n  7   v_delimiter varchar2(9):= ',';\n  8   v_name dbms_id;\n  9   v_sql clob;\n 10  BEGIN\n 11    FOR I IN 1..t.column.count LOOP\n 12      v_name := t.column(i).description.name;\n 13      IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  \n 14        v_str := v_name;\n 15      ELSIF t.column(i).description.type = dbms_tf.type_number THEN\n 16        v_str := 'to_char('||v_name||') as '||v_name;\n 17      ELSIF t.column(i).description.type = dbms_tf.type_date THEN\n 18        v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;\n 19      END IF;\n 20      v_cols := v_cols || v_delimiter || v_str;\n 21      v_unpivot := v_unpivot || v_delimiter || v_name;\n 22    END LOOP;\n 23    v_cols := LTRIM(v_cols,',');\n 24    v_unpivot := LTRIM(v_unpivot,',');\n 25    v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||\n 26           'FROM   (SELECT '|| v_cols ||' from t )'||\n 27           ' UNPIVOT include nulls (col_value for col_name \n                                    in ('||v_unpivot||')  )';\n 28    RETURN v_sql;         \n 29  END;\n 30  \/\n\nFunction PRINT_TABLE compiled\n\nSQL> with data as (select * from scott.emp where empno = 7369)\n  2  select * from print_table(data);\n\nCOL_NAME COL_VALUE                               \n-------- ----------------------------------------\nEMPNO    7369                                    \nENAME    SMITH                                   \nJOB      CLERK                                   \nMGR      7902                                    \nHIREDATE 1980-12-17 00:00:00                     \nSAL      800                                     \nCOMM     (NULL)                                  \nDEPTNO   20                                      \n\n8 rows selected. \n\nSQL> select * from print_table(scott.dept);\n\nCOL_NA COL_VALUE                               \n------ ----------------------------------------\nDEPTNO 10                                      \nDNAME  ACCOUNTING                              \nLOC    NEW YORK                                \nDEPTNO 20                                      \nDNAME  RESEARCH                                \nLOC    DALLAS                                  \nDEPTNO 30                                      \nDNAME  SALES                                   \nLOC    CHICAGO                                 \nDEPTNO 40                                      \nDNAME  OPERATIONS                              \nLOC    BOSTON                                  \n\n12 rows selected. \n<\/pre><\/div>\n\n\n
Listing 5: the implementation as a SQL macro works<\/em><\/strong><\/p>\n\n\n\n
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. <\/p>\n\n\n\n
Adding a row separator<\/h2>\n\n\n\n
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… <\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T ) \nRETURN VARCHAR2 SQL_MACRO\nAS\n v_cols clob ;\n v_unpivot  clob ;\n v_str   varchar2(200);\n v_delimiter varchar2(9):= ',';\n v_name dbms_id;\n v_sql clob;\nBEGIN\n  FOR I IN 1..t.column.count LOOP\n    v_name := t.column(i).description.name;\n    IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  \n      v_str := v_name;\n    ELSIF t.column(i).description.type = dbms_tf.type_number THEN\n      v_str := 'to_char('||v_name||') as '||v_name;\n    ELSIF t.column(i).description.type = dbms_tf.type_date THEN\n      v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;\n    END IF;\n    v_cols := v_cols || v_delimiter || v_str;\n    v_unpivot := v_unpivot || v_delimiter || v_name;\n  END LOOP;\n  v_cols := LTRIM(v_cols,',');\n  v_unpivot := LTRIM(v_unpivot,',');\n  v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||\n         'FROM   (SELECT '|| v_cols ||', \n                  ''-------------'' as "--------" from t )'||\n         ' UNPIVOT include nulls (col_value for col_name \n              in ('||v_unpivot||', "--------")  )';\n  return v_sql;         \nEND;\n\/\n\nSQL> select * from print_table(scott.dept);\n\nCOL_NAME COL_VALUE                               \n-------- ----------------------------------------\nDEPTNO   10                                      \nDNAME    ACCOUNTING                              \nLOC      NEW YORK                                \n-------- -------------                           \nDEPTNO   20                                      \nDNAME    RESEARCH                                \nLOC      DALLAS                                  \n-------- -------------                           \nDEPTNO   30                                      \nDNAME    SALES                                   \nLOC      CHICAGO                                 \n-------- -------------                           \nDEPTNO   40                                      \nDNAME    OPERATIONS                              \nLOC      BOSTON                                  \n-------- -------------                           \n\n16 rows selected. \n<\/pre><\/div>\n\n\n
Listing 6: PRINT_TABLE with row separators<\/em><\/strong><\/p>\n\n\n