Home » SQL & PL/SQL » SQL & PL/SQL » Unable to Load the English months in Staging Table. (Aix based system)
Unable to Load the English months in Staging Table. [message #303361] Fri, 29 February 2008 00:32 Go to next message
ctr8130
Messages: 2
Registered: January 2008
Junior Member
Hi Buddies,
I am Rajesh from India working in one of the MNC's.
I am struck up with one of the issue assigned to me,i will be very grateful to you,if anyone can help me out in this issue.

Problem :
There is an concurrent program 'xxx_xxxpoimport'.
what this program basically does is it will load the data from data file in the specified path to the staging table(using sqlloader).
The issue we are facing is we are unable to load this particular English months like 'Dec,Oct,May,Mar'.
when i ran the program 'xxx_xxxpoimport' its rejecting all the data which will start in this months''Dec,Oct,May,Mar',and returns the following error.
ORA-01843: not a valid month.
But when tried replacing this following months with 'Dez,Okt,Mai' in the data file,its working fine and loading the data into the staging table also.
ultimately i came to the conclusion it will accept only german
months,since they are using German calendar.when i checked in the front end level setup also,German calendar has been set.

But our client is saying its accepting all the english months with out any rejection of data in one of the user acceptance instances,where the German calendar has been set.

What i need to know from u guys is whether any setupwise or any oracle standard patches need to be applied to rectify this issue.

The issue we are facing is in 10G Database and application is
11.5.9

But this error is not happening in our 8i database.

if any one of you faced the same issue in 10G DATABASE,Please let me know.
So Kindly let me know your suggestions also,to further proceed in this issue.
If you need source code also i can provide that.

Thanks
Rajesh.
Re: Unable to Load the English months in Staging Table. [message #303368 is a reply to message #303361] Fri, 29 February 2008 01:09 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
useful link

http://www.itk.ilstu.edu/docs/oracle/server.101/b10749/ch3globenv.htm

regards,
Re: Unable to Load the English months in Staging Table. [message #303370 is a reply to message #303361] Fri, 29 February 2008 01:15 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set your NLS_LANG variable to a language corresponding to your month or use an explicit TO_DATE function in your control file.
Here's an example in SQL*Plus:
SQL> select to_date('01 Janvier 2008','DD Month YYYY') from dual;
TO_DATE('01JANVIER2
-------------------
01/01/2008 00:00:00

1 row selected.

SQL> select to_date('01 January 2008','DD Month YYYY') from dual;
select to_date('01 January 2008','DD Month YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('01 January 2008','DD Month YYYY','NLS_DATE_LANGUAGE=AMERICAN') from dual;
TO_DATE('01JANUARY2
-------------------
01/01/2008 00:00:00

1 row selected.

Regards
Michel

Previous Topic: check current parameter file
Next Topic: Invalid text is stored which is > 4000 length in nclob field
Goto Forum:
  


Current Time: Tue Dec 06 12:28:51 CST 2016

Total time taken to generate the page: 0.08273 seconds