databaseoracle-databasevarchar2

What is the default value of VARCHAR2 in Oracle?

I have a column in one of my tables that is of type varchar2(2000), but does this mean 2000 bytes? or 2000 characters? Because I know there's a difference between bytes and characters...

Solution

  • The default is one of the NLS parameters of your session, namely NLS_LENGTH_SEMANTICS. Here is how I can check it from my session:

    select value
from   v$nls_parameters
where  parameter = 'NLS_LENGTH_SEMANTICS'
;

VALUE 
------
BYTE

    You can alter your session to change the value (or you can do it through the graphical interface in something like SQL Developer). You can also put an ALTER SESSION command in your LOGIN.SQL (or, globally, GLOGIN.SQL) if you use one, if you want a specific value to be assigned whenever you start a session. Otherwise, when you start a new session the default will come from your SPFile (most likely).

    Here is how I can check what is in my SPFile:

    select value
from   v$parameter
where  name = 'nls_length_semantics'
;


VALUE 
------
BYTE

    I can also alter my system to change what's in the SPFile, but that's a DBA's job (I think). In any case, it CAN be changed.

    This is similar to other NLS parameters - consider NLS_DATE_FORMAT for example, the behavior is very similar.