Using SQL*Loader and sysdate-1

[Bungheid]

Does anyone know if you can use sysdate-n in SQL*Loader? I can use sysdate on its own fine but I cannot seem to subtract days from it

 

[karluk]

You should be able to do this with a line in your control file similar to

date_col "SYSDATE - 1"

 

[Bungheid]

Thanks but this doesn't seem to work if you are not using a data file, i.e only using data generated in the control file, the double quotes start the control file looking for a .dat file-

SQL*Loader-500: Unable to open file (test.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory

 

[karluk]

No, having the data in the control file makes no difference at all. I can use this syntax with inline data with no problems using my Oracle 8.1.7 loader. Here is a sample control file, which is partially derived from the Oracle documentation.

LOAD data
Infile *
append
INTO TABLE Test_date
( date_col "SYSDATE - 1",
col1 POSITION(01:03) CHAR "trimcol1)",
col2 POSITION(04:10) CHAR )
begindata
A 4567890
B 4567890