Home » RDBMS Server » Server Utilities » Re: ORA-01847: day of month must be between 1 and last day of month -split from ancient thread by bb
Re: ORA-01847: day of month must be between 1 and last day of month -split from ancient thread by bb [message #545633] Thu, 01 March 2012 07:53 Go to next message
xal_kaushal
Messages: 4
Registered: January 2012
Location: India
Junior Member
I have the similar problem guys

i have following error

ORA-00604: error occurred at recursive SQL level 1
ORA-01847: day of month must be between 1 and last day of month

for this my control file is
load data
infile "/abc/def/ghi/abd.txt"
truncate into table ABC
fields terminated by ","
trailing nullcols
(ssa,account_no,msisdn,account_category,package_plan,
cutoff_start_dt "to_date (:cutoff_start_dt , 'DD-MON-YY')",
cutoff_end_dt "to_date (:cutoff_end_dt, 'DD-MON-YY')",
credit_limit,threshold_usage,outstanding,actual_thresh_usage,
alarm_90_date "to_date(:alarm_90_date,'DD-MON-YY HH24:Mi:SS')",
alarm_100_date "to_date(:alarm_100_date,'DD-MON-YY HH24:Mi:SS')",
alarm_110_date "to_date(:alarm_110_date,'DD-MON-YY HH24:Mi:SS')"
)
sample of my data is
RJ - Jhunjhunu,129561522,1593215412,Individual,BSNL CDMA GENERAL PLAN,13-JAN-12,13-MAR-12,1000,963.6,0,-36.4,29-Feb-12 07:32:47,,
JK - Jammu,158841486,9186103990,Individual,EVDO plan 750,19-FEB-12,19-MAR-12,1000,905,1545,1450,29-Feb-12 07:32:45,,
JK - Jammu,163289254,9186103658,Individual,EVDO plan 750,19-FEB-12,19-MAR-12,1000,1003.5,756,759.5,29-Feb-12 07:32:44,29-Feb-12 07:32:44,
JK - Jammu,165312136,9186104527,Individual,EVDO plan 750,27-FEB-12,19-MAR-12,1000,917,0,-83,29-Feb-12 07:32:43,,
JK - Rajouri,165200100,9186110019,Individual,EVDO plan 750,23-FEB-12,19-MAR-12,1000,911.5,0,-88.5,29-Feb-12 07:32:43,,
JK - Jammu,162389085,1922207092,Individual,BSNL CDMA GENERAL PLAN,19-FEB-12,19-APR-12,1000,1010.4,692,702.4,29-Feb-12 07:32:42,29-Feb-12 07:32:43,
JK - Jammu,160464500,9186103406,Individual,EVDO plan 750,19-FEB-12,19-MAR-12,1000,960,1545,1505,29-Feb-12 07:30:48,,
PJ - Jalandhar,159480265,1812002320,Individual,EVDO plan 750,07-JAN-12,07-MAR-12,1000,927.5,0,-72.5,29-Feb-12 07:30:47,,
RJ - Jaipur Bajaj Nagar,151639722,1412172373,Individual,BSNL ONE INDIA PLAN,13-JAN-12,13-MAR-12,1000,915,-.5,-85.5,29-Feb-12 07:30:46,,

My one row is loaded but not the others pls help me




[Updated on: Sun, 04 March 2012 16:36] by Moderator

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #545639 is a reply to message #545633] Thu, 01 March 2012 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 13658
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Please read and follow How to use [code] tags and make your code easier to read?
2) This error:
ORA-00604: error occurred at recursive SQL level 1

Generally means that the other error was raised in a DB trigger. So do have some insert triggers on the table?
Re: ORA-01847: day of month must be between 1 and last day of month [message #545661 is a reply to message #545639] Thu, 01 March 2012 10:12 Go to previous messageGo to next message
Littlefoot
Messages: 21498
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@xal_kaushal, you should have posted table description. Without it, it is difficult to reproduce what you were doing.
Re: ORA-01847: day of month must be between 1 and last day of month [message #545729 is a reply to message #545639] Thu, 01 March 2012 23:49 Go to previous messageGo to next message
xal_kaushal
Messages: 4
Registered: January 2012
Location: India
Junior Member
i am not using any trigger
i am simply loaded my given data in the table
with the help of sqlloader


regards
kaushal
Re: ORA-01847: day of month must be between 1 and last day of month [message #545730 is a reply to message #545729] Thu, 01 March 2012 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 26595
Registered: January 2009
Location: SoCal
Senior Member
>i am simply loaded my given data in the table with the help of sqlloader
CONGRATULATIONS!
then you have no problem!
Re: ORA-01847: day of month must be between 1 and last day of month [message #545735 is a reply to message #545661] Fri, 02 March 2012 00:53 Go to previous messageGo to next message
Littlefoot
Messages: 21498
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do I use invisible ink?

Littlefoot wrote on Thu, 01 March 2012 17:12
@xal_kaushal, you should have posted table description. Without it, it is difficult to reproduce what you were doing.

Re: ORA-01847: day of month must be between 1 and last day of month [message #545745 is a reply to message #545735] Fri, 02 March 2012 03:22 Go to previous message
cookiemonster
Messages: 13658
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'd also help if you told us which row loaded sucessfully.
Previous Topic: ORACLE import error
Next Topic: Expdp problem
Goto Forum:
  


Current Time: Mon Sep 16 09:29:23 CDT 2019