Sqlldr error

I am not able to load date in format DDMMYYYY into DT column of table abc.

Please help

--Table
CREATE TABLE abc
(
  col1                  VARCHAR2(2),
  col2              VARCHAR2(9),
  col3                  VARCHAR2(2000),
  DT                    DATE
)

-sqlldr control file (abc.ctl)
LOAD DATA
INSERT
INTO TABLE abc
(
col1 position (01:02),
col2 position (03:11),
col3 position (12:1390),
DT "to_date(SYSDATE,'DDMMYYYY')"
)

--load data into abc table
sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/abc.ctl DATA=abc.dat LOG=abc.log BAD=abc.bad DISCARD=abc.dsc

Error while loading data as direct path.
SQL*Loader-417: SQL string (on column DT) not allowed in direct path.

Error while loading data under conventional path
Record 1: Rejected - Error on table abc, column DT.
ORA-01858: a non-numeric character was found where a numeric was expected
gram77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anandmahajanCommented:
Use something like this :

search_date DATE "MM/DD/YYYY" "
,deleted
,matches
,birthday_start DATE "MM/DD/YYYY"
,birthday_end DATE "MM/DD/YYYY "
,search_id
,date_search_deleted
0
anandmahajanCommented:
maybe for sysdate u need to say sysdate instead on DATE.

DO let let me know if that works else just send me sample data file. I will try to load it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
Since ur DT column is a date-type, just using SYSDATE will populate it with the current date and u don't have to specify any format identifiers.

In ur control file, just try - DT SYSDATE or DT "SYSDATE"

And then after its loaded, SELECT TO_CHAR(dt,'DDMMYYYY') FROM abc;

To alter the way Oracle displays a date-value, try this.

ALTER SESSION SET NLS_DATE_FORMAT = 'DDMMYYYY';
0
schwertnerCommented:
IF you define the table so:
CREATE TABLE abc
(
  col1                  VARCHAR2(2),
  col2              VARCHAR2(9),
  col3                  VARCHAR2(2000),
  DT                    DATE default sysdate
);

you can use this

LOAD DATA
INSERT
INTO TABLE abc
(
col1 position (01:02),
col2 position (03:11),
col3 position (12:1390)
)

Otherwise use

LOAD DATA
INSERT
INTO TABLE abc
(
col1 position (01:02),
col2 position (03:11),
col3 position (12:1390),
DT    sysdate
)


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.