1

enter image description here

Hi all, I've uploaded a photo of the way I see dates in PL/SQL Developer, I see question marks instead of the full date (need to look like "02-Aug-2013 13:50:21.663" for instance, as seen on other computers)...

If someone have any idea what on my preferences makes it look like this - I'd love to know... Thanks!

UPDATE: NLS Parameters:

NLS Parameters

NLS DB Parameters:

NLS DB Parametes

Improve this question
4
  • Can you try to run the same query typing only the month? (extract month from date) Commented Sep 3, 2014 at 6:30
  • Run the query in SQL*Plus, do you still have this problem? Commented Sep 3, 2014 at 6:45
  • I don't think your NLS_CHARACTERSET supports Hebrew characters; doesn't it need to be IW8MSWIN1255? (Or UTF). The month value is controlled by NLS_DATE_LANGUAGE, which is inherited from NLS_TERRITORY, so you could change those in your client. If you're connecting to a DB set us as 'American' then making your client match may be necessary to make everything look 'right'. Your PC's locale settings may be affecting the defaults too. Commented Sep 3, 2014 at 9:02
  • Please show us the stored procedure. Maybe you are doing something wrong there. Commented Sep 3, 2014 at 11:02

3 Answers 3

Reset to default
2

The issue seems to be due to mismatch between NLS parameters in client and database. For such questions, always remember to post the OS details, DB version up to 4 decimals.

To check the the parameter values:

SELECT * FROM v$nls_parameters;
SELECT * FROM nls_database_parameters;

  1. Set the NLS_DATE_LANGUAGE in client exactly same as that of Database. In your case, NLS_DATE_LANGUAGE in database is AMERICAN.

  2. Set the environment variable for NLS_LANG as :

[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

so, set it as AMERICAN_AMERICA.WE8MSWIN1252

  1. Another observation is that, your other NLS parameters also needs to be changed in client to match with that of Database. Else, you will find similar issues every now and then, just not with DATE.
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I've uploaded the 2 photos in the main message
Bramat, so it is pretty clear now that the locale NLS parameters doesn't match with the Database NLS paramaters. To be precise, NLS_DATE_LANGUAGE is different. So when you display the date column fetched from DB in your client, the ENGLISH is shown as junk in HEBREW. The most important thing is that WE8MSWIN1252 characterset does not support Hebrew characters. You need to set the correct characterset to support HEBREW. And then, edit your environment variable to match it.
@Bramat: did this solve your problem? Please mark it as the answer to make it clearer for anyone who ends up reading your post.
1

I would like to leave here a solution that has nothing to do with the national language settings, and, as the post that lead me to this itself states, 9 out of 10 cases of problems like yours (I was having just the same) - are fixed following this solution, instead.

I have been messing with the NLS for a while to no avail. I then just ended up in this post and this was what solved my problem.

In a short, if the font chosen to display SQL results is one that does not support the language you are trying to display in, then it will not work. In my case, needing to display Hebrew, I was able to do so simply by picking Tahoma as the display font, as suggested, again, by the post aforementioned.

Here is a screenshot showing the procedure:

Preferences -> User Interface -> Fonts -> Grid -> Select

enter image description here

Improve this answer

Comments

0

You may try changing 'Date Format', 'Timestamp Format', 'Timestamp TZ Format' to "DD-MON-YYYY HH24:MI:SS" (minus quotes) or in the format you want.

Access the preferences from Tools->Preferences->Database->NLS

Improve this answer

1 Comment

It's the MON value that's the problem, so this doesn't really help?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.