hi all

i have got a problem about nlssort statement.

i use oracle 10g r2 (32 bit on amd64) with windows 2003 server r2 (32
bit)
we have special characters in turkish (i dont know you can see
correctly.they are;öÖçÇiIsSüÜgG)

i create a database with windows 1254 character set(8 bit) which
supports these special character. But, when i use select with ordey by
keyword then the words which start with turkish character show up at
the bottom of the results.

After that i invent the nlssort :P(i guess it is a function).it works
when use it like below

select * from GECOUST order by NLSSORT(ad,'NLS_SORT=XTURKISH')

it seem ok.But,i also see a usage at documents of oracle.it says 'u can
use NLSSORT with create index statement' and i use it.

CREATE INDEX nls_index ON my_table (NLSSORT(name, 'NLS_SORT =
XTURKISH'));

but it doesnt work.

can anybody help me.i wanna use it with create index statement so i
dont have to use NLSSORT statement with ORDER BY everytime.

Thanks for attention

MadeOfRose <mehmetgulden (AT) gmail (DOT) com> wrote:
You are mixing some things up here.

Creating an index in a specific sort order will not influence how
query results are sorted.

It will only influence whether the index is used or not if you sort in a
specific sort order.

What you need to do is set NLS_SORT=XTURKISH in your session parameters:
ALTER SESSION SET NLS_SORT=XTURKISH;

Then the following query will do what you want:
select * from GECOUST order by ad;

You can set NLS_SORT on different levels:
- As database initialization parameter via ALTER SYSTEM.
- As session parameter via ALTER SESSION.
- As session parameter via the environment variable NLS_SORT.

The session parameter will override the initialization parameter, so be
careful.

What I would recommend if you want all sorts in Turkish is:

- Alter the initialization parameter NLS_SORT to XTURKISH.
Then restart the instance.
- Set the environment variable NLS_SORT on your clielect * from GECOUST
order bynt to XTURKISH.

Then your sort should work as desired, and the index should be used
(verify it with an EXPLAIN PLAN).

Yours,
Laurenz Albe

Thank you for well explained answer.
i use ALTER SESSION statement and it works very well.
do i have to anything at oracle if i set the NLS_SORT=XTURKISH
environment variable?
how does oracle understand the environment variable?

Thank you again

regards
Mehmet Gulden

Thank you for well explained answer.
i use ALTER SESSION statement and it works very well.
do i have to anything at oracle if i set the NLS_SORT=XTURKISH
environment variable?
how does oracle understand the environment variable?

Thank you again

regards
Mehmet Gulden

MadeOfRose wrote:
See the globabization guide:
http://download-west.oracle.com/docs...v.htm#g1012681

You need to remember to export environment variables in unix
environments, syntax is dependent on shell. For example, my korn shell
would like:

export NLS_SORT=XTURKISH

jg
--
@home.com is bogus.
No room for downtime or error... on commodity systems...
http://www.alphatradefn.com/story/20...___BW5201.html

MadeOfRose <mehmetgulden (AT) gmail (DOT) com> wrote:
Setting the environment variable will determine the default setting for
the session parameter - if you don't set the environment, you'll have
to ALTER SESSION at the beginning of each session.
ALTER SESSION will override the default setting from the environment
variable.

The choice is yours.

Basically, if you set the environment variable, everything should be fine.

I would probably also change the initialization parameter with ALTER SYSTEM
and restart the instance, just to make sure.
I don't know if this setting will have any effect at all, but maybe it
applies for situations where there is no session started from a client
machine (jobs or the like).

Maybe somebody else knows more about that.

Yours,
Laurenz Albe

thanks

i set the environment variable and it is working fine.

know i have another problem about the turkish characters

my database's character set is 'windows 1254'. When i use the INSERT
INTO with oracle raptor all the special characters seem well.
But, when i use the oracle oledb drivers with ASP (via ADO) , special
characters seem like question mark.
is there any way to determine character set to OLEDB.I could not find
the connection string parameter.
(i wanna use this character set because we have an ERP software and
this ASP site is going to work integrated with it.
they will communicate via XML files.And our ERP database use 8 bit
character set.)

Thank you
regards

MadeOfRose schreef:
ability of the program to display these characters.
You front-end does not understand them, and displays
a question mark.

Try opening a command line interface ("DOS-box"); perform
the select - probably you will see the same "wrong" results.
Now open the properties of the CLI, open the Fonts tab, and
select Lucinda Console for font. If your select does not display
correctly, exit you SQL, type 'chcp 1254', and retry.
Betcha it works!

chcp, by the way, stands for change code page.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

it is not suppose to be about displaying characters.
Because i use internet explorer to display character.
I tried many types op ch sets but it doesnt works.
Also i use raptor but it was same result.

But when i change regional setting (from advanced tag ->language of
non unicode programs use) it works great.
is there any risk to use these turkish settings (because i must use
english settings when i install the oracle db.All i wanna know is it
gonna make any problem with that turkish settings)

thanks

Mehmet Gulden