Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ashish .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: July 21, 2016 - 12:41 pm UTC

Version:

Viewed 50K+ times! This question is

You Asked

I have a database column that shall store data always
10 characters long. Which datatype should I use for it CHAR or
VARCHAR2 and What is the specific reason for using that datatype ?



and Tom said...



A CHAR datatype and VARCHAR2 datatype are stored identically (eg: the word 'WORD' stored in a CHAR(4) and a varchar2(4) consume exactly the same amount of space on disk, both have leading byte counts).

The difference between a CHAR and a VARCHAR is that a CHAR(n) will ALWAYS be N bytes long, it will be blank padded upon insert to ensure this. A varchar2(n) on the other hand will be 1 to N bytes long, it will NOT be blank padded.

Using a CHAR on a varying width field can be a pain due to the search semantics of CHAR. Consider the following examples:

ops$tkyte@8i> create table t ( x char(10) );
Table created.

ops$tkyte@8i> insert into t values ( 'Hello' );
1 row created.

ops$tkyte@8i> select * from t where x = 'Hello';

X
----------
Hello

ops$tkyte@8i> variable y varchar2(25)
ops$tkyte@8i> exec :y := 'Hello'
PL/SQL procedure successfully completed.

ops$tkyte@8i> select * from t where x = :y;
no rows selected

ops$tkyte@8i> select * from t where x = rpad(:y,10);

X
----------
Hello


Notice how when doing the search with a varchar2 variable (almost every tool in the world uses this type), we have to rpad() it to get a hit.

If the field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER, it will not help either.

The only time I personally use a CHAR type is for CHAR(1). And that is only because its faster to type char(1) then varchar2(1) -- it offers no advantages.



Rating

  (39 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Use of CHAR vs. VARCHAR2

Lynn Hopkins, August 08, 2001 - 2:16 pm UTC

Exactly the information I was looking for.

shoud I use char or varchar2

A reader, February 03, 2003 - 7:58 pm UTC


A reader, December 01, 2003 - 4:29 pm UTC


reader

A reader, December 29, 2003 - 9:25 am UTC


char or varchar - thank you

Courtland, May 11, 2005 - 10:50 am UTC

This is the information I was looking for; it came up as hit #2 in the Google search "oracle datatypes char varchar varchar2 site:oracle.com".

good one

Vijay, December 08, 2005 - 7:07 am UTC

Hi Tom,
good day to you, hope you are doing good, just to know out of curosity what is the reason to have CHAR datatype when we have Varchar2, other than for backward compatibility.

Kind Regards,
Vijay

Tom Kyte
December 08, 2005 - 7:13 am UTC

if you ask me....


none. Here is an excerpt from my most recent book on that topic:

<quote>
The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. ......
</quote>

Some reasons why CHAR is commonly used

Adam Musch, December 08, 2005 - 8:03 am UTC

1. Some programming languages need to pull both data and data length from the database when using cursors against variable-length columns. Therefore, programmers may prefer CHARs, as they then have to write less code.

(Sure, it's specifically COBOL, but some of us still support apps written in COBOL.)

2. Some database engines have a bias for CHAR columns, with "myth and legend" tuning stating that "CHAR columns perform better." Therefore, a portable application (I know, foolishness itself) may use CHARs more frequently, even if a VARCHAR2 would be a more logical choice.

I agree with using VARCHAR2's, however. Just pointing out some reasons why one might go in another direction.

trim

karthick, December 08, 2005 - 10:20 am UTC

select * from t where trim(x) = :y

this also works but if there is an index on x the index is not considered why so.





Tom Kyte
December 08, 2005 - 11:52 am UTC

because you applied a function to the column X that modifies the value of X, the index on X has the value of X not the modified value of X.

University SQL Help

Tim, November 06, 2006 - 1:36 am UTC

Very helpful page. Now I'm positive my Prof is an idiot. Thanks for reassuring me.

advantage of varchar than varchar2

Hanumantha Rao, November 14, 2006 - 8:04 am UTC

hi,
this is Hanuma from Mumbai, India. In our project we are using varchar instead of varchar2. i don't know, what is the advantage of varchar? plz clear my dought?

Tom Kyte
November 14, 2006 - 8:10 am UTC

currently they are the same. but that is subject to change:

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref77 <code>

A reader, November 14, 2006 - 8:39 am UTC


When to use CHAR vs. VARCHAR2

Robert Simpson, November 14, 2006 - 11:05 am UTC

As previously indicated, CHAR and VARCHAR2 are stored identically in the database, so the determination of whether to use CHAR or VARCHAR2 is more closely tied to the programming language and practices than to the database. The difference is that comparisons of two CHAR fields will be automatically blank-padded; comparisons with VARCHAR2 will not. For an example of what NOT to do, we had an application where the DBAs' recommendation was overridden, and all database columns were made VARCHAR2, in order to save time on the design. As a result, every time one of these columns is compared to a CHAR, the programmers must use an RPAD or RTRIM function, since PL/SQL won't do automatic padding on VARCHAR2s. In some cases, when the function is applied to an index column, the query runs much longer because the index is not used (I do realize that you could create a function index, but we usually fix the problem by moving the function to the other side of the "="). So the time that was saved in the design phase has been spent many times over trying to determine why certain queries are running so poorly.

One reason that VARCHAR2 is often used is that some programmers use variable length character variables, which will also defeat the programming language's automatic padding for fixed-length comparisons. For example, in Java, programmers might simply use "String", which is variable length, for all character variables and avoid "char[]", which is the appropriate type for storing data being transferred to or from a database CHAR column.

In database design, we're taught how to determine whether to make a column CHAR or VARCHAR/VARCHAR2. You ask yourself, "is the length of the value significant?" For example, if the column has up to 6 characters representing a gender, is a 6-character "MALE " different from a 4-character "MALE"? In this case the answer is obviously "no" and the column should therefore be CHAR so that regardless of whether you compare it to a 4-character constant or 6-character host variable, the comparison is automatically padded and works properly regardless of whether the column contains "MALE " or "FEMALE" and whether the constant or host variable contains "MALE", "MALE " or "FEMALE". If either side of the comparison is a variable length character type, then you will need to RPAD and/or RTRIM one or both sides of the comparison to make it work properly.


Tom Kyte
November 15, 2006 - 6:38 am UTC

if you just use varchar2 everywhere, no problem

My advice is, has been, will be very loudly stated as:

IGNORE THE EXISTENCE OF CHAR.

period. If you never use char, you never compare char to varchar2, problem solved.

And if you use char and compare a char(n) to a char(m) they will never compare either.

Just say NO TO CHAR.

char / varchar2 storage

Mark A. Williams, November 14, 2006 - 9:21 pm UTC

Hi Robert,

You said, "As previously indicated, CHAR and VARCHAR2 are stored identically in the database, so the determination of whether to use CHAR or VARCHAR2 is more closely tied to the programming language and practices than to the database."

I think there might be a qualifier missing in that statement. CHAR and VARCHAR2 are stored identically when they are the same length *and* when that length is equal to the size specified for the column.

In any case, here's example:

SQL> connect /
Connected.
SQL> create table char_test
  2  (
  3    c1 char(8),
  4    c2 varchar2(8)
  5  );

Table created.

SQL> insert into char_test values ('a', 'b');

1 row created.

SQL> commit;

Commit complete.

SQL> @get_header_block
Enter segment name: char_test

HEADER_FILE HEADER_BLOCK
----------- ------------
          5            9

1 row selected.

SQL> @dump_block
Enter data file name or number: 5
Enter min block: 10
Enter max block: 10

System altered.

SQL>

Now looking at the relevant part of the block dump:

col  0: [ 8]  61 20 20 20 20 20 20 20
col  1: [ 1]  62

It does not seem that the CHAR and VARCHAR2 columns are stored identically in the database.

Also, "For an example of what NOT to do, we had an application where the DBAs' recommendation was overridden, and all database columns were made VARCHAR2, in order to save time on the design."

You don't mean to say that a column that might have been declared as varchar2(32) was made char(32) do you?

Perhaps I've misunderstood what you are saying, and, if so, I apologize.

- Mark 

My poor wording

Mark A. Williams, November 14, 2006 - 10:48 pm UTC

I wrote:

You don't mean to say that a column that might have been declared as varchar2(32) was made char(32) do you?

What I meant:

The DBAs recommended something like char(32) and someone else decided to make it varchar2(32)?

- Mark

CHAR vs. VARCHAR in database

Robert Simpson, November 16, 2006 - 4:59 pm UTC

I suspect the original author was referring to the way they are stored with a length followed by the value of the column. See his example above, just under "and we said...".
As far as qualifiers go, this is only the way that Oracle works. In DB2, for example, a CHAR takes less space to store than a VARCHAR, and potentially a _lot_ less space in the logs, since every column after the VARCHAR must be included in the log records if the length of the VARCHAR changes (they recommend putting VARCHARs at the end of rows for that reason).

The DBAs recommended making each column whatever it should be, whether that be CHAR or VARCHAR, based on good database design principles (see my "gender" example above). It was decided not to spend the time to do that, so instead we've had to spend more time determining whether or not you need to add RPADs and RTRIMs and solving the other problems that approach causes when those functions are put on the wrong side of the "=". Obviously, it's much easier to use CHAR and let the padding happen automatically. (You may still need to be careful about which side of the "=" is going to be automatically padded - I'm not sure if, when a key column get padded, if it can still use the index or not. But, adside from typos, you should be comparing only to shorter values, for example: GENDER = 'MALE', but not GENDER = 'UNKNOWN' or GENDER = 'FEMALE ' when GENDER is CHAR(6).)


Answered our exact question, thanks!

User Erik, November 17, 2006 - 4:01 pm UTC


A reader, December 19, 2006 - 8:52 am UTC


Different behavior of LIKE since 9i

Uwe M. Kuechler, January 22, 2008 - 10:06 am UTC

Dear Tom,
my customer has been aware of this issue for quite some time, yet still some CHAR columns can be found in legacy (and third party) tables. Recently, I've found an interesting behavior of the LIKE operator that changed after migration to 10g. I wonder If you have some background on this?

Using your example in 8i:

SQL> create table t ( x char(10) );

Table created.

SQL> insert into t values ( 'Hello' );

1 row created.

SQL> select * from t where x LIKE 'Hello';

X
----------
Hello



Now, let's try the same in 9i and above:

SQL> select * from t where x LIKE 'Hello';

no rows selected


Of course I recommended to migrate from CHAR to VARCHAR2 or use LIKE ...% at least, but I'm still curious if Oracle changed the internal optimization from LIKE to "=".

Best regards,
Uwe

Tom Kyte
January 22, 2008 - 6:27 pm UTC

it was a "bug" in 9i, it was not promoting the 'hello' literal to a char for the like, 10g does.

it is still "like" in 10g:

ops$tkyte%ORA10GR2> select * from t where x like 'Hello';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 |     3   (0)| 00:00:01
|*  1 |  TABLE ACCESS FULL| T    |     1 |    12 |     3   (0)| 00:00:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X" LIKE 'Hello')


Why "wasting" space

Marius NITU, February 09, 2009 - 4:25 am UTC

Hi Tom,

Could you please explain in two words why Oracle stores the length of the CHAR type as it does for VARCHAR. Is it for historical reasons ? It seems that is not the case with other databases and there are some people who thinks that¿s a waste because the length it¿s always known. Personnaly I don¿t think that it really matters.

Tom Kyte
February 09, 2009 - 7:15 pm UTC

because we always have, a char is just a varchar2 with a leading byte indicator.

And you know what, if the biggest worry in the year 2009 that "others" have is that there is a 1 byte (for 255 and less) and 2 bytes (for 256 and more, but at that point, you are talking about 2 bytes VERSUS a fixed allocation of more than 256) length field - I'd say "they are penny wise and pound foolish"....


There are slightly larger fish to fry, don't you think so?

Marius NITU, February 10, 2009 - 4:48 am UTC

Thanks Tom,
I'm totally agreeing with you.

Just out of curiousity

Saurabh, May 05, 2009 - 6:35 pm UTC

The difference between varchar2 and char is varchar2 supports variable length of characters. So my question is doesn't oracle to has to do something at the back end to make this possible which will affect the performance? I use varchar2 most of the places but few places are there where char seems to be perfectly suitable like states in US(it can not be greater that or less than 2 character)or social security number or telephone number.
Tom Kyte
May 11, 2009 - 8:58 am UTC

A char in Oracle is simply a varchar2 that has a fixed length - it is blank padded to the maximum length.

Under the covers - they are otherwise *IDENTICAL* - in storage, in retrieval, in everything.

The only difference between:

varchar2(10)
char(10)

is that the char(10) will ALWAYS be either NULL or 10 bytes in length - with blanks provided by us at the end of the string if you do not supply them.


Oh, wait, that is precisely what I originally wrote :(



I would suggest forgetting the existence of the CHAR type - it can only cause confusion and will lead developers to always add "rtrim(column)" in their predicates - leading to poor performance....

srp, May 12, 2009 - 6:10 pm UTC

Thanks for replying. But as far as I know another difference between char and varchar2 is varchar2 will not occupy space for null values. i.e. when we use varchar2(10) and assign it value suppose
state varchar2(10) := 'WI' it will take the space in memory for two characters only where char will take space for whole 10 characters. Now apparently this is advantage of varchar2. But my thinking is it will take more time to store for varchar2(may be important for hard real-time systems). As far as I am concerned I use char where the string value is neither greater than nor less than specified value(like state in USA) so developers don't have to use rtrim.
Tom Kyte
May 13, 2009 - 11:14 am UTC

... But as far as I know another difference between char and
varchar2 is varchar2 will not occupy space for null values. ...

that is not accurate.


You can say this: ANY column that is NULL and at the end of a row such that no other column after it is NOT NULL occupies 0 bytes. ANY column that is NULL and has a column after it that is NOT NULL will occupy space.

varchar2 and char are the same with regards to NULL.



do not use char, period. the space for a char is not allocated until referenced either.

varchar2(4001) in PL SQL

Atul Gupta, May 20, 2009 - 9:53 am UTC

Hello Tom,
Pls let me know if its more efficient and memory friendly to use varchar2(4001) in PL SQL rather than varchar2(3999) in PL SQL as oracle takes less memory and more optimized to handle varchar2(4001) in PL SQL ?

Since internally if variable declared as varchar2(4000) oracle considers it as sql datatype and varchar2(4001) as pl sql and has special scheme which ensures that memory utilization is less ?

I just heard this and not sure if the above is true ..... let me know if there is any truth in above statements ?

br
atul
Tom Kyte
May 23, 2009 - 1:04 pm UTC

you were mislead.

use the right length in your code - period


A reader, May 23, 2009 - 10:10 pm UTC


CHAR Vs VARCHAR2

Som Bahadur Limbu, March 23, 2010 - 10:55 am UTC

Hi Tom,
/*****************************************************************************************/
atul - "Pls let me know if its more efficient and memory friendly to use varchar2(4001) in PL
SQL rather than varchar2(3999) in PL SQL as oracle takes less memory and more optimized to handle
varchar2(4001) in PL SQL ? "

Followup May 23, 2009 - 1pm Central time zone:
"you were mislead.

use the right length in your code - period "
/*****************************************************************************************/

The actual value for Atul's question should be varchar2(2000).
What do you have to comment on the below extract from the oracle documentation.

1.
[PL/SQL
User’s Guide and Reference
Release 2 (9.2)
March 2002
Part No. A96624-01]

[Page No. PL/SQL Datatypes 3-9, PL/SQL Datatypes 3-10]
"
Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2
that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to
Predefined Datatypes hold the actual value.

For a VARCHAR2 variable that is shorter than 2000 bytes,
PL/SQL preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a
VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter
takes up 1999 bytes."

2.
Oracle® Database
PL/SQL User's Guide and Reference
10g Release 2 (10.2)
B14261-01
June 2005

[Page No. - 3-8 Oracle Database PL/SQL User’s Guide and Reference]

"
Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2
that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to
hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,
PL/SQL preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a
VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes
up 1999 bytes."

Tom Kyte
March 23, 2010 - 12:54 pm UTC

I don't care what the length is, they were mislead.

You use the right size, not the size of some performance trick, you use the RIGHT SIZE.

that is all, and that is what I mean by mislead, this is not a performance discussion - there is only ONE size to use for your variables...

The right size.

A reader, May 08, 2010 - 9:47 am UTC

Difference between VARCHAR and VARCHAR2?
Tom Kyte
May 10, 2010 - 6:15 pm UTC

there isn't any as of 11g Release 2 and before.

Thank you!

Olav, October 12, 2010 - 1:38 am UTC

I have been using the Char, Number, etc. for a long time now and on some old computer systems I have been having some issues were I had to use trim(), lpad(), etc.

Mostly this was due to the original design.
Now, after beeing enlightened, as far as I understand - I should use only varchar2 and nvarchar2 for oracle.

This simplifies a lot too for me, as I mainly work on converting old computer systems to oracle and then afterwards make apex pages for them.

Anyhow, thanks :-) I've read this entire thread and I have now seen the light! (In an agnostic way!).

compare char(n) and char(m)

asktom fan, May 10, 2012 - 2:55 pm UTC

Hi Tom,

In your followup dated November 15, 2006 - 6am Central time zone, you said:

<quote>

if you just use varchar2 everywhere, no problem

My advice is, has been, will be very loudly stated as:

IGNORE THE EXISTENCE OF CHAR.

period.  If you never use char, you never compare char to varchar2, problem solved.

And if you use char and compare a char(n) to a char(m) they will never compare either.

Just say NO TO CHAR. 

</quote>




It appears that the statement you made about comparing char(n) to char(m) is no longer true in Oracle 11.2.0.3. What was the datbase version when you made that statement?


Please see the following:



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t (x char(10), y char(15), z varchar(15));

Table created.

SQL> insert into t (x, y, z) values ('hello', 'hello', 'hello');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t where x = y;

X          Y               Z
---------- --------------- ---------------
hello      hello           hello

SQL> select * from t where y = z;

no rows selected



Just to be clear, I am not a fan of CHAR either :)

Tom Kyte
May 10, 2012 - 3:35 pm UTC

I misspoke with the char(n) to char(m), we will cast to the "higher" type in that case.

thanks!

compare char(n) with char(m)

asktom fan, May 10, 2012 - 4:09 pm UTC

Tom, thanks for your quick response.

Can you please clarify what you mean by "we will cast to the "higher" type in that case"?
Tom Kyte
May 11, 2012 - 1:23 am UTC

the char(n) and char(m) are "normalized" to be char( greatest(n,m) ) and then compared.

char(n), varchar2(n) and literals in the where condition

Prajjwal Mallik, August 23, 2012 - 5:10 am UTC

Hi Tom,

My question is, if the following 2 queries returns the same row, why does "select * from my_tab where aa=bb" not return anything?

select * from my_tab where aa='pqr'
select * from my_tab where bb='pqr';


Regards,
Prajjwal


----------------------- Please refer to the following -----------------------------


SQL> create table my_tab(aa char(5), bb varchar2(5));

Table created.

SQL> insert into my_tab values ('pqr', 'pqr');

1 row created.

SQL> select * from my_tab where aa='pqr'; --no trailing space in the search condition

AA    BB
----- -----
pqr   pqr

SQL> select * from my_tab where aa='pqr '; --one trailing space in the search condition

AA    BB
----- -----
pqr   pqr

SQL> select * from my_tab where aa='pqr  '; --two trailing spaces in the search condition

AA    BB
----- -----
pqr   pqr

SQL> select * from my_tab where aa='pqr   '; --three trailing spaces in the search condition, length('pqr   ')=6 which is greater than 5

AA    BB
----- -----
pqr   pqr





So, I believe, the search condition involving char datatype does not even care how many trailing spaces are there after 'pqr'.





SQL> select * from my_tab where bb='pqr';

AA    BB
----- -----
pqr   pqr

SQL> select * from my_tab where aa=bb;

no rows selected

Tom Kyte
August 29, 2012 - 11:38 am UTC

that is the way char's (stupidly) work - by (committee) design.


when you compare a char(n) to a char(m) - you take the greater of N and M and cast the other one to that size.

So, when you compare a char(5) (your column) to a chr(3) 'pqr', the literal pqr is cast as a char(5) which blank pads it.


When you compare a varchar2(5) to a varchar2(3) - no such casting happens.


never use char, just don't do it.

adderek, January 22, 2013 - 6:46 am UTC


CHAR(1) vs VARCHAR2(1) CONSTANT PL/SQL variable

Shimmy, October 17, 2013 - 2:54 pm UTC

I define a contant as :

DELIMITER CONSTANT CHAR(1) := ';';

Is it better to define it as:

DELIMITER CONSTANT VARCHAR2(1) := ';';

Does VARCHAR2(2) take more memory space?
Is it faster to process CHAR than VARCHAR2 in this case?

Tom Kyte
November 01, 2013 - 8:13 pm UTC

a char(1) and a varchar2(1) are 100% identical.

they both have a leading byte to tell us their length.


that are the same.


I say to never never never, never ever, use a char type - not even a char(1). A char is just a varchar that takes the maximum width and is blank padded. It is a waste.

char or varchar2 or varchar

Ankush, July 10, 2014 - 6:19 am UTC

we use char when we have the exact length of the variable..in this situation char work more faster than using the varchar.
and there is no such difference between varchar and varchar2.the only difference is the lenght.both are same otherwise...

Adrian Shum, December 05, 2014 - 3:23 am UTC

Ankush: I don't think you have actually read the answer: Using CHAR is NOT faster than VARCHAR2. At least not in Oracle.

The only case that CHAR outperforms VARCHAR2 is when you are updating the value from a shorter string to a longer one, which will trigger some kind of internal resizing for VARCHAR2 column. However, variable-length data is never be a good candidate to use CHAR

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1542606219593

Checkout the performance test and my update on it

WoundedEgo, June 22, 2015 - 5:25 pm UTC

Significant overlooked difference!:

"...Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes..."
http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/03_types.htm

CHAR remains useful

Peter, December 31, 2015 - 2:48 am UTC

You said...

If the field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER, it
will not help either.

The only time I personally use a CHAR type is for CHAR(1). And that is only because its
faster to type char(1) then varchar2(1) -- it offers no advantages.

I recall in another post you advocated using data types as constrained as possible. Granted, CHAR isn't much of a constraint. But when I see CHAR(9) vs VARCHAR2(9), they have the expected different meanings and the meanings are useful to communicate to humans. But where we still store things like an SSN, CHAR(9) is more meaningful than VARCHAR2(9). At this point in time, SSNs will always have 9 character (digits). The column name isn't always going to make this obvious.
Connor McDonald
January 01, 2016 - 4:41 am UTC

I see your point, but it would still would not stop me from storing a 6 char SSN in there, unless I add some constraints about the *content* of the data. And if I'm doing that, then potentially we're back to varchar2 being 'just as good'


QUESTION

Amar, July 21, 2016 - 10:43 am UTC

Sir,
will you please give me the difference between
11g char/varchar/varchar2 datatypes
and
12g char/varchar/varchar2 datatypes

thanks ..

QUESTION

Amarnath Sharma, July 21, 2016 - 12:24 pm UTC

Sir,
when I use 11g char/varchar/varchar2 it accepts number in it
but when I use 12c char/varchar/varchar2 it doesn't accept number ,why please help me

thank you
Chris Saxon
July 21, 2016 - 12:41 pm UTC

What do you mean, "it doesn't accept number"?

Show us!

The size clause works the same in 12c as it does in 11g. Though you can go up to 32,767 if you have extended data types enabled. The 11g limit is 4,000.

Ivan, July 18, 2018 - 5:05 pm UTC

Excellent explanation.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library