Sokrates on Oracle

Archive for the ‘Allgemein’ Category

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 ) 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 ) 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)
  ret clob;
  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) || 
          ') as ' || trim(both '"' from tab_to_char.p_tab.column(i) ||
	  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');

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)
  ret clob;
  ret := 'select 
  (select * from (select rownum as "ROW", t.* from tab_to_char(p_tab) t)) 
  unpivot include nulls (
    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) 
	  || ' as ''' || trim(both '"' from print_table.p_tab.column(i) || '''' ||
	  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);

---------- ---------- ---------------------------------------------
         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);

---------- ---------- ---------------------------------------------
         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 !

Posted in Allgemein | Leave a Comment »

Find first n gaps in integer primary key

Posted by Matthias Rogel on 13. September 2016


sokrates@12.1 > create table t( pk int primary key check(pk > 0));

Table created.

sokrates@12.1 > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t
sokrates@12.1 > select trunc(dbms_random.value(1, 1e5)) from dual
sokrates@12.1 > connect by level <= 1e5 
sokrates@12.1 > /

63187 rows created.

Finding the first n gaps

sokrates@12.1 > variable n number
sokrates@12.1 > exec :n := 1000

PL/SQL procedure successfully completed.

sokrates@12.1 > set autotr traceonly timi on
sokrates@12.1 > with
sokrates@12.1 > gaps(g, counter, isgap) as
sokrates@12.1 > (
sokrates@12.1 >    select 0, 1, cast(null as varchar2(1)) from dual
sokrates@12.1 >    union all
sokrates@12.1 >    select
sokrates@12.1 >       gaps.g + 1,
sokrates@12.1 >       gaps.counter + case when is null then 1 else 0 end,
sokrates@12.1 >       case when is null then 'x' end
sokrates@12.1 >    from gaps, t
sokrates@12.1 >    where gaps.counter <= :n
sokrates@12.1 >    and = gaps.g + 1
sokrates@12.1 > )
sokrates@12.1 > search breadth first by g asc set o
sokrates@12.1 > cycle g set is_cycle to 1 default 0
sokrates@12.1 > select
sokrates@12.1 >    gaps.g
sokrates@12.1 > from
sokrates@12.1 >    gaps
sokrates@12.1 > where
sokrates@12.1 >    gaps.isgap = 'x'
sokrates@12.1 > /

1000 rows selected.

Elapsed: 00:00:00.12

Execution Plan
Plan hash value: 3013247790

| Id  | Operation                                 | Name         | Rows  | Bytes | Cost (%CPU)| Time  |
|   0 | SELECT STATEMENT                          |              |     2 |    30 |     5  (20)| 00:00:01 |
|*  1 |  VIEW                                     |              |     2 |    30 |     5  (20)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|              |       |       |            |       |
|   3 |    FAST DUAL                              |              |     1 |       |     2   (0)| 00:00:01 |
|   4 |    NESTED LOOPS OUTER                     |              |     1 |    39 |     2   (0)| 00:00:01 |
|*  5 |     RECURSIVE WITH PUMP                   |              |       |       |            |       |
|*  6 |     INDEX UNIQUE SCAN                     | SYS_C0087690 |     1 |    13 |     0   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   1 - filter("GAPS"."ISGAP"='x')
   5 - filter("GAPS"."COUNTER"<=TO_NUMBER(:N))
   6 - access("T"."PK"(+)="GAPS"."G"+1)

   - dynamic statistics used: dynamic sampling (level=2)

          0  recursive calls
          0  db block gets
       2656  consistent gets
          0  physical reads
          0  redo size
       9313  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       2635  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Posted in Allgemein, sql | 3 Comments »

Nominated for Oracle Database Developer Choice Awards (SQL) – San Francisco calling

Posted by Matthias Rogel on 2. October 2015

I don’t know why and by whom, but I was nominated for the Oracle Database Developer Choice Awards – SQL Category as one of eight SQL developers.


Cite from


The Oracle Database Developer Choice Awards celebrate and recognize technical expertise and contributions in the Oracle Database community. As longtime and new users of Oracle Database move to the Cloud and take advantage of this exciting new architecture, community experts will play a critical role in helping them succeed.

, so I am quite happy to be part of such an illustrious selection.

Until 15th of October everyone with an otn-account can still vote on

I know Stew, Kim and Sayan quite well from “the community” and so I am aware of the fact they deserve it more than me, but anyway – you can still vote for me ( I’m quite sure I’m the sexiest of the candidates – for this reason I already was always chosen as class representative in school 😉 )

Nice side effect: somehow my boss got wind of the whole thing and so he will send me to OOW 2015 regardless of the result of the voting.

So, looking quite forward to go and hopefully meet quite a lot of  you guys there !

Posted in Allgemein | Leave a Comment »

Overview of all time changes this year via SQL

Posted by Matthias Rogel on 30. March 2015

inspired by Laurent Schneider’s remark concerning special time changes in Lord Howe Island

with dates as
trunc(sysdate, 'year') + level - 1 as day
connect by
extract(year from trunc(sysdate, 'year') + level - 1) = extract(year from trunc(sysdate, 'year'))

timezones as
vtn.TZNAME, listagg(vtn.TZABBREV, ', ') within group(order by vtn.tzabbrev) tzabbrevs
v$timezone_names vtn
group by

offsets as
24 * (cast(from_tz(cast( as timestamp), 'UTC') at time zone v.TZNAME as date) - as offset
timezones v, dates d

changes as
d.*, d.loffset - d.offset offset_change,
case when d.loffset > d.offset then 'DST start' else 'DST end' end as time_change
o.*, lead(o.offset) over(partition by o.tzname order by loffset
from offsets o
) d
where d.offset != d.loffset
max(case c.time_change when 'DST start' then end) as DST_start,
rtrim(to_char(max(case c.time_change when 'DST start' then c.offset_change end), 'S90.99'), '0.') || ' hour' as time_change_DST_start,
'UTC' || rtrim(to_char(max(case time_change when 'DST start' then c.loffset end), 'S90.99'), '0.') || ' hour' as offset_after_DST_Start,
max(case c.time_change when 'DST end' then end) as DST_end,
rtrim(to_char(max(case c.time_change when 'DST end' then c.offset_change end), 'S90.99'), '0.') || ' hour' as time_change_DST_end,
'UTC' || rtrim(to_char(max(case time_change when 'DST end' then c.loffset end), 'S90.99'), '0.') || ' hour' as offset_after_DST_end,
changes c
group by
c.tzname, c.tzabbrevs
order by
dst_start, c.tzname

Posted in Allgemein | Tagged: | Leave a Comment »

Best Practice in 12c

Posted by Matthias Rogel on 4. December 2013

Since PL/SQL now is closely integrated into SQL, we hence can happily state

sokrates@12.1 > with function bestpractice return varchar2
  2  is
  3  begin
  4     return 'Do not use PL/SQL when it can be done with SQL alone !';
  5  end bestpractice;
  6  select bestpractice() from dual
  7  /

Do not use PL/SQL when it can be done with SQL alone !

Posted in 12c, Allgemein, fun, sql | Tagged: | 2 Comments »

Strange ORA-14196

Posted by Matthias Rogel on 7. October 2013

It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.

sokrates@11.2 > create table strange(i int not null, j int not null);

Table created.

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
  2  using index ( create unique index struix on strange ( i, j ) )
  3  /
alter table strange add constraint unique_i unique(i) not deferrable
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

We have to create a non-unique index here !

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
  2  using index ( create  index struix on strange ( i, j ) )
  3  /

Table altered.

Also reproduced on 12.1.
Who can explain this behaviour to me ( I suppose it is a bug ) ?

Posted in Allgemein | Tagged: | 3 Comments »

Partition Info in V$SESSION_LONGOPS

Posted by Matthias Rogel on 10. May 2013

Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but only to one (sub-)partition of it.
I filed an enhancement request several years ago concerning this matter, but never received any feedback.
However, there is a workaround to that. In many cases, we can find out on which (sub-) partition the longop is working on: V$SESSION_WAIT’s P1- and P2-info can be used for that in case the session is waiting mainly on I/O ( which might be most likely for many systems. )
Here is an extension to V$SESSION_LONGOPS which tries to figure out this additional info.

Update 27/02/2014
Note that the original version has been improved by Jonathan Lewis. I have marked the relevant part with a corresponding comment.
I haven’t observed so far that I wasn’t able to get the partition information from v$session.row_wait_obj# ( as suggested by him ), but from the part marked as “superfluous most likely” ( my original version ). However, I have no proof that this is not possible.

            select 'does not apply'
            from dual
            where slo.TARGET not like '%.%'
            or slo.TARGET is null
            select 'does not apply'
            from dba_tables dt
            where dt.OWNER=substr(, 1, instr(, '.') - 1)
            and dt.TABLE_NAME=substr(, instr(, '.') + 1)
            and dt.PARTITIONED='NO'       
            -- Jonathan Lewis, see
               ob.subobject_name || ' (' || ob.object_type || ')'
            from v$session s, dba_objects ob
              ob.object_id = s.row_wait_obj#
            and s.sid = slo.sid
            and ob.OBJECT_TYPE like '%PARTITION%'            
            -- superfluous most likely
               de.partition_name || ' (' || de.segment_type || ') NOT SUPERFLUOUS IF YOU SEE THAT'
            from v$session_wait sw, dba_extents de
            and slo.opname like '%Scan%'
            and sw.P1TEXT like 'file%'
            and sw.P1 = de.FILE_ID and sw.P2 between de.BLOCK_ID and de.BLOCK_ID + de.BLOCKS - 1
            and de.owner = substr(, 1, instr(, '.') - 1)
            and de.segment_type in
               'INDEX PARTITION', 'INDEX SUBPARTITION'            
            and de.segment_name in
                 -- table
                    substr(, instr(, '.') + 1)
                 from dual
                 union all
                 -- index
                 select di.index_name
                 from dba_indexes di
                 where di.owner=substr(, 1, instr(, '.') - 1)
                 and di.TABLE_NAME = substr(, instr(, '.') + 1)
   as partition_info,     
from v$session_longops slo
where slo.TIME_REMAINING > 0

Note that this might take a bit longer than a simple

select slo.*
from v$session_longops slo
where slo.TIME_REMAINING > 0

, though due to coalesce’s short circuiting it is quite efficient.

Posted in Allgemein, sql | Tagged: | 3 Comments »


Posted by Matthias Rogel on 29. April 2013

Tim wrote
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.

Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2

Here is a try to write one, fixes and improvements are welcome !

create or replace function CAST_TO_DATE(bdr in raw) return date deterministic is
         100 * (to_number(substr(bdr,1,2), 'xx') - 100) + 
         to_number(substr(bdr,3,2), 'xx') - 101, 
     + NUMTOYMINTERVAL(to_number(substr(bdr,5,2), 'xx')-1, 'month')
     + NUMTODSINTERVAL(to_number(substr(bdr,7,2), 'xx')-1, 'day')
     + NUMTODSINTERVAL(to_number(substr(bdr,9,2), 'xx') - 1, 'hour')   
     + NUMTODSINTERVAL(to_number(substr(bdr,11,2), 'xx') - 1, 'minute')   
     + NUMTODSINTERVAL(to_number(substr(bdr,13,2), 'xx') - 1, 'second');
  exception when others then return to_date(1, 'J');
end CAST_TO_DATE;   

Posted in Allgemein | Tagged: | 4 Comments »

A simple pipelined version of print_table

Posted by Matthias Rogel on 10. April 2013

Tom Kyte’s print_table procedure, available on
seems to be very popular and there exist tricky variations on the theme, for example the following nice xml-trick by Sayan Malakshinov.

Please note that it is very easy to use the existing print_table-code to generate a pipelined version which can be used in SQL.
I use the following code since ages and it always does me a great job, so probably it is worth sharing.

create or replace function fprint_table
( p_query in varchar2,
  p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
return sys.odcivarchar2list 
authid current_user
l varchar2(4000);
s integer default 1;
  dbms_output.enable(buffer_size => null);
     p_query => p_query,
     p_date_fmt => p_date_fmt

     dbms_output.get_line(line => l, status => s);
     exit when s != 0;
        pipe row(l);
     exception when no_data_needed then exit;
  end loop;

end fprint_table;

sokrates@11.2 > select * from table(fprint_table('select user,sysdate from dual'));

USER                          : SOKRATES
SYSDATE                       : 10-apr-2013 12:27:50
1 row selected.

Posted in Allgemein | Tagged: | Leave a Comment »

An undocumented restriction in Workspace Manager – exporting tables with valid time support

Posted by Matthias Rogel on 7. February 2013

If you are using Workspace Manager, it could be probably useful to know, that there is an undocumented restriction concerning import/export.
Due to Import and Export Considerations,
…Workspace Manager supports the import and export of version-enabled tables in one of the following two ways: a full database import and export, and a workspace-level import and export through Workspace Manager procedures. No other export modes, such as schema, table, or partition level, are currently supported….

However, this does not hold for tables with valid time support:

sokrates@11.2 > CREATE TABLE d (id NUMBER PRIMARY KEY);

Table created.

sokrates@11.2 > EXECUTE DBMS_WM.EnableVersioning (table_name=>'D', validTime=>TRUE, hist => 'NONE');

PL/SQL procedure successfully completed.

sokrates@11.2 >  EXECUTE DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE');
BEGIN DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE'); END;

ERROR at line 1:
ORA-20171: WM error: Export not supported on a table with valid time
ORA-06512: at "WMSYS.LT", line 13185
ORA-06512: at line 1

Support confirmed, that in this case only full db import/export (!) is supported, documentation would be updated somewhen.

Posted in Allgemein | Tagged: , , | Leave a Comment »