Using the NLS support in where

From: Dieter Oberkofler <lbits_at_via.at>
Date: 1995/04/13
Message-ID: <3mju7i$qdd_at_ns2.via.at>#1/1


I have the following problem:
When using the german character set my Oracle 7.1 Server is not able to perform correct selects using a where. Using a select like "select Match from Demo where Match >= 'ÖS';" does not work because Oracle seems always to use the binary compares when evaluating the where. A order by works fine because it seems to use the according NLS-parameter for the current session using the NLS string compares.
I have the following questions:

  1. If i'am right, why does Oracle use different methodes in handling where and order by ?
  2. Is the only possible solution just to use a redundant column holding the NLS-value "NLSSORT('ÖS', 'NLS_SORT = German')" and then use this column for the where?
  3. How can i use an index in order to optimize the select without using the methode describet in 2) ?

I called the Oracle Support Centers in Austria and Germany and the

answer was simply: 	'Don't use indizes' and
			'Be careful when using where'

Thank you for any help or comment.

Dieter Oberkofler
Vienna Received on Thu Apr 13 1995 - 00:00:00 CEST

Original text of this message