[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • Last Modified:

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
0
gram77
Asked:
gram77
  • 2
3 Solutions
 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now