0

I have a text file and it contains one column as "NPA_DATE", in which few rows contain date as "00-00-000".

I am unable to load data where date is in "00-00-0000" format. I am getting the error as "Record 1: Rejected - Error on table MIS_PNPA, column NPA_DATE. ORA-01847: day of month must be between 1 and last day of month". However I want to load the data with NPA_DATE as "00-00-0000" along with other data. All other rows are getting uploaded in database. I use toad for oracle 9.0.1.8

Beginner to professional
  • 3
  • 3
  • 00 is a invalid date and month. Why don't you replace "00-00-0000" with null or [minimum date](https://stackoverflow.com/questions/687510/how-do-you-obtain-the-maximum-possible-date-in-oracle) – Knu8 Sep 22 '16 at 09:56
  • Thanks for your prompt reply Knu8, however please let me know how to replace date "00-00-0000' by null in control file (.ctl) as it is a daily process to truncate the table and insert the new/updated data in table. My ctl file is LOAD DATA INTO TABLE mis_pnpa FIELDS TERMINATED BY '|' TRAILING NULLCOLS (sr_no,branch_code,account_no,name1,acct_type,int_cat,product_name,appv_amt,adv_amt,theo_bal,out_amt,srf_amt,security_amt,arrear,inca,uipy,act_outstand,npa_date date 'DD-MM-YYYY',old_irac,new_irac,arrear_cond,sys,proc_date date 'DD/MM/YYYY') – Beginner to professional Sep 22 '16 at 11:20
  • Why dont you replace "00-00-0000' with null in the text file itself. – Knu8 Sep 22 '16 at 11:23
  • Dear Knu8, I have done the automation of updating the database on daily basis. Almost 20 tables gets updated on daily basis by .bat file. If it has been a one time exercise I would have replaced it in text file but its an ongoing process which will happen daily. So if some code is there which we can put in control file (.ctl), so that it can be uploaded on daily basis via automation. – Beginner to professional Sep 22 '16 at 11:42
  • I am pretty much sure that you can write code to replace some text in .txt file. – Knu8 Sep 22 '16 at 11:52

1 Answers1

0

You should add the SQLPlus tag to your original post as this is really a SQLPlus question.

Anyway alter your control file to this to set the date to NULL if it comes in as '00-00-0000' in the data file:

...
npa_date date "DD-MM-YYYY" NULLIF (npa_date="00-00-0000")
...
Gary_W
  • 9,933
  • 1
  • 22
  • 40