Syntax formatter might change your data

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>
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  /
X-1
Y-3
Z-

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.

3 Comments on “Syntax formatter might change your data

  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.
    Cheers
    Philipp

  2. Pingback: Commas at the beginning or end of a sql code line « Oracle DBA – A lifelong learning experience

  3. 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);
    commit;

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

    X LENGTH(X) Y LENGTH(Y) Z LENGTH(Z)
    ---------- ---------- ---------- ---------- ---------- ----------
    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:

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

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

%d bloggers like this: