CODEWITHSUNDEEP
plsqloracle11g
Siddhartha
Siddhartha

Reputation: 491

does VARCHAR increase the capacity of the variable assigned to it?

City CHAR(10),
Street VARCHAR(10)

One thing i understood is varchar is having variable length . but what in the above case - if the name of the street is more than 10 characters - then does it increase he size again by 10 units by default ? . Please clarify it .

Upvotes: 0

Views: 207

Answers (3)

Xophmeister
Xophmeister

Reputation: 9211

No, that's not how it works. CHAR is a fixed with string: so if you put less that that length in, it is padded with, presumably, spaces; if you put more in, it is truncated throws an error. VARCHAR (and VARCHAR2 in Oracle) allocates a maximum length, but no padding will be added to the string if you don't meet that length; however, again, your input will be truncated you'll get an error if it's too big (the field won't grow automatically).

For arbitrary text input, you'd use a BLOB or CLOB type. Either that, or store the text in the filesystem somewhere and point to its path in your DB.

Upvotes: 1

Boneist
Boneist

Reputation: 23578

It's easy to create a test case to see how it works:

create table test1 (col1 char(10),
                    col2 varchar(10));

-- create data with varying lengths
insert into test1 (col1, col2)
select lpad('a', level, 'a') col1,
       lpad('a', level, 'a') col2
from   dual
connect by level <= 10;

-- see the length of the stored values for each row
select col1, col2, length(col1), length(col2)
from   test1
order by col1, col2;

COL1       COL2       LENGTH(COL1) LENGTH(COL2)
---------- ---------- ------------ ------------
a          a                    10            1
aa         aa                   10            2
aaa        aaa                  10            3
aaaa       aaaa                 10            4
aaaaa      aaaaa                10            5
aaaaaa     aaaaaa               10            6
aaaaaaa    aaaaaaa              10            7
aaaaaaaa   aaaaaaaa             10            8
aaaaaaaaa  aaaaaaaaa            10            9
aaaaaaaaaa aaaaaaaaaa           10           10

-- insert 11 characters into col1
insert into test1 (col1, col2)
values ('12345678901', null);

ORA-12899: value too large for column "SCHEMA"."TEST1"."COL1" (actual: 11, maximum: 10)

-- insert 11 characters into col2
insert into test1 (col1, col2)
values (null, '12345678901');

ORA-12899: value too large for column "SCHEMA"."TEST1"."COL2" (actual: 11, maximum: 10)

commit;

drop table test1;

Upvotes: 1

miracle173
miracle173

Reputation: 1973

No, you will get an error if you try to insert a larger string.

Database SQL Language Reference:

VARCHAR2 Data Type
The VARCHAR2 data type specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.

Upvotes: 2

Related Questions