Andrew Fraser DBA

oracle substrb function to chop a string down to its first 4000 bytes

The substrb 1 , 4000 function will chop a string down to its first 4000 bytes, e.g.:

SELECT SUBSTR('1€3',2,1) , SUBSTRB('1€3',1,3) , LENGTH('1€3') , LENGTHB('1€3') FROM dual ;

Needed if using the (non default) option of ‘char’ in column lengths:

CREATE TABLE mytable ( col1 VARCHAR2(4000 CHAR) ) ;

You can change the default setting at session level:

ALTER SESSION SET nls_length_semantics = 'CHAR' ;

But that needs run every time you connect in, it doesn’t remember session settings after you disconnect. Permanently changing that parameter at database level is possible but not advisable.

You can see what is set as default at each of the 3 levels with:

SELECT * FROM nls_session_parameters  WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;
SELECT * FROM nls_instance_parameters WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;
SELECT * FROM nls_database_parameters WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;

The NVARCHAR2 datatype could be useful if you want to use a different character set for a particular column.