Before I change NLS_COMP and NLS_SORT values
Using Oracle 10g on Windows Server 2003.
We need to change the NLS_COMP and NLS_SORT values for accent insensitive comparison in queries (i.e: "á" or "a" should be the same)
We are planning to do:
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTE
So, we hope it allow us to query like:
select * from table_a where text like '%papá%'
Results:
papa
papá
But before to make these changes, I would like to know what are the current settings of NLS_COMP and NLS_SORT. How can I find them? I read about NLS_SESSION_PARAMETERS, but I think it is Oracle 11g stuff.
After I change NLS_COMP and NLS_SORT through ALTER SESSION, should I restart instance service, so the changes will be permanent?
Thank you
We need to change the NLS_COMP and NLS_SORT values for accent insensitive comparison in queries (i.e: "á" or "a" should be the same)
We are planning to do:
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTE
So, we hope it allow us to query like:
select * from table_a where text like '%papá%'
Results:
papa
papá
But before to make these changes, I would like to know what are the current settings of NLS_COMP and NLS_SORT. How can I find them? I read about NLS_SESSION_PARAMETERS, but I think it is Oracle 11g stuff.
After I change NLS_COMP and NLS_SORT through ALTER SESSION, should I restart instance service, so the changes will be permanent?
Thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
- NLS_SORT and NLS_COMP should be defined in INI.ORA or PSFILE. I should add this lines in init.ora:
NLS_COMP=ANSI
NLS_SORT=GENERIC_BASELETTE
- However, if they are not, where are those values defined?
This is my INITXXXX.ORA. As you see, there are no NLS_SORT or NLS_COMP parameter:
mtdprod.__db_cache_size=20
mtdprod.__java_pool_size=1
mtdprod.__large_pool_size=
mtdprod.__shared_pool_size
*.background_dump_dest='C:
*.compatible='10.1.0.2.0'
*.control_files='C:\oracle
*.core_dump_dest='C:\oracl
*.db_block_size=8192
*.db_domain='jne.gob.pe'
*.db_file_multiblock_read_
*.db_name='mtdprod'
*.dispatchers='(PROTOCOL=T
*.java_pool_size=157286400
*.job_queue_processes=10
*.log_archive_dest_1='LOCA
*.log_archive_format='ARC%
*.open_cursors=300
*.pga_aggregate_target=122
*.processes=500
*.remote_login_passwordfil
*.sga_target=370147328
*.shared_pool_size=1572864
*.sort_area_size=65536
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS
*.user_dump_dest='C:\oracl
However, if I do:
select * from NLS_SESSION_PARAMETERS;
Results:
NLS_SORT SPANISH
NLS_COMP BINARY
select * from nls_database_parameters;
I got these results:
NLS_SORT BINARY
NLS_COMP BINARY
I don't understand. Where are these values set or defined?