ORA-01843- Not a Valid Month [message #179996] |
Thu, 29 June 2006 11:00  |
harsha18
Messages: 4 Registered: May 2006 Location: Hyderabad
|
Junior Member |
|
|
Hi....I am importing data into an oracle database using a tool. While importing data into the db from an excel file, I am getting the error "ORA-01843: not a valid month". I have 2 date columns in the excel file which go into 2 columns in the database. I have exhausted all the options in the excel date format but none of them seem to work. Please help me on how to resolve this issue.
Bye,
Harsha
|
|
|
|
Re: ORA-01843- Not a Valid Month [message #179999 is a reply to message #179998] |
Thu, 29 June 2006 11:14   |
harsha18
Messages: 4 Registered: May 2006 Location: Hyderabad
|
Junior Member |
|
|
The tool is called VagumDB and is very much similar to Toad in look and operation. Am unable to use sqlldr because of some constraints(read lack of availability with client provided software). Can changing the date format to any other help get over the issue?
|
|
|
|
Re: ORA-01843- Not a Valid Month [message #180094 is a reply to message #179996] |
Fri, 30 June 2006 02:41   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I presume that the data is too large to inspect the date columns and spot the problems
Well, you could try loading the data into a staging table that has varchar2 columns instead of date columns.
Once the data is in the staging table, you can run something like this to spot the invalid dates
SQL> create or replace function valid_date (p_date in varchar2
2 ,p_format in varchar2 default 'dd-mm-yyyy')
3 return varchar2 as
4 v_date date;
5 BEGIN
6 v_date := to_date(p_date,p_format);
7 return 'Y';
8 exception
9 when others then
10 return 'N';
11 end;
12 /
Function created.
SQL> create table temp_date_test (col_1 number,col_2 varchar2(10));
Table created.
SQL> insert into temp_date_test values (1,'01-01-2000');
1 row created.
SQL> insert into temp_date_test values (2,'01-02-2000');
1 row created.
SQL> insert into temp_date_test values (3,'01-99-2000');
1 row created.
SQL> insert into temp_date_test values (4,'Halibut');
1 row created.
SQL> select col_1,col_2
2 from temp_date_Test
3 where valid_date(col_2,'dd-mm-yyyy') = 'N';
COL_1 COL_2
---------- ----------
3 01-99-2000
4 Halibut
|
|
|
Re: ORA-01843- Not a Valid Month [message #180248 is a reply to message #180094] |
Sat, 01 July 2006 15:10  |
harsha18
Messages: 4 Registered: May 2006 Location: Hyderabad
|
Junior Member |
|
|
Thanks for all your help,JRowBottom...I was able to overcome the error by temporarily altering the session date format using the Alter Session statement.
Alter session set nls_date_format='MM/DD/YYYY';
Thanks,
Harsha18
|
|
|