Home » RDBMS Server » Server Utilities » Sql Loader Error (Oracle,11G,Unix)
Sql Loader Error [message #578358] Wed, 27 February 2013 04:32 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi

I am trying to load from a file using Sql Loader Script. File is being created by unix commands which has single column with dates. All data is going to .bad file, could some advise how to load this into table. Below is the Script and file date i am using.

Thanks

load data
        insert into table DATE_LOAD_TBL
        TRAILING NULLCOLS
        (
        CURRENT_DATE  "TO_DATE(:CURRENT_DATE)"
        )


Data File:
29-Jan-2013
30-Jan-2013
31-Jan-2013

Re: Sql Loader Error [message #578362 is a reply to message #578358] Wed, 27 February 2013 04:49 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I am not sure about it, but you may have to include Date Format.

regards,
Delna
Re: Sql Loader Error [message #578369 is a reply to message #578362] Wed, 27 February 2013 05:44 Go to previous messageGo to next message
Littlefoot
Messages: 19294
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> $type test7.ctl
load data
INFILE *
into table date_load_tbl
replace
TRAILING NULLCOLS
  (
   current_date position(1:12) "to_date(:current_date, 'dd-mon-yyyy', 'nls_date_language=english')"
  )

begindata
29-Jan-2013
30-Jan-2013
31-Jan-2013
SQL> $sqlldr scott/tiger@ora10 control=test7.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Sri Vel 27 12:43:28 2013

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

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from date_load_tbl;

CURRENT_DA
----------
29.01.2013
30.01.2013
31.01.2013

SQL>
Re: Sql Loader Error [message #578376 is a reply to message #578369] Wed, 27 February 2013 06:05 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Thanks Littlefoot.

It worked perfectly.

Thanks again
Re: Sql Loader Error [message #578399 is a reply to message #578376] Wed, 27 February 2013 08:57 Go to previous message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CURRENT_DATE is the name of a standard function; do not use it as your column name:
SQL> select current_date from dual;

CURRENT_DATE
-------------------
27/02/2013 15:57:09

Regards
Michek
Previous Topic: Import data type date from excel to oracle
Next Topic: Wrap utility
Goto Forum:
  


Current Time: Tue Jul 22 21:38:57 CDT 2014

Total time taken to generate the page: 0.09493 seconds