2

We use Oracle 10.2.0.4.0 database, oracle form builder and report builder for creating forms and reports.

Now the problem is in our production database nls_date_format is dd-mon-rr format. When developer create form in developer suit they give dd-mm-rr format at form level and when data stored in table that date format is dd-mm-rr.

Now when developer run form or report within form builder it gives dd-mm-rr format.but when same form or report run from application server side it gives junk characters in month.date and year print same as date format only month display in junk characters.

Hope you all guide well.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Jayesh
  • 46
  • 9

1 Answers1

3

There are two issues.

when data stored in table that date format is dd-mm-rr.

This is completely wrong. Oracle doesn't store the date in the format you see, what you see is for display. Oracle stores DATE in an internal proprietary format in 7 bytes with each byte representing different elements of the DATE.

Byte    Description
----    -------------------------------------------------
1       Century value but before storing it add 100 to it
2       Year and 100 is added to it before storing
3       Month
4       Day of the month
5       Hours but add 1 before storing it
6       Minutes but add 1 before storing it
7       Seconds but add 1 before storing it

Do not depend on the locale_specific NLS_DATE_FORMAT. Always use:

  • TO_CHAR to display the date in your desired format
  • TO_DATE to explicitly convert the string into date.

Remember, TO_DATE is NLS dependent.

If you only have a date element, and if you do not care about the time element, then better use ANSI Date literal which follows a fixed format 'YYYY-MM-DD'.

only month display in junk characters

This is again because you are depending on the NLS_DATE_LANGUAGE. As I said, you should avoid depending on the locale-specific client settings. Explicitly mention the NLS_DATE_LANGUAGE or use ANSI Date literal if you are not concerned about the time element.

For example,

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RR') dt FROM DUAL;

DT
---------
26-OCT-15

SQL> alter session set nls_date_language='french';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RR') dt FROM DUAL;

DT
-----------
26-OCT. -15

So, what happened above? for a person using FRENCH nls_date_language, the MONTH is showing junk value. Let's make it NLS independent by explicitly mentioning the nls_date_language.

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RR', 'nls_date_language=english') dt FROM DUAL;

DT
---------
26-OCT-15

Also, the NLS_LANG value might not be correctly set in the OS environmental variable. See Why are junk values/special characters/question marks displayed on my client?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • we already used to_char in oracle forms developing for converting DD-MM-RR but still problems..in oracle application server side. – Jayesh Oct 26 '15 at 06:56
  • @jayeshkamaliya I can bet that you are depending on `NLS_DATE_LANGUAGE` of the client. See the updated example. – Lalit Kumar B Oct 26 '15 at 06:57
  • sir in our database nls_language_date is ameriacan and in oracle application forms in that registry nls_lang is GUJARATI_INDIA.UTF8 this can be the reason of this issue? – Jayesh Oct 26 '15 at 07:35
  • @jayeshkamaliya Yes, it would be an issue. Your client OS NLS_LANG doesn't support the characters you want to display. See [Why are junk values/special characters/question marks displayed on my client?](http://lalitkumarb.com/2014/09/24/why-are-junk-values-displayed-in-my-client/) – Lalit Kumar B Oct 26 '15 at 08:23
  • thank you sir .but i check in production database both have same value.but the main problem is when we run the form from Oracle application server side then it gives junk character and in oracle application server side i check nls_lang is GUJARATI_INDIA.UTF8, so it can be reason of junk character. – Jayesh Oct 26 '15 at 09:50
  • In your application server, the NLS_LANG must support `GUJARATI_INDIA.UTF8`. Do one thing, in your app server, spool the output as **HTML** file and open in a browser. You will be able to see as most browsers have the capability to display multiple character sets. That way you can be confirmed that database is sending the correct value, but app server is unable to display. – Lalit Kumar B Oct 26 '15 at 10:37
  • sir i couldnt get you what are you saying.any reference link for that so i can read and then apply on my production server.thank you sir for support. – Jayesh Oct 26 '15 at 11:40
  • Just open SQL*Plus in your application server, search google how to spool SQL query result in SQL*Plus, spool the output of `SELECT TO_CHAR(SYSDATE, 'DD-MON-RR') FROM DUAL; as `output.html`. – Lalit Kumar B Oct 26 '15 at 13:15