Saturday, September 08, 2012

Zero Length Columns?

This was tested on Oracle 11.1.0.6.0 on Windows XP. While researching something completely different on the Internet, I came across a reference to zero length columns. This seemed strange to me. Why would you ever want such a thing?

Then I wondered what would happen if you tried to have a column with a length of -1 but Oracle told me it was too long!

Finally I tried to create a column which was 1.5 characters long. This time the error message was more helpful:

 SQL> create table andrew
  2  (short_column varchar2(0))
  3  /
(short_column varchar2(0))
                        *
ERROR at line 2:
ORA-01723: zero-length columns are not allowed

SQL> ed
Wrote file afiedt.buf

  1  create table andrew
  2* (short_column varchar2(-1))
SQL> /
(short_column varchar2(-1))
                       *
ERROR at line 2:
ORA-00910: specified length too long for its datatype

SQL> ed
Wrote file afiedt.buf

  1  create table andrew
  2* (short_column varchar2(1.5))
SQL> /
(short_column varchar2(1.5))
                       *
ERROR at line 2:
ORA-02017: integer value required

SQL>

No comments: