ORA-00936: missing expression in SQL*LOADER control file

Question

I have a control.ctl file and I'm using SQL*LOADER to load this data in the table.

LOAD DATA
INFILE 'data_for_insert.csv'
INSERT INTO TABLE TABLE_NAME
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1,
COL2,
NEXT_MONDAY EXPRESSION "SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual",
TODAY SYSDATE
)

Error received:

Record 16: Rejected - Error on table TABLE_NAME, column NEXT_MONDAY .
ORA-00936: missing expression

I can't find what is the problem because the expression SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual works when I run it in SQL Developer. If I take that expression out it works so there's no problems in the rest of the code. Can someone help? thanks!

Answer 1

If that's an expression, then use an expression, not the whole query; not that it won't work (you found out that it actually works), it's just unnecessary.

Control file:

load data
infile *
replace
into table test
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(col1,
 col2,
 next_monday expression "next_day (sysdate, 'MONDAY')",
 today       sysdate
)

begindata
1,2
3,4

Testing:

SQL> $sqlldr scott/tiger control=test9.ctl log=test9.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pet Stu 26 21:24:37 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL> select * from test;

      COL1       COL2 NEXT_MONDA TODAY
---------- ---------- ---------- ----------
         1          2 29.11.2021 26.11.2021
         3          4 29.11.2021 26.11.2021

SQL>