Home » RDBMS Server » Server Administration » probelm creating function-based-index (sql 9.2.06,Aix)
probelm creating function-based-index [message #305401] Mon, 10 March 2008 11:37 Go to next message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

Hi guys,
I'm trying to create a function based index, really simple
but from some reason it dosen't work:

When i try:
SQL> alter session set nls_sort='CANADIAN FRENCH';
Session altered.
but when i try:
CREATE INDEX onair.prog_media_nls_houseno_idx2 ON
table_xxx (NLSSORT("HOUSE_NUMBER"
,'nls_sort=''CANADIAN FRENCH''') ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE ona_ID1
STORAGE (...)
ERROR at line 1:
ORA-12702: invalid NLS parameter string used in SQL function
When i checked the valid options:
SQL> select * from v$nls_valid_values
2 where parameter='SORT'
3 and value like '%CANAD%';

PARAMETER VALUE
--------------- -------------------
SORT CANADIAN FRENCH
SORT CANADIAN_M

Did any one ran into this problem? any suggestions?
thanks in advance.


Re: probelm creating function-based-index [message #305429 is a reply to message #305401] Mon, 10 March 2008 14:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Use CANADIAN_M. CANADIAN FRENCH is obsolete.
Re: probelm creating function-based-index [message #305430 is a reply to message #305429] Mon, 10 March 2008 14:10 Go to previous messageGo to next message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

Thanks.
That's what i did even though i found no note regarding
this value to be obsolete.
Re: probelm creating function-based-index [message #305434 is a reply to message #305430] Mon, 10 March 2008 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 10.2.0.3:
select * from v$nls_valid_values
where parameter='SORT'
and value like '%CANAD%';

PARAMETER VALUE           ISDEPRECATED 
SORT      CANADIAN FRENCH TRUE 
SORT      CANADIAN_M      FALSE 

Regards
Michel
Re: probelm creating function-based-index [message #305444 is a reply to message #305434] Mon, 10 March 2008 15:30 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It is also listed in the 11g online documentation under obsolete linguistic sorts:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/applocaledata.htm#NLSPG596
Previous Topic: UTF8 character set conversion
Next Topic: partitioning a non-partitioned table
Goto Forum:
  


Current Time: Sat Dec 10 08:51:36 CST 2016

Total time taken to generate the page: 0.06870 seconds