When SQL functions evaluate views and triggers, default values from the current session are used for the NLS function parameters. When SQL functions evaluate CHECK constraints, they use the default values that were specified for the NLS parameters when the database was created.

The following NLS parameters are not accepted in SQL functions except for NLSSORT:

NLS_LANGUAGE
NLS_TERRITORY
NLS_DATE_FORMAT

NLS_DATE_FORMAT and NLS_TERRITORY_FORMAT are not accepted as parameters because they can interfere with required format masks. A date format must always be specified if an NLS parameter is in a TO_CHAR or TO_DATE function. As a result, NLS_DATE_FORMAT and NLS_TERRITORY_FORMAT are not valid NLS parameters for the TO_CHAR or TO_DATE functions. If you specify NLS_DATE_FORMAT or NLS_TERRITORY_FORMAT in the TO_CHAR or TO_DATE function, then an error is returned.

NLS_LANGUAGE can interfere with the session value of NLS_DATE_LANGUAGE. If you specify NLS_LANGUAGE in the TO_CHAR function, for example, then its value is ignored if it differs from the session value of NLS_DATE_LANGUAGE.

The CONVERT function enables conversion of character data between character sets.

The CONVERT function converts the binary representation of a character string in one character set to another. It uses exactly the same technique as conversion between database and client character sets. Hence, it uses replacement characters and has the same limitations.

The syntax for CONVERT is as follows:

CONVERT(char, dest_char_set[, source_char_set])

char is the value to be converted. source_char_set is the source character set and dest_char_set is the destination character set. If the source_char_set parameter is not specified, then it defaults to the database character set.

Oracle provides SQL functions that work in accordance with different length semantics. There are three groups of such SQL functions: SUBSTR, LENGTH, and INSTR. Each function in a group is based on a different kind of length semantics and is distinguished by the character or number appended to the function name. For example, SUBSTRB is based on byte semantics.

The SUBSTR functions return a requested portion of a substring. The LENGTH functions return the length of a string. The INSTR functions search for a substring in a string.

The SUBSTR functions calculate the length of a string differently. Table 9-2 summarizes the calculation methods.

The LENGTH and INSTR functions calculate string length in the same way, according to the character or number added to the function name.

The following examples demonstrate the differences between SUBSTR and SUBSTRB on a database whose character set is AL32UTF8.

For the string Fußball, the following statement returns a substring that is 4 characters long, beginning with the second character:

SELECT SUBSTR ('Fußball', 2 , 4) SUBSTR FROM DUAL;

SUBS
----
ußba

For the string Fußball, the following statement returns a substring 4 bytes long, beginning with the second byte:

SELECT SUBSTRB ('Fußball', 2 , 4) SUBSTRB FROM DUAL;

SUB
---
ußb

The LIKE conditions specify a test that uses pattern-matching. The equality operator (=) exactly matches one character value to another, but the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.

LIKE calculates the length of strings in characters using the length semantics associated with the input character set. The LIKE2, LIKE4, and LIKEC conditions are summarized in Table 9-3.

There is no LIKEB condition.

Two SQL functions, NLS_CHARSET_NAME and NLS_CHARSET_ID, can convert between character set ID numbers and character set names. They are used by programs that need to determine character set ID numbers for binding variables through OCI.

Another SQL function, NLS_CHARSET_DECL_LEN, returns the declaration length of a column in number of characters, given the byte length of the column.

This section includes the following topics:

• Converting from Character Set Number to Character Set Name

• Converting from Character Set Name to Character Set Number

• Returning the Length of an NCHAR Column

  See Also:

  Oracle Database SQL Language Reference

The NLSSORT function enables you to force a specific collation (sort order) for ORDER BY, GROUP BY, comparison conditions, and a number of other collation-sensitive operations. However, starting with Oracle Database 12c Release 2 (12.2), the recommended way to force a specific collation for such operations is to use the COLLATE operator. The COLLATE operator works for all the collation-sensitive operations, including those for which NLSSORT cannot be used, for example MAX, MIN, and INSTR.

The NLSSORT function calculates a collation key for its character argument. The collation key is a value of data type RAW, which has the following property: when two collation keys created for a given collation for two (possibly different) source character values are compared as binary, their mutual ordering corresponds to the expected mutual ordering of the source character values in this collation, that is, NLSSORT(c1) < NLSSORT(c2), if and only if c1 < c2, where both NLSSORT and the character operator < (less-than) use the same collation.

The collations used for ORDER BY, GROUP BY, comparison conditions, and other collation-sensitive operations are determined by the data-bound collation determination rules. If these rules yield a pseudo-collation, the session parameters NLS_COMP and NLS_SORT determine the actual collation.

The following example specifies a German collation with the NLS_SORT session parameter. It assumes that the declared collation of column1 is USING_NLS_COMP or USING_NLS_SORT.

ALTER SESSION SET NLS_SORT = GERMAN;
SELECT * FROM table1
     ORDER BY column1;

The following example first sets the NLS_SORT session parameter to German, but the NLSSORT function overrides it by specifying a French sort.

ALTER SESSION SET NLS_SORT = GERMAN;
SELECT * FROM table1
     ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');

The WHERE clause uses binary comparison when NLS_COMP is set to BINARY and the declared collation of referenced columns is USING_NLS_COMP. But, this can be overridden by using the NLSSORT function in the WHERE clause.

The following example makes a linguistic comparison using the WHERE clause.

ALTER SESSION SET NLS_COMP = BINARY;
SELECT * FROM table1
WHERE NLSSORT(column1, 'NLS_SORT=FRENCH')>
      NLSSORT(column2, 'NLS_SORT=FRENCH');

Setting the NLS_COMP session parameter to LINGUISTIC causes the NLS_SORT value to be used in the WHERE clause.

Oracle Database may add the NLSSORT function implicitly to SQL expressions in a subquery to implement linguistic behavior for a category of collation-sensitive operations. The implicitly added NLSSORT calls are visible in the execution plan for an SQL statement.

The rest of this section contains the following topics:

• NLSSORT Syntax

• Comparing Strings in a WHERE Clause

• Controlling an ORDER BY Clause

SELECT * FROM table1
ORDER BY NLSSORT(column1, 'NLS_LANG=PL');

SELECT column1 FROM table1 WHERE column1 > 'B';

WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string)

SELECT column1 FROM table1 WHERE NLSSORT(column1) > NLSSORT('B');

SELECT column1 FROM table1 WHERE column1 COLLATE USING_NLS_SORT > 'B';

SELECT column1 FROM table1 WHERE column1 COLLATE GERMAN > 'B';

SELECT last_name FROM employees 
     ORDER BY last_name;

SELECT last_name FROM employees
     ORDER BY NLSSORT(last_name, 'NLS_SORT = GERMAN');

Several format masks are provided with the TO_CHAR, TO_DATE, and TO_NUMBER functions.

The RM (Roman Month) format element returns a month as a Roman numeral. You can specify either upper case or lower case by using RM or rm. For example, for the date 7 Sep 2007, DD-rm-YYYY returns 07-ix-2007 and DD-RM-YYYY returns 07-IX-2007.

Note that the MON and DY format masks explicitly support month and day abbreviations that may not be three characters in length. For example, the abbreviations "Lu" and "Ma" can be specified for the French "Lundi" and "Mardi", respectively.

The week numbers returned by the WW format mask are calculated according to the following algorithm: int(dayOfYear+6)/7. This algorithm does not follow the ISO standard (2015, 1992-06-15).

To support the ISO standard, the IW format element is provided. It returns the ISO week number. In addition, the I, IY, IYY, and IYYY format elements, equivalent in behavior to the Y, YY, YYY, and YYYY format elements, return the year relating to the ISO week number.

In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday. The week number is determined according the following rules:

• If January 1 falls on a Friday, Saturday, or Sunday, then the week including January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.

• If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the week is the first week of the new year, because most of the days in the week belong to the new year.

For example, January 1, 1991, is a Tuesday, so Monday, December 31, 1990, to Sunday, January 6, 1991, is in week 1. Thus, the ISO week number and year for December 31, 1990, is 1, 1991. To get the ISO week number, use the IW format mask for the week number and one of the IY formats for the year.

The DBMS_LOB PL/SQL package can load external BFILE data into LOB columns. Oracle Database performs character set conversion before loading the binary data into CLOB or NCLOB columns. Thus, the BFILE data does not need to be in the same character set as the database or national character set to work properly. The APIs convert the data from the specified BFILE character set into the database character set for the CLOB data type, or the national character set for the NCLOB data type. The loading takes place on the server because BFILE data is not supported on the client.

• Use DBMS_LOB.LOADBLOBFROMFILE to load BLOB columns.

• Use DBMS_LOB.LOADCLOBFROMFILE to load CLOB and NCLOB columns.

  See Also:

  • Oracle Database PL/SQL Packages and Types Reference

  • Oracle Database SecureFiles and Large Objects Developer's Guide