0

I am trying to create a control file for SQL*Loader script. I have a date column that is defined as NOT NULL. I need to set-up the script to use current date, if the incoming value from the file is blank. I tried a few ways - some examples below - but I keep getting below error when running sql loader, but the format works if I do select from dual

How do I need to set it up in SQLLoader?

  PROGRAM_CHANGE_TS     POSITION(37:47) DATE "decode(length(trim(:PROGRAM_CHANGE_TS)), 11, to_date(:PROGRAM_CHANGE_TS,'yymmddHH24:MI'),to_date(sysdate,'yymmddHH24:MI'))",
  PROGRAM_CHANGE_TS     POSITION(37:47) DATE "NVL(Decode(:PROGRAM_CHANGE_TS,'00/00/0000',TO_DATE('01/01/2010','dd/mm/yyyy'),TO_DATE(:PROGRAM_CHANGE_TS,'dd/mm/yyyy')),TO_DATE('01/01/2010','dd/mm/yyyy'))",

oracle decode example ORA-00907: missing right parenthesis

select decode(length(trim(:PROGRAM_CHANGE_TS)), 11, to_date(:PROGRAM_CHANGE_TS,'yymmddHH24:MI'),to_date(sysdate,'yymmddHH24:MI')) from dual

or

select NVL(Decode(:PROGRAM_CHANGE_TS,'00/00/0000',TO_DATE('01/01/2010','dd/mm/yyyy'),TO_DATE(:PROGRAM_CHANGE_TS,'dd/mm/yyyy')),TO_DATE('01/01/2010','dd/mm/yyyy')) from dual

works

What changes do I need to make to use it from a sql loader control file?

adbdkb
  • 1,897
  • 6
  • 37
  • 66

2 Answers2

0

You're using to_date in the ctl-file, so remove the explicit DATE specifier.

Also, you don't need to use use decode and length, nvl along with trim is the way to go.

Finally, sysdate is already a date, so you don't need to explicitely convert it into a date with to_date.

The following should work (not tested):

PROGRAM_CHANGE_TS POSITION(37:47) "nvl(to_date(trim(:PROGRAM_CHANGE_TS),'yymmddHH24:MI'),sysdate)",
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • Sorry, added the comments to above by mistake. I tested this NVL. Thanks. I used the NVL part and now it is going further in terms of getting the ORA-00907 error. That error is now eliminated. I am still getting the below error when incoming data is spaces - column PROGRAM_CHANGE_TS. ORA-01858: a non-numeric character was found where a numeric was expected . How can I resolve that part? Also one other question - the non-blank data has 2 digit year, so it is getting inserted as 20YY for all, how can I change this such that anything higher than 70 will be translated to 19YY? – adbdkb Jan 08 '15 at 08:34
  • We can help you better if you posted your `create table` statement your `*.ctl` file and a few records of sample data. – René Nyffenegger Jan 08 '15 at 08:45
0

I need to set-up the script to use current date, if the incoming value from the file is blank.

If you get NULL value for the date column, then simply use NVL function to load SYSDATE instead.

PROGRAM_CHANGE_TS POSITION(37:47) DATE "NVL(:PROGRAM_CHANGE_TS, SYSDATE)"

Don't be confused between BLANK and NULL. 00/00/0000 is neither blank nor null. If you are getting 00/00/0000 as the value, then use DECODE.

PROGRAM_CHANGE_TS POSITION(37:47) DATE "DECODE(:PROGRAM_CHANGE_TS, '00/00/0000', SYSDATE, :PROGRAM_CHANGE_TS)"

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thanks. I used the NVL part and now it is going further in terms of getting the ORA-00907 error. That error is now eliminated. I am still getting the below error when incoming data is spaces - `column PROGRAM_CHANGE_TS. ORA-01858: a non-numeric character was found where a numeric was expected `. How can I resolve that part? – adbdkb Jan 08 '15 at 08:22
  • Also one other question - the non-blank data has 2 digit year, so it is getting inserted as 20YY for all, how can I change this such that anything higher than 70 will be translated to 19YY? – adbdkb Jan 08 '15 at 08:30
  • Use `RR` format instead of `YY`. `select to_date('01/01/72', 'dd/mm/rr') from dual` – Lalit Kumar B Jan 08 '15 at 08:32
  • @adbdkb, you sure the `NVL` throws `ORA-00907 `? – Lalit Kumar B Jan 08 '15 at 08:35
  • Wrap :PROGRAM_CHANGE_TS in TRIM(): "NVL(TRIM(:PROGRAM_CHANGE_TS), SYSDATE)". That way NVL should see NULL instead of spaces. – Gary_W Jan 08 '15 at 21:18