Syntax formatter might change your data

Posted by

I saw this on an AskTom question today answered by my colleague Chris.  Check out this simple example

SQL> create table T (
  2    x int default 1,
  3    y int default 1
  4   ,z int);

Table created.

It looks like I’ve assigned a default of “1” to both X and Y. But lets now dump out the default definition from the dictionary.

SQL> set serverout on
SQL> declare
  2    longcol varchar2(200);
  3  begin
  4    for i in (
  5      select column_name, data_default
  6      from   user_tab_cols
  7      where  table_name = 'T'
  8      )
  9    loop
 10      longcol := i.data_Default;
 11      dbms_output.put_line(i.column_name||'-'||length(longcol));
 12    end loop;
 13  end;
 14  /

Notice the length of the default for Y. If we output those 3 characters (not shown here) we get “1” (the default), then a line feed, then a space.

So where you put the commas makes a difference !  Of course, ultimately (because its a numeric) the assigned value will not change.


  1. Hello Connor,
    Technically that is correct. But since this is just an expression the additional whitespace stored will never make a difference in real live.
    So it is just “ugly” that more whitespaces are stored in the dictionary than necessary. That’s it.

  2. While the metadata about the length of the default in USER_TAB_COLS may be off, the data itself is represented correctly:

    insert into t (x, y, z) values (9, 9, 9);
    insert into t (z) values (0);
    insert into t (x, y, z) values (11, 12, 13);
    insert into t (x, y, z) values (101, 102, 103);

    SQL> select x, length(x), y, length(y), z, length(z) from t;

    ---------- ---------- ---------- ---------- ---------- ----------
    9 1 9 1 9 1
    1 1 1 1 0 1
    11 2 12 2 13 2
    101 3 102 3 103 3

Got some thoughts? Leave a comment

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.