0

Hi I'm trying to use collate on a view which has column name per_va_first_name when I use the following query :

SELECT *
 FROM person_view
 WHERE NLSSORT(per_va_first_name, 'NLS_SORT = FRENCH_AI') = NLSSORT('mickaël', 'NLS_SORT  =FRENCH_AI')

I get the error

ORA-12702: invalid NLS parameter string used in SQL function

I'm new to oracle and this nlssort. Can anyone help me in pointing out what's my mistake? And at the same time I want to use collate in Hibernate for Java. Same french char set.

Edit:

When I use these commands in sql

alter session set nls_sort=French_AI;
alter session set nls_comp=linguistic;

I get the desired output when this query is executed

SELECT * FROM v_myuser_search_test_ea4 where per_va_first_name like 'Mickaël%'

How to do this in Hibernate? Is there a way I can append 'CI' to French_AI to make it 'French_AI_CI'

Aleksandr M
  • 24,264
  • 12
  • 69
  • 143
RaMs_YearnsToLearn
  • 459
  • 6
  • 11

1 Answers1

-1

According to Oracle documentation found on http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions113.htm#SQLRF51562

you might change your query to

SELECT *
FROM person_view
ORDER BY NLSORT(per_va_first_name, 'NLS_SORT = FRENCH_AI_CI')

Hibernate should understand it, however you are already losing database portability as this is Oracle-specific function.

Magic Wand
  • 1,572
  • 10
  • 9
  • I tried it, in vain. It just returns me one record with name as 'mickaël' not all the records with 'mickael'. Bdw I was making mistake with French_CI_AI. I dunno what's the problem but either supports French_AI or French_CI but not both. – RaMs_YearnsToLearn Jul 18 '14 at 10:17
  • sorry, I did not understand your initial question. Can you alter properties on database level for NLS_SORT and NLS_COMP? – Magic Wand Jul 18 '14 at 13:48
  • As far as I remember, Oracle JDBC driver does not have an option to set these parameters, it uses underlying VM character set and encoding for collators. I had similar problem with WebSphere application server on Linux talking to Oracle database that was on UTF-8. Collators did not work until I set VM properties user.language and user.country (for your case it would be -Duser.language=fr -Duser.country=FR). – Magic Wand Jul 18 '14 at 13:58