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 select table_name, column_name, default_length, data_default from user_tab_columns where table_name in ('T1', 'T2') order by table_name, column_name ; TABLE_NAME COLUMN_NAME DEFAULT_LENGTH DATA_DEFAULT -------------------- -------------------- -------------- ---------------------------------- 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:
declare v1 varchar2(32); begin 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; dbms_output.put_line( r.table_name || ' ' || r.column_name || ' ' || r.default_length || ' ' || ascii(substr(v1,r.default_length)) ); end loop; end; / 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
Lagniappe
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.
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 |
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
https://github.com/jkstill/hexadecimal/tree/master/hexdump
Comment by jkstill — February 1, 2021 @ 5:55 pm GMT Feb 1,2021 |
Jared,
Thanks for the offering
Regards
Jonathan Lewis
Comment by Jonathan Lewis — February 2, 2021 @ 3:33 pm GMT Feb 2,2021 |
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 |
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).
Regards
Jonathan Lewis
Comment by Jonathan Lewis — February 2, 2021 @ 3:48 pm GMT Feb 2,2021 |