In Oracle, the NLS_CHARSET_DECL_LEN() function returns the declaration length (in number of characters) of an NCHAR column.

Syntax

The syntax goes like this:

NLS_CHARSET_DECL_LEN(byte_count, char_set_id)

Where byte_count is the width of the column and char_set_id is the character set ID of the column.

Example

Here’s a basic example:

SELECT NLS_CHARSET_DECL_LEN(100, 1)
FROM DUAL;

Result:

100

Here it is with a different character set ID:

SELECT NLS_CHARSET_DECL_LEN(100, 2000)
FROM DUAL;

Result:

50

Get the Character Set ID

We can use the NLS_CHARSET_ID() function to return the character set ID for a given character set:

Example:

SELECT NLS_CHARSET_DECL_LEN(
    100, 
    NLS_CHARSET_ID('AL16UTF16')
    )
FROM DUAL;

Result:

50

Null Argument

If any argument is null, the result is null:

SET NULL 'null';
SELECT 
    NLS_CHARSET_DECL_LEN(null, 1) AS r1,
    NLS_CHARSET_DECL_LEN(100, null) AS r2
FROM DUAL;

Result:

     R1      R2 
_______ _______ 
   null    null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling the function without passing any arguments results in an error:

SELECT NLS_CHARSET_DECL_LEN()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_CHARSET_DECL_LEN()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT NLS_CHARSET_DECL_LEN(100, 1, 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_CHARSET_DECL_LEN(100, 1, 2)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: