Oracle is just "wagering" that very-long variable-length strings are rarely filled. I can create longer and longer varchar columns -- but the same data_length results:
SQL> create table bvar ( b100 varchar2(100 char), b1000 varchar2(1000 char), b2000 varchar2(2000 char), b4000 varchar2(4000 char));
Table created.
Elapsed: 00:00:00.11
SQL> select column_name, data_length, char_length, data_length/char_length from user_tab_columns where table_name = 'BVAR' order by column_id;
COLUMN_NAME DATA_LENGTH CHAR_LENGTH DATA_LENGTH/CHAR_LENGTH
----------------------------------- ----------- ----------- -----------------------
B100 400 100 4
B1000 4000 1000 4
B2000 4000 2000 2
B4000 4000 4000 1
However, when Oracle knows the string will be filled (CHAR data type) it refused to go over 2000 characters:
SQL> create table bfix ( b100 char(100 char), b1000 char(1000 char), b2000 char(2000 char));
Table created.
SQL> select column_name, data_length, char_length, data_length/char_length from user_tab_columns where table_name = 'BFIX' order by column_id;
COLUMN_NAME DATA_LENGTH CHAR_LENGTH DATA_LENGTH/CHAR_LENGTH
----------------------------------- ----------- ----------- -----------------------
B100 400 100 4
B1000 2000 1000 2
B2000 2000 2000 1
SQL> create table b2001 ( b2001 char(2001 char));
create table b2001 ( b2001 char(2001 char))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
The 12c documentation says:
"Independently of the maximum length in characters, the length of VARCHAR2 data cannot exceed:
• 32767 bytes if MAX_STRING_SIZE = EXTENDED
• 4000 bytes if MAX_STRING_SIZE = STANDARD"
https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021
November 17, 2016 - 2:49 am UTC
True, but this is not the same as saying "I can set varchar2 to any length I want and everything stays the same".
A client program requesting a varchar2(1000) column (or array of them) will allocate more memory than one requesting a varchar2(10) column.