When creating a linguistic index, I am not able to specify CANADIAN FRENCH. Oracle reports that the NLS parameter string is invalid. I suspect that it's because there is a space in it, but the answer eludes me. Here is a short example of a script that works properly in FRENCH, but not CANADIAN FRENCH. Note that setting the NLS_SORT is not required, but I used that to confirm that I was spelling CANADIAN FRENCH correctly.
Both of these statement result in "Session Altered".
alter session set nls_sort = "FRENCH";
alter session set nls_sort = "CANADIAN FRENCH";
However, only the first of these statements result in a successful index creation:
create index NAMES_FRENCH on PS_NAMES ( NLSSORT( NAME, 'NLS_SORT=FRENCH' ) );
create index NAMES_CANADIAN_FRENCH on PS_NAMES ( NLSSORT( NAME, 'NLS_SORT=CANADIAN FRENCH' ) );
The second gives this result:
Error starting at line 102 in command:
create index NAMES_CANADIAN_FRENCH on PS_NAMES ( NLSSORT( NAME, 'NLS_SORT=CANADIAN FRENCH' ) )
Error at Command Line:102 Column:39
Error report:
SQL Error: ORA-12702: invalid NLS parameter string used in SQL function
12702. 00000 - "invalid NLS parameter string used in SQL function"
*Cause: An unknown parameter name or invalid value is specified in a NLS
parameter string.
*Action:
There is this note in the docs
So I try this:
SQL> create table t ( name varchar2(100));
Table created.
SQL>
SQL> create index NAMES_FRENCH on t ( NLSSORT( NAME, 'NLS_SORT=FRENCH' ) );
Index created.
SQL> create index NAMES_CANADIAN_FRENCH on t ( NLSSORT( NAME, 'NLS_SORT=CANADIAN FRENCH' ) );
create index NAMES_CANADIAN_FRENCH on t ( NLSSORT( NAME, 'NLS_SORT=CANADIAN FRENCH' ) )
*
ERROR at line 1:
ORA-12702: invalid NLS parameter string used in SQL function
SQL> create index NAMES_CANADIAN_FRENCH on t ( NLSSORT( NAME, 'NLS_SORT=CANADIAN_M' ) );
Index created.