VARCHAR and VARCHAR2

If you create a table with a VARCHAR column in Oracle 11.2, Oracle sets it up as a VARCHAR2:

SQL> l

  1  create table t1

  2  (c1 varchar(1),

  3*  c2 varchar2(1))

SQL> /

 

Table created.

 

SQL> desc t1

Name                       Null?    Type

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

C1                                  VARCHAR2(1)

C2                                  VARCHAR2(1)

 

SQL>

According to a book I am working through, this has been the case since Oracle 8. However, Oracle say you should not rely on this as, in a future release, the specifications for VARCHAR and VARCHAR2 may diverge.

Justify Left, Centre and Right

This was tested on Oracle 11.2. NUMBER items and their column headings are right justified by default:

 

SQL> set numwidth 15

SQL> select count(*) table_count from dba_tables

  2  /

 

    TABLE_COUNT

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

           3110

 

SQL>

 

If you want to see the heading in the centre of the column, you can do so with justify centre:

 

SQL> col table_count justify centre

SQL> select count(*) table_count from dba_tables

  2  /

 

  TABLE_COUNT

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

           3110

 

SQL>

 

If you want to see the heading to the left of the column, you can do so with justify left:

 

SQL> col table_count justify left

SQL> select count(*) table_count from dba_tables

  2  /

 

TABLE_COUNT

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

           3110

 

SQL>

 

VARCHAR2 items and their column headings are left justified by default:

 

SQL> select table_name from dba_tables

  2  where rownum = 1

  3  /

 

TABLE_NAME

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

CON$

 

SQL>

 

If you want to see the heading in the centre of the column, you can do so with justify centre:

 

SQL> col table_name justify centre

SQL> select table_name from dba_tables

  2  where rownum = 1

  3  /

 

          TABLE_NAME

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

CON$

 

SQL>

 

If you want to see the heading to the right of the column, you can do so with justify right:

 

SQL> col table_name justify right

SQL> select table_name from dba_tables

  2  where rownum = 1

  3  /

 

                    TABLE_NAME

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

CON$

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>

 

Simple INSERT Statements

This was tested on Oracle 11.2. Before I could can do any INSERT statements, I needed to create a table:

SQL> create table source

  2  (col1 varchar2(1),

  3   col2 varchar2(1))

  4  /

Table created. 

SQL>

Then I tried various single-row INSERT statements. Oracle converted the NUMBER columns (i.e. the ones without quotes) into VARCHAR2 format with or without the TO_CHAR function:

SQL> insert into source

  2  (col1,col2) values(to_char(1),'2')

  3  /

1 row created.

SQL> insert into source values('3',4)

  2  /

1 row created.

SQL> insert into source (col1,col2)

  2  select 5,'6' from dual

  3  /

1 row created.

SQL> insert into source

  2  select '7',to_char(8) from dual

  3  /

1 row created.

SQL> select * from source

  2  /

C C

- -

1 2

3 4

5 6

7 8 

SQL> 

Next I created an empty copy of the table:

SQL> create table target

  2  as select * from source

  3  where 1=2

  4  /

Table created. 

SQL> 

... and copied the rows from SOURCE to TARGET with 2 different multi-row INSERT statements:

SQL> insert into target select * from source

  2  /

4 rows created.

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

4 rows created. 

SQL> 

I dropped a column from the SOURCE table:

SQL> alter table source drop column col2

  2  /

Table altered. 

SQL> 

... and both the INSERT statements then failed albeit with different error messages:

SQL> insert into target select * from source

  2  /

insert into target select * from source

            *

ERROR at line 1:

ORA-00947: not enough values

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

select col1,col2 from source

            *

ERROR at line 2:

ORA-00904: "COL2": invalid identifier

SQL>

I replaced the dropped column and added an extra one. In my experience, adding columns is far more common than dropping columns:

SQL> alter table source add

  2  (col2 varchar2(1),

  3   col3 varchar2(1))

  4  /

Table altered.

SQL>

Then only the first INSERT failed:

SQL> insert into target select * from source

  2  /

insert into target select * from source

            *

ERROR at line 1:

ORA-00913: too many values

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

4 rows created. 

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 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>