Print a table with one column name + value per row

There are some utilities out there to print tables with one column per row. Why not use a single SQL statement?

Asktom solution

See Dynamic query to print out any table for a 40-line anonymous PL/SQL block that prints out column name and value pairs using DBMS_OUTPUT. Output from the EMP table looks like this:

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

Tanel Poder

Tanel has a similar script here: https://blog.tanelpoder.com/files/scripts/printtab2.sql

SQL only: test data

To make things interesting, here is a table with various number and date-related columns:

drop table t purge;
create table t(
  pk number primary key,
  var varchar2(10),
  rw raw(10),
  num number,
  dte date,
  ts timestamp,
  tsz timestamp with time zone,
  tsl timestamp with local time zone,
  ids interval day to second,
  iym interval year to month
);

insert into t select level,
  '2', hextoraw('FF'),4.5, sysdate,
  systimestamp, systimestamp, systimestamp, 
  to_dsinterval('P1DT2H3M4S'), to_yminterval('P1Y1M')
from dual
connect by level <= 2;
commit;

select * from t;
PK VAR RW NUM DTE TS TSZ TSL IDS IYM
1 2 FF 4,5 2018-05-18 12:21:33 2018-05-18 12:21:33 2018-05-18 12:21:33 +02:00 2018-05-18 12:21:33 EUROPE/PARIS +01 02:03:04. +00001-01
2 2 FF 4,5 2018-05-18 12:21:33 2018-05-18 12:21:33 2018-05-18 12:21:33 +02:00 2018-05-18 12:21:33 EUROPE/PARIS +01 02:03:04. +00001-01

 

Using DBMS_XMLGEN

DBMS_XMLGEN.GETXMLTYPE generates an XML document with the content of whatever query you pass it. (I had to change to {} because otherwise WordPress interprets the XML instead of just showing it…)

select dbms_xmlgen.getxmltype('select * from t where pk = 1') from dual;

{ROWSET}
 {ROW}
  {PK}1{/PK}
  {VAR}2{/VAR}
  {RW}FF{/RW}
  {NUM}4,5{/NUM}
  {DTE}2019-08-01 10:32{/DTE}
  {TS}2019-08-01 10:32:56,786646{/TS}
  {TSZ}2019-08-01 10:32:56,786646 -04:00{/TSZ}
  {TSL}2019-08-01 16:32:56,786646{/TSL}
  {IDS}+01 02:03:04.000000{/IDS}
  {IYM}+01-01{/IYM}
 {/ROW}
{/ROWSET}

As you can see, the entire content is wrapped in a ROWSET tag and each row is wrapped in a ROW tag. Within each row, a tag with the column name encloses the column value.

Query with one row

We can generate SQL data from our XML using a generic XMLTABLE clause:

select * from xmltable(
  'ROWSET/ROW/*'
  passing dbms_xmlgen.getxmltype('select * from t where pk = 1')
  columns
    col_name varchar2(30) path 'name()',
    col_val varchar2(100) path 'text()' -- after study, more correct than '.'
);
COL_NAME COL_VAL
PK 1
VAR 2
RW FF
NUM 4,5
DTE 2018-05-18 12:21:33
TS 2018-05-18 12:21:33,523620
TSZ 2018-05-18 12:21:33,523620 +02:00
TSL 2018-05-18 12:21:33,523620
IDS +01 02:03:04
IYM +01-01

 

Watch out! My session’s NLS parameters were used by DBMS_XMLGEN to convert numbers and datetime data to strings, including the comma as a decimal marker because I am in France. Note I changed my timestamp display parameters to show fractional seconds – yes with a comma as decimal “point”…

Query with multiple rows

If there are multiple rows, we can mark each row with an increasing counter – and while we’re at it, we’ll do the same for each column.

select rn, cn, col_name, col_val from xmltable(
  'ROWSET/ROW' passing dbms_xmlgen.getxmltype('select * from t')
  columns
    rn for ordinality,
    xmldata xmltype path '*'
) a
, xmltable('*' passing a.xmldata
  columns
    cn for ordinality,
    col_name varchar2(30) path 'name()',
    col_val varchar2(100) path 'text()' -- after study, more correct than '.'
);
RN CN COL_NAME COL_VAL
1 1 PK 1
1 2 VAR 2
1 3 RW FF
1 4 NUM 4,5
1 5 DTE 2018-05-18 12:21:33
1 6 TS 2018-05-18 12:21:33,523620
1 7 TSZ 2018-05-18 12:21:33,523620 +02:00
1 8 TSL 2018-05-18 12:21:33,523620
1 9 IDS +01 02:03:04
1 10 IYM +01-01
2 1 PK 2
2 2 VAR 2
2 3 RW FF
2 4 NUM 4,5
2 5 DTE 2018-05-18 12:21:33
2 6 TS 2018-05-18 12:21:33,523620
2 7 TSZ 2018-05-18 12:21:33,523620 +02:00
2 8 TSL 2018-05-18 12:21:33,523620
2 9 IDS +01 02:03:04
2 10 IYM +01-01

 

Update: XMLTYPE(CURSOR(…))

Thanks to Iudith Mentzel, I (re)learned that XMLTYPE can take a REF CURSOR as input. This is great news, because with a REF CURSOR the input query can contain bind variables. With version 12.2.0.1 there is a bug when mixing XMLTYPE(CURSOR(…)) with XMLTABLE – if the path contains ‘.’ ! There is no problem on livesql with version 18c.

By using ‘text()’, which finally seems to be the correct path to use anyway, I get the desired results:

select rn, cn, col_name, col_val from xmltable(
  'ROWSET/ROW' passing xmltype(cursor(select * from t))
  columns
    rn for ordinality,
    xmldata xmltype path '*'
) a
, xmltable('*' passing a.xmldata
  columns
    cn for ordinality,
    col_name varchar2(30) path 'name()',
    col_val varchar2(100) path 'text()'
);

Summary

Using DBMS_XMLGEN (or better, XMLTYPE(CURSOR(…)) and XMLTABLE, we can “print” tables one row per column, with a row counter, a column counter, the column name and the column value as a string. Only one SQL statement is required; it produces a result set that, compared to DBMS output, is much easier to massage further or to display in an APEX application.

Hope this helps!

8 thoughts on “Print a table with one column name + value per row

  1. Hello Mr.Stew,

    Excellent :):)

    As an alternative to dbms_xmlgen.getxmltype(‘select * from t’),
    you can use the XMLTYPE constructor directly, the overload with a cursor parameter: XMLTYPE( CURSOR ( select * from t ) ).

    I suppose that the behavior versus the current NLS settings should be the same.

    Cheers & Best Regards,
    Iudith Mentzel

    • Hello Iudith,

      Thanks for your praise and very sharp remark. XMLTYPE looked very promising, since the CURSOR construct allows for bind variables.

      Unfortunately, when I tested it (on the 12.2.0.1 Developer VM), I got
      ORA-00600: internal error code, arguments: [qkebCreateColInFro:1], [], [], [], [], [], [], [], [], [], [], []

      I have no access to support to investigate further.

      Best regards,
      Stew

  2. Hello Stew,

    I performed a small test in LiveSQL, and it does indeed work with a bind variable:

    DECLARE
    l_pk NUMBER := 1;
    BEGIN

    FOR rec IN
    (

    select rn, cn, col_name, col_val
    from xmltable(
    ‘ROWSET/ROW’ passing xmltype( cursor(select * from t where pk = l_pk) )
    columns
    rn for ordinality,
    xmldata xmltype path ‘*’
    ) a
    , xmltable(‘*’ passing a.xmldata
    columns
    cn for ordinality,
    col_name varchar2(30) path ‘name()’,
    col_val varchar2(100) path ‘.’
    )
    )

    LOOP
    DBMS_OUTPUT.put_line( rec.rn||’ – ‘||rec.cn||’ – ‘||rec.col_name||’ – ‘|| rec.col_val );
    END LOOP;

    END;
    /

    1 – 1 – PK – 1
    1 – 2 – VAR – 2
    1 – 3 – RW – FF
    1 – 4 – NUM – 4.5
    1 – 5 – DTE – 26-MAY-18
    1 – 6 – TS – 26-MAY-18 12.21.46.521483 PM
    1 – 7 – TSZ – 26-MAY-18 12.21.46.521483 PM +00:00
    1 – 8 – TSL – 26-MAY-18 05.21.46.521483 AM
    1 – 9 – IDS – +01 02:03:04.000000
    1 – 10 – IYM – +01-01

    Does this mean that the Oracle version included with the Developer VM does have some limitations ?

    Currently I only have access to LiveSQL … I am still considering the basically two variants of having a full environment at hand,
    namely the Developer VM or a full database installation on my Windows PC …

    At this moment, it looks to me that the soon coming 18c XE is the most promising, being a full featured version :)
    since working in a Linux environment, either with Developer VM or with a Docker image, does not exactly appeal to me …
    I think that it still requires a more thorough knowledge of the Linux OS than I am able to achieve :(

    Cheers & Best Regards,
    Iudith

    • Iudith,

      Yes, XMLTYPE with the CURSOR() function works with bind variables. That is true in version 12.2.0.1 in my VM and on livesql in version 18.1.0.0.0 on livesql.

      My specific example works on livesql (version 18.1.0.0.0) but not in the Developer VM (version 12.2.0.1). However, if I change one line of code it works!

      select rn, cn, col_name, col_val from xmltable(
      ‘ROWSET/ROW’ passing xmltype(cursor(select * from t))
      columns
      rn for ordinality,
      xmldata xmltype path ‘*’
      ) a
      , xmltable(‘*’ passing a.xmldata
      columns
      cn for ordinality,
      col_name varchar2(30) path ‘name()’,
      col_val varchar2(100) path ‘text()’ — instead of ‘.’
      );

      I am inclined to think there is a bug in the older version that is fixed in the newer.

      Best regards, Stew

  3. Hello Stew,

    If already at this point:

    I am not in anyway an XML specialist, therefore I gave a “little thought” to the path ‘.’ used in your SELECT.

    As by the “pure xml theory” definition, the ‘.’ was supposed to return the current context element itself, which is the element
    generated by XMLTABLE(‘*’ passing a.xmldata … ), with the element tags included.

    So, I think that whether the ” path ‘.’ ” works as expected or not depends on the XML implementation in that specific environment.

    In my opinion, it can only work where this “special path” is interpreted by default as returning the text inside the target (current context) element and not the element itself (with the element tags included).

    Using path ‘text()’, instead, does explicitly return the “text()” function applied to each element generated by XMLTABLE,
    just like using path “name()” does explicitly return its name.

    I don’t wonder that all the XML documentations I have encountered are kind of “not exactly precise”,
    and the Oracle XML documentation is not an exception.

    I have the feeling that I will never completely master this XML stuff.

    Thanks a lot & Best Regards,
    Iudith

    • Iudith,

      After a bit of reading and testing, yes ‘text()’ seems to be the correct path expression to use anyway, so the bug is irrelevant.

      I have learned a lot thanks to you!

      Best regards,
      Stew

  4. The query worked great but do not pick the column which null value. Could you tell how to handle it?

    • Hi KV,

      To get special handling of NULL, you have to use a DBMS_XMLGEN context. For example:

      with function xml_with_empty_tags(p_sql in varchar2)
      return xmltype is
        qryCtx DBMS_XMLGEN.ctxHandle;
        output clob;
      BEGIN
        qryCtx := DBMS_XMLGEN.newContext(p_sql);
        DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.EMPTY_TAG);
        output := DBMS_XMLGEN.getXML(qryCtx);
        DBMS_XMLGEN.closeContext(qryCtx);
        return xmltype(output, null, 1, 1);
      end xml_with_empty_tags;
      select rn, cn, col_name, col_val from xmltable(
        'ROWSET/ROW' passing xml_with_empty_tags('select dummy, null nil from dual')
        columns
          rn for ordinality,
          xmldata xmltype path '*'
      ) a
      , xmltable('*' passing a.xmldata
        columns
          cn for ordinality,
          col_name varchar2(30) path 'name()',
          col_val varchar2(100) path 'text()'
      );
      
      RN   CN   COL_NAME   COL_VAL   
      1     1   DUMMY      X          
      1     2   NIL               

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