Skip to Main Content
  • Questions
  • Creating linguistic indexes for CANADIAN FRENCH

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Myron.

Asked: June 27, 2018 - 12:45 pm UTC

Last updated: July 01, 2018 - 5:10 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

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:

and Connor said...

There is this note in the docs

CANADA_FRENCH

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.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great response

Myron Wintonyk, July 01, 2018 - 5:21 am UTC

I spent hours trying to find this answer, and in the end, it was so simple. I just had to read the documentation. There’s just so much of it.

Anyway, thanks for the info. It will prove very useful!

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database