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>

Maximum Permitted Size of a CHAR Column

The example below, which I ran on an Oracle 9.2.0.7.0 database, is too trivial to need explanation:

SQL> create table andrew (wide_column char(2000))
  2  /

Table created.

SQL> drop table andrew
  2  /

Table dropped.

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

SQL>

Maximum Permitted Size of a VARCHAR2 Column

The example below, which I ran on an Oracle 9.2.0.7.0 database, is too trivial to need explanation:

SQL> create table andrew (wide_column varchar2(4000))
  2  /

Table created.

SQL> drop table andrew
  2  /

Table dropped.

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

SQL>