Syntax formatter might change your data

August 12, 2016

1 minute

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 responses to “Syntax formatter might change your data”

Philipp Salvisberg

August 13, 2016 at 10:16 pm

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

Reply
Commas at the beginning or end of a sql code line « Oracle DBA – A lifelong learning experience

August 19, 2016 at 2:54 am

[…] I saw a post by Connor McDonald on how the syntax formatter – or your own hand formatted code might give the wrong results if you are npot acreful […]

Reply
Sean

August 19, 2016 at 4:47 am

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

Reply