Answer / pavan_1981

VARCHAR2(size):variable lenght character data maximum size
is 4000 and minimum is 1
char2(size):fixed lenght character data of lenght size bytes
minimum and default is 1 and maximum is 2000.

Answer / roopesh kumar

Varchar2: The max. allowed length is 4000 byte default 0
i.e. nothing. This is variable length datatype. it will
take only same space as value stored.

Char: Max. permitable length 2000 byte. Default length 0.
It will store value blank padded to right side till
full length as declared.

Ex. SQL> create table fg (d varchar2);
create table fg (d varchar2)
*
ERROR at line 1:
ORA-00906: missing left parenthesis


SQL> create table fg (d char);

Table created.

SQL> desc fg
Name Null? Type
------------------------------- -------- ----
D CHAR(1)
SQL> create table cher_chk (col1 char(12));

Table created.

SQL> insert into cher_chk values ('raj');

1 row created.
SQL> select length(col1) from cher_chk
2 /

LENGTH(COL1)
------------
12

Answer / sravanthi

Let us declare to variables as below:
v_empname CHAR2(10) := 'BILL';
v_depname VARCHAR2(10) := 'HRD';
the database allocates 10 bytes memory for v_empname and
just 3 bytes memory for v_deptname.
when a variable is declared varchar then the memory will be
allocated variably.
So,if one is not sure of the data that the variable is
going to hold then VARCHAR2 should be used instead of CHAR
during declaration.

Answer / esakkiraja

CHAR
-----

SQL> CREATE TABLE char_test (col1 CHAR(10));

Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump"
FROM char_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------
------------------------
qwerty 10 Typ=96 Len=10:
113,119,101,114,116,121,32,32,32,32


VARCHAR
-------

SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump"
FROM varchar_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------
------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121


VARCAHR2
----------

SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump"
FROM varchar2_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------
------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121



VARCHAR vs. VARCHAR2
---------------------
1. VARCHAR is going to be replaced by VARCHAR2 in next
version. So, Oracle suggests the use VARCHAR2 instead of
VARCHAR while declaring datatype.

2. VARCHAR can store up to 2000 bytes of characters while
VARCHAR2 can store up to 4000 bytes of characters.

3. If we declare datatype as VARCHAR then it will occupy
space for NULL values, In case of VARCHAR2 datatype it will
not occupy any space.


CHAR vs. VARCHAR
----------------

VARCHAR is used to store variable length character strings
up to 4000 characters. But, remember CHAR is faster than
VARCHAR - some times up to 50% faster.

Answer / thilaga

char 2000 byres, varchar 4000 bytes

Answer / sushant

Both CHAR and VARCHAR2 types are used to store string values but both behave very differently.



CHAR should be used to store fix length strings. String values will be space/blank padded towards right before storing on disk. If this type is used to store varibale length strings, it will waste a lot of disk space. For example we can use it to store fields where we have one character only like IS_MINOR char (1). Here we will store only 'Y' or 'N' hence it becomes a prefect candidiate for char data type.


VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.


Explanation with an example could be found here :

http://www.oraclebin.com/2012/11/difference-between-char-and-varchar2.html

What are the types of optimization?

0 Answers  

Which one is faster ienumerable or iqueryable?

0 Answers  

what is data integrity? : Sql dba

0 Answers  

Give an example of Full Outer Join?

1 Answers   IBM,

What is null in pl/sql?

0 Answers  

What are sql indexes?

0 Answers  

Write a sql query to convert all character to uppercase after hypen.

0 Answers  

What is the difference between function and procedure in pl/sql?

0 Answers  

Whate is use of MOD function in pl/sql.

3 Answers   Oracle,

Is left join faster than join?

0 Answers  

What are the commands used in sql?

0 Answers  

Does truncate release storage space?

0 Answers