Oracle Scratchpad

February 1, 2021


Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 4:50 pm GMT Feb 1,2021

Here’s a quirky little detail – probably totally irrelevant to virtually everyone – that came up in a question on the Oracle Developer Forum a couple of days ago. It concerns the way Oracle stores and records default values for columns, and it also does a hat-tip to the “commas at the start/end of the line” argument. Here’s a little script to create two identical tables:

create table t1 (
        t1 timestamp default '01-Jan-2021 12:00:00'
,       t2 timestamp default '01-Jan-2021 12:00:00'
,       t3 timestamp default '01-Jan-2021 12:00:00'

create table t2 (
        t1 timestamp default '01-Jan-2021 12:00:00',
        t2 timestamp default '01-Jan-2021 12:00:00',
        t3 timestamp default '01-Jan-2021 12:00:00')

Here’s a query to check that we’ve set the defaults correctly, followed by the output:

break on table_name skip 1
set linesize 180

spool default_length.lst

        table_name, column_name, default_length, data_default
        table_name in ('T1', 'T2')
order by

-------------------- -------------------- -------------- ----------------------------------
T1                   T1                               23 '01-Jan-2021 12:00:00'
                     T2                               23 '01-Jan-2021 12:00:00'
                     T3                               23 '01-Jan-2021 12:00:00'

T2                   T1                               22 '01-Jan-2021 12:00:00'
                     T2                               22 '01-Jan-2021 12:00:00'
                     T3                               22 '01-Jan-2021 12:00:00'

It would appear that we have the same default values set for the columns – but for table t1 the length of the default values is 23, while for table t2 it’s only 22. How strange, how do we investigate what’s going on.

A check of the view user_tab_columns tells us that data_default is a long column so we can’t dump() it, and we can’t substr() it. We could dump the relevant block from sys.col$, but rather than do that I’ll write a little PL/SQL block that reads the long into a PL/SQL varchar2() and outputs the last byte:

        v1 varchar2(32);
        for r in (
                select  table_name, column_name, default_length, data_default
                from    user_tab_columns
                where   table_name in ('T1','T2')
        ) loop
                v1 := r.data_default;
                        r.table_name || ' ' ||
                        r.column_name || ' ' ||
                        r.default_length || ' ' ||
        end loop;

T1 T1 23 10
T1 T2 23 10
T1 T3 23 10
T2 T1 22 39
T2 T2 22 39
T2 T3 22 39

The last character of data_default for the t1 table (with length 23) is chr(10) – the line-feed, while the last character for the t2 table (with length 22) is chr(39) – the single-quote character.

The text stored in the data_default column is literally the text you supplied to Oracle (it’s not an expression that is stored and evaluated at table creation time); and the text that’s stored seems to be all the text that Oracle see up to the point where a new token tells it to stop, and in the case of t1 that’s the comma after the line-feed (if you’re running on Windows you might see the length as 24 since DOS uses “carriage return – line feed” compared to the UNIX line-feed only).

Here’s another variant, just to emphasise the point, showing another table declaration and the associated output from the PL/SQL:

create table t3 (
        t1 timestamp default '01-Jan-2021 12:00:00'    
,       t2 timestamp default '01-Jan-2021 12:00:00'     ,       t3 timestamp default '01-Jan-2021 12:00:00'

T3 T1 23 10
T3 T2 27 32
T3 T3 23 10

In this case there are 5 spaces between the declaration of column t2 and the comma that separates it from the declaration of column t3. As you can see the default length is longer and the last stored byte is chr(32) – the space character


You could criticise me for not including a format string as part of my definition of the default value, so it should have been something like: to_timestamp(’01-Jan-2021 12:00:00′,’dd-mon-yyyy hh24:mi:ss’) There is, however, one drawback to this – the expression is now 62 characters long (at least), which means the default value won’t be cached in the dictionary cache (v$rowcache)- and this might introduce a parsing overhead that you would prefer to avoid.

Do take a look at comment #3 below, though.


  1. Someone at Oracle missed the lexical analysis and tokenisation 101 lecture :-)

    Comment by /* Michael D O'Shea */ (@MichaelDOShea) — February 1, 2021 @ 5:04 pm GMT Feb 1,2021 | Reply

  2. Something I have wanted for sometime is a utility to dump long/clob/blob etc, much like ‘hexdump -C’ in linux.
    Recently I created one

    The hexdump.hexdump_long procedure will do a hexdump -C like dump of a LONG.

    The file for this is hexdump.sql. There are some example scripts for calling it.
    The file hexdump_binary.sql is similar, but for BLOBs. The only thing that cannot be done directly from PL/SQL is LONG RAW

    Comment by jkstill — February 1, 2021 @ 5:55 pm GMT Feb 1,2021 | Reply

  3. Concerning your lagniappe: you could use the timestamp literal syntax: TIMESTAMP’2021-01-01 12:00:00′ which is 30 characters and caching might still apply.

    Comment by pkl — February 2, 2021 @ 7:07 am GMT Feb 2,2021 | Reply

  4. pkl,

    Thanks for the comment – it’s very easy to forget the availability of “time literals”, and it can make a difference to the optimizer in some cases.
    I should have set the columns up as timestamp with time zone: TIMESTAMP’2021-01-01 12:00:00 +2:00′ (36 characters).

    Jonathan Lewis

    Comment by Jonathan Lewis — February 2, 2021 @ 3:48 pm GMT Feb 2,2021 | Reply

  5. […] Data Defaults (Feb 2021): a quirky little detail about how column defaults can be stored. […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — July 15, 2022 @ 8:31 pm BST Jul 15,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: