Home » RDBMS Server » Server Utilities » date format in control file (sql loader) (oracle 10g)
date format in control file (sql loader) [message #609765] Wed, 12 March 2014 01:58 Go to next message
vivek_veri
Messages: 8
Registered: September 2013
Location: INDIA
Junior Member
Hi all,

I am trying to load my data from a text file to an oracle table using sql loader. What should be the syntax of date MASK for the below kind of data.
621228487|3/9/2014 6:20:26 PM

I am using the following control file

LOAD DATA
INFILE 'SQL_LDR_DT_EXP.txt'
INTO TABLE SQLLDR_EXP
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
( TX_ID,
DT DATE "DD-MON-YYYY HH24:MI:SS" NULLIF (DT=BLANKS)
)

Please help me with this.

when i use above format i am getting error in log file as


Record 1: Rejected - Error on table SQLLDR_EXP, column DT.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 2: Rejected - Error on table SQLLDR_EXP, column DT.
ORA-01843: not a valid month
Re: date format in control file (sql loader) [message #609766 is a reply to message #609765] Wed, 12 March 2014 02:01 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Think! What does
3/9/2014 6:20:26 PM have to do with
DD-MON-YYYY HH24:MI:SS? They must match!
Re: date format in control file (sql loader) [message #609769 is a reply to message #609766] Wed, 12 March 2014 02:49 Go to previous messageGo to next message
vivek_veri
Messages: 8
Registered: September 2013
Location: INDIA
Junior Member
i have change the date format

LOAD DATA
INFILE 'SQL_LDR_DT_EXP.txt'
INTO TABLE SQLLDR_EXP
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
( TX_ID,
DT DATE "mm/dd/yyyy hh24:mi:ss" NULLIF (DT=BLANKS)
)

still getting same error



able SQLLDR_EXP, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TX_ID FIRST * | CHARACTER
DT NEXT * | DATE mm/dd/yyyy hh24:mi:ss
NULL if DT = BLANKS

Record 1: Rejected - Error on table SQLLDR_EXP, column DT.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 2: Rejected - Error on table SQLLDR_EXP, column DT.
ORA-01830: date format picture ends before converting entire input string


Table SQLLDR_EXP:
0 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Re: date format in control file (sql loader) [message #609770 is a reply to message #609769] Wed, 12 March 2014 03:05 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This format mask still doesn't match input data. You don't use 24 hours but AM/PM. Try with control file
LOAD DATA 
INFILE *
INTO TABLE SQLLDR_EXP 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS 
( TX_ID,
  DT DATE "mm/dd/yyyy hh:mi:ss pm"
)

begindata
621228487|3/9/2014 6:20:26 PM

Loading session
M:\a1_maknuto>sqlldr scott/tiger@ora10 control=test12.ctl log=test12.log

SQL*Loader: Release 11.2.0.1.0 - Production on Sri O×u 12 09:02:32 2014

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

Commit point reached - logical record count 1

M:\a1_maknuto>

What's been done:
M:\a1_maknuto>sqlplus scott/tiger@ora10

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 12 09:02:51 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> select * from sqlldr_exp;

     TX_ID DT
---------- -------------------
 621228487 09.03.2014 18:20:26

SQL>

[Updated on: Wed, 12 March 2014 03:05]

Report message to a moderator

Re: date format in control file (sql loader) [message #609777 is a reply to message #609770] Wed, 12 March 2014 04:14 Go to previous messageGo to next message
vivek_veri
Messages: 8
Registered: September 2013
Location: INDIA
Junior Member
thanks lot sir,
its working for 3/9/2014 6:20:26 PM

but its not working if my text file contains both records i.e (if text file contains both am and pm)

RAW_ID|DATE_OF_TRANSACTION
621228487|3/9/2014 6:20:26 PM
621228489|3/9/2014 4:20:26 AM

Re: date format in control file (sql loader) [message #609779 is a reply to message #609777] Wed, 12 March 2014 04:22 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does it mean "its not working"? It works for me:

Control file:
LOAD DATA 
INFILE *
INTO TABLE SQLLDR_EXP 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS 
( TX_ID,
  DT DATE "mm/dd/yyyy hh:mi:ss pm"
)

begindata
621228487|3/9/2014 6:20:26 PM
621228489|3/9/2014 4:20:26 AM


Loading:
M:\a1_maknuto>sqlldr scott/tiger@ora10 control=test12.ctl log=test12.log

SQL*Loader: Release 11.2.0.1.0 - Production on Sri O×u 12 10:20:02 2014

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

M:\a1_maknuto>


Testing:
SQL> select * From sqlldr_exp;

     TX_ID DT
---------- -------------------
 621228487 09.03.2014 18:20:26
 621228489 09.03.2014 04:20:26

SQL>


Why don't you post some more information (such as I did - copy/paste your execution sequence); log file, perhaps?
Re: date format in control file (sql loader) [message #609784 is a reply to message #609779] Wed, 12 March 2014 04:47 Go to previous messageGo to next message
vivek_veri
Messages: 8
Registered: September 2013
Location: INDIA
Junior Member
now

control file is


LOAD DATA
INFILE SQL_LDR_DT_EXP.txt
INTO TABLE SQLLDR_EXP
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
( TX_ID,
DT DATE "mm/dd/yyyy hh:mi:ss pm"
)

content of log file is :->



SQL*Loader: Release 9.2.0.1.0 - Production on Wed Mar 12 15:16:00 2014

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: D:\VIVEK\SQLLDR\DATE_SQL_LDR\DATE_PRACTISE\ram.ctl
Data File: SQL_LDR_DT_EXP.txt
Bad File: D:\VIVEK\SQLLDR\DATE_SQL_LDR\DATE_PRACTISE\SQL_LDR_DT_EXP.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table SQLLDR_EXP, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TX_ID FIRST * | CHARACTER
DT NEXT * | DATE mm/dd/yyyy hh:mi:ss pm

Record 1: Rejected - Error on table SQLLDR_EXP, column DT.
ORA-01858: a non-numeric character was found where a numeric was expected


Table SQLLDR_EXP:
1 Row successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Date cache:
Max Size: 1000
Entries : 1
Hits : 0
Misses : 0

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 1
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Mar 12 15:16:00 2014
Run ended on Wed Mar 12 15:16:00 2014

Elapsed time was: 00:00:00.14
CPU time was: 00:00:00.05


content of table :621228487|3/9/2014 6:20:26 PM
Re: date format in control file (sql loader) [message #609785 is a reply to message #609784] Wed, 12 March 2014 04:53 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you attach SQL_LDR_DT_EXP.TXT file to your next message? I used BEGINDATA option (i.e. copied values you previously posted into my control file; by the way, what happens when you do that as well?).
Re: date format in control file (sql loader) [message #609786 is a reply to message #609785] Wed, 12 March 2014 05:06 Go to previous messageGo to next message
vivek_veri
Messages: 8
Registered: September 2013
Location: INDIA
Junior Member
hi sir,

with this cotrol file provided by you (wich includes begindata) its working

LOAD DATA
INFILE *
INTO TABLE SQLLDR_EXP
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
( TX_ID,
DT DATE "mm/dd/yyyy hh:mi:ss pm"
)

begindata
621228487|3/9/2014 6:20:26 PM
621228489|3/9/2014 4:20:26 AM


Re: date format in control file (sql loader) [message #609787 is a reply to message #609786] Wed, 12 March 2014 05:07 Go to previous messageGo to next message
vivek_veri
Messages: 8
Registered: September 2013
Location: INDIA
Junior Member
sir

if text file contains 100000 records then we have to
write 100000 records in cotrol file ?
Re: date format in control file (sql loader) [message #609788 is a reply to message #609787] Wed, 12 March 2014 05:10 Go to previous message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course not; that's why you use INFILE which points to a file.

I don't know what is wrong with that file. Did you copy it from another system? If so, how? (should be in ASCII mode, I presume). Maybe it contains certain control characters which prevent data to be loaded correctly. Or, maybe not all rows in that file comply to the format you applied.
Previous Topic: impdp
Next Topic: Log miner
Goto Forum:
  


Current Time: Tue Sep 02 04:56:25 CDT 2014

Total time taken to generate the page: 0.16618 seconds