What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte

Question

I know that I can declare a varchar2 using the number of the characters that it should be able to contain   However  in an Oracle database on which I am working  I found that a field  named PDF  is defined as follows   VARCHAR2 1 BYTE    What does this mean  How many characters can it contain   Another  related question  What is the difference between a VARCHAR and a VARCHAR2

User · Answer

it means ONLY one byte will be allocated per character - so if you're using multi-byte charsets, your 1 character won't fit

if you know you have to have at least room enough for 1 character, don't use the BYTE syntax unless you know exactly how much room you'll need to store that byte

when in doubt, use VARCHAR2(1 CHAR)

same thing answered here Difference between BYTE and CHAR in column datatypes

Also, in 12c the max for varchar2 is now 32k, not 4000. If you need more than that, use CLOB

in Oracle, don't use VARCHAR

User · Answer

To answer you first question  Yes  it means that 1 byte allocates for 1 character  Look at this example  SQL gt  conn   as sysdba Connected  SQL gt  create table test  id number 10   v char varchar2 10     Table created   SQL gt  insert into test values 11111111111  darshan    insert into test values 11111111111  darshan     ERROR at line 1  ORA-01438  value larger than specified precision allows for this column   SQL gt  insert into test values 11111  darshandarsh    insert into test values 11111  darshandarsh     ERROR at line 1  ORA-12899  value too large for column  SYS   TEST   V CHAR   actual  12  maximum  10    SQL gt  insert into test values 111  Darshan     1 row created   SQL gt     And to answer your next one  The difference between varchar2 and varchar     VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters  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

User · Answer

The VARCHAR datatype is synonymous with the VARCHAR2 datatype  To avoid possible changes in behavior  always use the VARCHAR2 datatype to store variable-length character strings   If your database runs on a single-byte character set  e g  US7ASCII  WE8MSWIN1252 or WE8ISO8859P1  it does not make any difference whether you use VARCHAR2 x BYTE  or VARCHAR2 x CHAR    It makes only a difference when your DB runs on multi-byte character set  e g  AL32UTF8 or AL16UTF16   You can simply see it in this example   CREATE TABLE my table       VARCHAR2 byte VARCHAR2 1 BYTE        VARCHAR2 char VARCHAR2 1 CHAR      INSERT INTO my table  VARCHAR2 char  VALUES          1 row created   INSERT INTO my table  VARCHAR2 char  VALUES         1 row created   INSERT INTO my table  VARCHAR2 byte  VALUES          INSERT INTO my table  VARCHAR2 byte  VALUES         Error at line 10 ORA-12899  value too large for column  MY TABLE   VARCHAR2 BYTE   actual  3  maximum  1   INSERT INTO my table  VARCHAR2 byte  VALUES        Error at line 11 ORA-12899  value too large for column  MY TABLE   VARCHAR2 BYTE   actual  2  maximum  1    VARCHAR2 1 CHAR  means you can store up to 1 character  no matter how many byte it has  In case of Unicode one character may occupy up to 4 bytes   VARCHAR2 1 BYTE  means you can store a character which occupies max  1 byte   If you don t specify either BYTE or CHAR then the default is taken from NLS LENGTH SEMANTICS session parameter   Unless you have Oracle 12c where you can set MAX STRING SIZE EXTENDED the limit is VARCHAR2 4000 CHAR   However  VARCHAR2 4000 CHAR  does not mean you are guaranteed to store up to 4000 characters  The limit is still 4000 bytes  so in worst case you may store only up to 1000 characters in such field    See this example      in UTF-8 occupies 3 bytes    CREATE TABLE my table2 VARCHAR2 char VARCHAR2 4000 CHAR     BEGIN     INSERT INTO my table2 VALUES                                         FOR i IN 1  7 LOOP         UPDATE my table2 SET VARCHAR2 char   VARCHAR2 char   VARCHAR2 char      END LOOP  END     SELECT LENGTHB VARCHAR2 char    LENGTHC VARCHAR2 char  FROM my table2   LENGTHB VARCHAR2 CHAR  LENGTHC VARCHAR2 CHAR  ---------------------- ----------------------                   3840                   1280 1 row selected    UPDATE my table2 SET VARCHAR2 char   VARCHAR2 char   VARCHAR2 char   UPDATE my table2 SET VARCHAR2 char   VARCHAR2 char   VARCHAR2 char Error at line 1 ORA-01489  result of string concatenation is too long   See also Examples and limits of BYTE and CHAR semantics usage  NLS LENGTH SEMANTICS   Doc ID 144808 1

User · Answer

You can declare columns variables as varchar2 n CHAR  and varchar2 n byte    n CHAR means the variable will hold n characters  In multi byte character sets you don t always know how many bytes you want to store  but you do want to garantee the storage of a certain amount of characters   n bytes means simply the number of bytes you want to store   varchar is deprecated  Do not use it  What is the difference between varchar and varchar2

[sql] What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte?

Examples related to sql

Examples related to oracle

Examples related to varchar

Examples related to sqldatatypes