I have the following control file:
load data
infile *
into table xyz replace
fields terminated by x'9'
trailing nullcols
(load_date date 'yyyymmdd' "nvl(:load_date, to_char(trunc(sysdate),'yyyymmdd'))",
ubs_entity char(9),
some_type char(5)
)
BEGINDATA
XY/F0000 WXYZ
YX/E0000 ZYXW
XX/D0000 ABCD
The table that is loaded into is:
create table xyz (
load_date date,
entity varchar2(20),
some_type varchar2(15)
)
partition by range (load_date)
interval( numtodsinterval(1, 'DAY'))
(partition part_01
values less than (to_date(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) )
;
I get the following sql loader exception
Record 3: Rejected - Error on table XYZ, column LOAD_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Question is, how to prefix the load with trunc(sysdate) as a first column, since nvl() seems not to be working?
Ultimately, I would like be able to perform a direct load, since the volumes are 10 million+ per load, and may occur several times a day, so I might have to include a "load_version" column as well.