OraFAQ Forum: Server Utilities » Re: ORA-01847: day of month must be between 1 and last day of month -split from ancient thread by bb

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

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

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

cookiemonster
Messages: 13966
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?

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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!

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

It'd also help if you told us which row loaded sucessfully.

Report message to a moderator

Previous Topic:	ORACLE import error
Next Topic:	Expdp problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:48:59 CST 2025