• Explain Duplex And Collate
• Collate Or Not Collate Printing
• Collateral Definition
• Meaning To Collate

Translate Collate. See 4 authoritative translations of Collate in Spanish with example sentences, conjugations and audio pronunciations. A collation specifies the bit patterns that represent each character in a dataset. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. The collate standard facet provides functions to compare and assist in comparing strings in a locale-specific manner. The collate class template has a protected destructor: Programs shall only construct objects of derived classes, or use those installed in locale objects (through usefacet). The values of the default collation column specify the default collations for the character sets. By convention, a collation for a character set begins with the character set name and ends with ci (case insensitive) cs (case sensitive) or bin (binary).

col·late

collate

col•late

collate

collate

Explain Duplex And Collate

collate

collate

collate

collate

collate

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Collation determines how strings are compared, which has a direct impact on ordering (sorting) and equality tests between strings. Oracle Database 12c Release 2 (12.2) lets you specify the collation used for columns that hold string data, allowing you to easily perform case insensitive queries, as well as control the output order of queried data.

This article is only covering the basics of how to set the collation. The wider implications of using collations, as well as the restrictions associated with them can be found here.

• Setting Collation
  

Related articles.

Assumptions

This article assumes the following.

• Your are using Oracle Database 12c Release 2 (12.2).
• Your MAX_STRING_SIZE parameter is set to EXTENDED. You can see how to do this here. Without this you will see this error, 'ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.'
• Your COMPATIBLE parameter is set to 12.2 or higher.
• The database character set is AL32UTF8, NLS_LANGUAGE=AMERICAN and NLS_TERRITORY=AMERICA. Other setting can be used, but the output may not resemble that shown in this article.

Default Behaviour

To understand the impact of collation, it's important to understand the default behaviour of an Oracle database. Before we start using UTF data we need to make sure SQL*Plus will handle it properly. We can do this by setting the NLS_LANG environment variable correctly before starting SQL*Plus.

Create the following test table and populate it with some data containing special characters. Notice the variation in the first letter of the first name.

The following query shows how Oracle treats special characters with regards to sort operations. We can see upper case comes before lower case in the sort order, and the regular characters come before the special characters.

The following query shows how Oracle treats special characters in comparisons. We can see an exact match is made.

The following query shows how Oracle treats special characters in group operations. Once again, the variants on the first character are treated separately.

In the following examples, think back to this default behaviour.

Setting Collation

Collation can be set at a number of different levels, which will be demonstrated below. Each section will not repeat all possible syntax variations or collations as that would be too repetitive.

There are two basic types of collation.

• Binary : Ordering and comparisons of string data are based on the numeric value of the characters in the strings.
• Linguistic : Ordering and comparisons of string data are based on the alphabetic sequence of the characters, regardless of their numeric values. The list of linguistic collations is available here.

When using collations there are three suffixes that alter the behaviour of sorts and comparisons.

• '_CI' : Case insensitive, but accent sensitive.
• '_AI' : Both case and accent insensitive.
• '_CS' : Both case and accent sensitive. This is default if no extension is used.

If no collation is specified, directly or via a default setting, the default USING_NLS_COMP pseudo-collation is used, which means the NLS_SORT and NLS_COMP parameters are used to determine the actual collation used.

The only supported collation for CLOB and NCLOB columns is the USING_NLS_COMP pseudo-collation.

Column-Level

The collation of a specific column can be defined when the table is created. In the following example we set the collation of the COMPANY column to BINARY_CI, which will make sorts and comparisons of that columns data case insensitive, but will still treat special characters as separate.

We can see the collation has made a difference if we query data in the column.

We can also set the collation of new columns added to an existing table. In the following example we set the collation of the LOCATION column to BINARY_AI, which will make sorts and comparisons of that columns data both case insensitive and accent insensitive.

The output below shows the impact of the collation on this new column.

We can see the collation of the columns using the {DBA|ALL|USER}_TAB_COLUMNS views.

Table-Level

The default collation for the whole table can be defined when the table is created.

The table default collation can be changed using the ALTER TABLE statement, but this only affects new columns added to table, not existing columns.

By displaying the column level collation we can see the existing column was not affected by the change in the default collation of the table.

The default table collation is displayed using the {DBA|ALL|USER}_TABLES views.

Schema-Level

The default collation for a schema can be defined when the user is created.

If we connect to the user and create a table we can see the default setting is used to define the default table collation.

The default schema collation is changed using the ALTER USER command, but this doesn't affect the default collation of any existing objects.

If we add another column to the existing table you may think it will use the new default schema collation, but you would be wrong.

Remember, changes to the default schema collation do not affect existing objects, so the default table collation is unchanged.

Session-Level

There are two ways to set the default collation for a session. The DEFAULT_COLLATION parameter can be set at session level.

Alternatively the NLS_SORT and NLS_COMP parameters can be set at session level, making them the defaults for the session.

Notice the default references to the USING_NLS_COMP pseudo-collation, which means the NLS_SORT and NLS_COMP parameters are used to determine the actual collation used.

Database-Level

If the NLS_SORT and NLS_SORT parameters are set at the CDB level they represent the default values for the CDB and all associated PDBs.

If the parameters are set at the PDB level they override the CDB settings.

Remember to reset these values if you don't want any unexpected behaviour in your instance.

Statement-Level

There are a number of ways to influence the results of statements, regardless of the collation associated with the columns. The following examples show how you might use the COLLATE operator and NLSSORT function.

First we need a new session and a new table with no collation defined.

Now we can try a few examples of using the COLLATE operator and NLSSORT function.

Linguistic Indexes

Collate Or Not Collate Printing

Any index created on a column using a named collation is actually a function-based index, but you can create additional indexes with alternative collations.

Views and Materialized Views

Collateral Definition

Views and materialized views can take advantage of collation. The following example creates a clean table, a view that uses collation, then queries the view to shown the collation in action.

For more information see:

Meaning To Collate

Hope this helps. Regards Tim...