OraFAQ Forum: Server Administration » probelm creating function-based-index

Home » RDBMS Server » Server Administration » probelm creating function-based-index (sql 9.2.06,Aix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

probelm creating function-based-index [message #305401]

Mon, 10 March 2008 11:37

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.

Report message to a moderator

Re: probelm creating function-based-index [message #305429 is a reply to message #305401]

Mon, 10 March 2008 14:01

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Use CANADIAN_M. CANADIAN FRENCH is obsolete.

Report message to a moderator

Re: probelm creating function-based-index [message #305430 is a reply to message #305429]

Mon, 10 March 2008 14:10

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.

Report message to a moderator

Re: probelm creating function-based-index [message #305434 is a reply to message #305430]

Mon, 10 March 2008 14:13

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: probelm creating function-based-index [message #305444 is a reply to message #305434]

Mon, 10 March 2008 15:30

Barbara Boehmer
Messages: 9100
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

Report message to a moderator

Previous Topic:	UTF8 character set conversion
Next Topic:	partitioning a non-partitioned table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 23:41:56 CST 2024