CODEWITHSUNDEEP
sqloracle-database
user918229
user918229

Reputation: 433

varchar2(n BYTE|CHAR) default -> CHAR or BYTE

I am supporting code that has below schema declaration:-

create table sample (
        id number Primary key,
        username varchar2(100),
);

I looked up on Oracle documentation & varchar2 has two declaration modes varchar2(n BYTE) and varchar2(n CHAR), If I don't specify explicitly BYTE or CHAR & just declare username varchar2(500) then will 500 BYTES be allocated or 500 characters?

thanks,

Upvotes: 40

Views: 43392

Answers (2)

Denis Kulagin
Denis Kulagin

Reputation: 8906

You might check your current value using the following query:

SELECT
  value

FROM
  NLS_DATABASE_PARAMETERS

WHERE
  parameter='NLS_LENGTH_SEMANTICS';

Upvotes: 13

Justin Cave
Justin Cave

Reputation: 231661

The default will be whatever your NLS_LENGTH_SEMANTICS parameter is set to. By default, that is BYTE to be consistent with older versions of Oracle where there was no option to use character length semantics. If you are defining your own schema and you are using a variable width character set (like AL32UTF8), I'd strongly recommend setting NLS_LENGTH_SEMANTICS to CHAR because you almost always intended to specify lengths in characters not in bytes.

Upvotes: 43

Related Questions