Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01843- Not a Valid Month
icon4.gif  ORA-01843- Not a Valid Month [message #179996] Thu, 29 June 2006 11:00 Go to next message
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 #179998 is a reply to message #179996] Thu, 29 June 2006 11:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What tool?
The correct term is 'loading'. In oracle terminology, 'import' means something else(bu similar).
did you try sqlldr?
Convert the file to csv and use sqlldr to load.
Most probably it has to do with the date format.
Re: ORA-01843- Not a Valid Month [message #179999 is a reply to message #179998] Thu, 29 June 2006 11:14 Go to previous messageGo to next message
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 #180000 is a reply to message #179999] Thu, 29 June 2006 11:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
may be. I have no idea how this tool handles the dates.
Re: ORA-01843- Not a Valid Month [message #180094 is a reply to message #179996] Fri, 30 June 2006 02:41 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: How to detect inserting value between ranges
Next Topic: Controling the script execution...
Goto Forum:
  


Current Time: Thu Aug 21 19:57:00 CDT 2025