Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NLS_LANGUAGE not showing up correctly in nls_database_parameters correctly

704504Aug 20 2009 — edited Aug 20 2009
I am using Oracle 11g 11.1.0.7 on a Windows 2008 server in spanish_peru

This is what comes up when I run the select * from nls_database_parameters

PARAMETER
------------------------
VALUE
------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$


PARAMETER
------------------------
VALUE
------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
WE8MSWIN1252

However, I have the nls_language set to latin american spanish and the nls_territory set to peru.
I have checked the DB Control and it shows the initialization parameters as such but the nls_database_parameters does not.

I do an Oracle Text search something like this:
select score(1)Relevancia,
dbms_lob.getlength("TRANS_DOCUMENTO") "TRANS_DOCUMENTO",
FILENAME, TRANSCRIPCION_ID,
ctx_doc.snippet
('TRANSCRIPCION_DOC_CTXIDX',
TRANSCRIPCION_ID,
:P20_SEARCH,
'<span style="color:darkgreen;font-weight:bold;font-size:14px;">',
'</span>') "Fragmento de Código"
from transcripcion_documentos
where contains(trans_documento, :P20_SEARCH, 1) > 0
and creado_fecha between (to_date(:P20_PREVIOUS, 'dd/MM/yyyy')) and (to_date(:P20_NEXT, 'dd/MM/yyyy'))
order by 1 desc

It will not return any data. I remove the following line:
and creado_fecha between (to_date(:P20_PREVIOUS, 'dd/MM/yyyy')) and (to_date(:P20_NEXT, 'dd/MM/yyyy'))
and the results are shown.

Any ideas as to how to change this script to return data. I have tried without the to_date statement and still no data is returned. Is there any issue with the nls_database_parameters showing one thing and the init parameters showing another.

Is there some type of Alter system script that changes these parameters? Although making sure the nls_language and nls_territory were listed in the pfile and spfile should have been good enough.
Any ideas as to what to try next??
This post has been answered by Nicolas Gasparotto on Aug 20 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 17 2009
Added on Aug 20 2009
17 comments
3,743 views