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

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!

CodePudding user response：

In the end this solution seems to have solved the problem. I added parentheses inside the double quotes.

NEXT_MONDAY EXPRESSION "(SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual)"

CodePudding user response：

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>