OraFAQ Forum: Server Utilities » date format in control file (sql loader)

Home » RDBMS Server » Server Utilities » date format in control file (sql loader) (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

date format in control file (sql loader) [message #609765]

Wed, 12 March 2014 01:58

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

Report message to a moderator

Re: date format in control file (sql loader) [message #609766 is a reply to message #609765]

Wed, 12 March 2014 02:01

Littlefoot
Messages: 21823
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!

Report message to a moderator

Re: date format in control file (sql loader) [message #609769 is a reply to message #609766]

Wed, 12 March 2014 02:49

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.

Report message to a moderator

Re: date format in control file (sql loader) [message #609770 is a reply to message #609769]

Wed, 12 March 2014 03:05

Littlefoot
Messages: 21823
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

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

Report message to a moderator

Re: date format in control file (sql loader) [message #609779 is a reply to message #609777]

Wed, 12 March 2014 04:22

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: date format in control file (sql loader) [message #609784 is a reply to message #609779]

Wed, 12 March 2014 04:47

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

Report message to a moderator

Re: date format in control file (sql loader) [message #609785 is a reply to message #609784]

Wed, 12 March 2014 04:53

Littlefoot
Messages: 21823
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?).

Report message to a moderator

Re: date format in control file (sql loader) [message #609786 is a reply to message #609785]

Wed, 12 March 2014 05:06

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

Report message to a moderator

Re: date format in control file (sql loader) [message #609787 is a reply to message #609786]

Wed, 12 March 2014 05:07

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 ?

Report message to a moderator

Re: date format in control file (sql loader) [message #609788 is a reply to message #609787]

Wed, 12 March 2014 05:10

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	impdp
Next Topic:	Log miner

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 02:58:28 CST 2024