Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1

  2  (c1 char,

  3   c2 char(1))

  4  /

 

Table created.

 

SQL> desc t1

Name                       Null?    Type

-------------------------- -------- ------------------

C1                                  CHAR(1)

C2                                  CHAR(1)

 

SQL> 

However, if you rely on defaults like this and the software supplier changes them, you could be left with an application which does not work. 

ORA-01441

This was tested on Oracle 11.2. I created a table with one VARCHAR2 column, which was 15 characters long:

SQL> create table tab1 (col1 varchar2(15))

  2  /

 

Table created.

SQL>

I inserted one row, which was 11 characters in length:

SQL> insert into tab1 values ('Christopher')

  2  /

 

1 row created.

 

SQL> select * from tab1

  2  /

 

COL1

---------------

Christopher

SQL> 

I tried to make the column 10 characters long. This failed, as you might expect, because the row I added earlier had 11 characters: 

SQL> alter table tab1 modify col1 varchar2(10)

  2  /

alter table tab1 modify col1 varchar2(10)

                        *

ERROR at line 1:

ORA-01441: cannot decrease column length because some

value is too big

SQL>

I found the row which was too long, made it a bit shorter then I was able to alter the table successfully. This seems reasonable as VARCHAR2 data is variable length:

SQL> select col1 from tab1 where length(col1) > 10

  2  /

 

COL1

---------------

Christopher

 

SQL> update tab1 set col1 = 'Chris'

  2  where col1 = 'Christopher'

  3  /

 

1 row updated.

 

SQL> alter table tab1 modify col1 varchar2(10)

  2  /

 

Table altered.

 

SQL> select * from tab1

  2  /

 

COL1

----------

Chris

 

SQL>

I did a similar test with a CHAR column. I found that I could not modify it at all unless the column was null. I guess this is because CHAR data is fixed length: 

SQL> create table tab1 (col1 char(15))

  2  /

 

Table created.

 

SQL> insert into tab1 values ('Chris')

  2  /

 

1 row created.

 

SQL> select * from tab1

  2  /

 

COL1

---------------

Chris

 

SQL> alter table tab1 modify col1 char(10)

  2  /

alter table tab1 modify col1 char(10)

                        *

ERROR at line 1:

ORA-01441: cannot decrease column length because some

value is too big

 

SQL> update tab1 set col1 = null

  2  /

 

1 row updated.

 

SQL> alter table tab1 modify col1 char(10)

  2  /

 

Table altered.

 

SQL>

 

VSIZE Function

This example demonstrates the use of the VSIZE function, which tells you how much space Oracle needs to store a value. VARCHAR2 columns are variable length so the space required depends on the length of the value in the column. CHAR columns are fixed length so the space required depends on the column definition. DATE columns always seem to take up 7 bytes. NUMBER columns often need fewer characters to store than you might think (I will try to look at this further in a future post):

SQL> create table emp
  2  (first_name varchar2(10),
  3   surname    char(10),
  4   hire_date  date,
  5   salary     number)
  6  /

Table created.

SQL> insert into emp values
  2  ('Joe', 'Bloggs', sysdate, 1000000)
  3  /

1 row created.

SQL> insert into emp values
  2  ('John', 'Smith', sysdate-1000, 1234567)
  3  /

1 row created.

SQL> insert into emp values
  2  ('Andrew', 'Reid', sysdate-2000, 9999999)
  3  /

1 row created.

SQL> select first_name, vsize(first_name)
  2  from emp
  3  /

FIRST_NAME VSIZE(FIRST_NAME)
---------- -----------------
Joe                        3
John                       4
Andrew                     6

SQL> select surname, vsize(surname)
  2  from emp
  3  /

SURNAME    VSIZE(SURNAME)
---------- --------------
Bloggs                 10
Smith                  10
Reid                   10

SQL> select hire_date, vsize(hire_date)
  2  from emp
  3  /

HIRE_DATE VSIZE(HIRE_DATE)
--------- ----------------
09-FEB-12                7
15-MAY-09                7
19-AUG-06                7

SQL> select salary, vsize(salary)
  2  from emp
  3  /

    SALARY VSIZE(SALARY)
---------- -------------
   1000000             2
   1234567             5
   9999999             5

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>