Oracle Collation Functions
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose These functions return collation setting information.
Note
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
Dependencies
STANDARD    
 
COLLATION
Returns the name of the derived collation named collations and pseudo-collations. If the derived collation is a Unicode Collation Algorithm (UCA) collation, then the function returns the long form of its name. If the derived collation is undefined due to a collation conflict returns NULL. COLLATION(<expression>) RETURN VARCHAR2;
SQL> CREATE TABLE id_table (
  2  coll_name VARCHAR2(64) COLLATE BINARY_AI,
  3  coll_id VARCHAR2(8)    COLLATE BINARY_CI);
CREATE TABLE id_table (
*
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.


ALTER SYSTEM SET max_string_size=extended COMMENT='Collation enabled 15-Mar-2018' SID='*' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
SHUTDOWN IMMEDIATE;
STARTUP;

SQL> CREATE TABLE id_table (
  2  coll_name VARCHAR2(64) COLLATE BINARY_AI,
  3  coll_id VARCHAR2(8) COLLATE BINARY_CI);

SQL> INSERT INTO id_table VALUES('Morgan', 'ZZYZX4242');

SQL> SELECT collation(name), collation(id)
  2  FROM id_table;

COLLATION COLLATION
--------- ---------
BINARY_AI BINARY_CI
 
NLS_COLLATION_ID
Takes as its argument a collation name and returns the corresponding collation ID number NLS_COLLATION_ID(<expression>) RETURN NUMBER;
SELECT nls_collation_id('BINARY_CI')
FROM dual;

NLS_COLLATION_ID('BINARY_CI')
-----------------------------
                       147455
 
NLS_COLLATION_NAME
Takes as its argument a collation name and returns the corresponding collation name NLS_COLLATION_NAMEk(<expression>) RETURN VARCHAR2;
SELECT nls_collation_name(81919)
FROM dual;

NLS_COLLA
---------
BINARY_AI



SELECT nls_collation_name(208897,'S')
FROM dual;

NLS_COLLATION
-------------
UCA0610_DUCET



SELECT nls_collation_name(208897,'L')
FROM dual;

NLS_COLLATION_NAME(208897,'L')
----------------------------------------
UCA0610_DUCET_S4_VS_BN_NY_EN_FN_HN_DN_MN

Related Topics
Built-in Functions
Collation Operator
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx