Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sqlldr & date?

Re: sqlldr & date?

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Fri, 11 Oct 2002 00:50:55 +0200
Message-ID: <ov0cqu8tpmd34odacfuece1e0tcm2fo0qn@4ax.com>


On Thu, 10 Oct 2002 22:25:38 GMT, Glen A Stromquist <glen_stromquist_at_nospam.yahoo.com> wrote:

>control file LOAD.CTL:
>
>load data
>infile file.csv
>insert into table schema.table
>fields terminated by ","
>(DATEOF "DD-MON-YY",
>NUM1,
>NUM2,
>NUM3,
>NUM4,
>NUM5,
>NUM6,
>NUM7)
>
>.CSV file:
>
>DATEOF,NUM1,NUM2,NUM3,NUM4,NUM5,NUM6,NUM7
>12-Jun-82,12,22,23,45,55,46,45
>13-Jun-82,23,32,46,12,45,66,65
>
>
>When I run SQLLDR NAME/PASS_at_DB CONTROL=LOAD.CTL it shows:
>
>Commit point reached - logical record count 31
>Commit point reached - logical record count 62
>
>The resulting log file loks like this:
>
>SQL*Loader: Release 8.1.7.3.0 - Production on Thu Oct 10 15:55:37 2002
>
>(c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>Control File: load.ctl
>Data File: file.csv
> Bad File: file.bad
> Discard File: none specified
>
> (Allow all discards)
>
>Number to load: ALL
>Number to skip: 0
>Errors allowed: 50
>Bind array: 64 rows, maximum of 65536 bytes
>Continuation: none specified
>Path used: Conventional
>
>Table SCHEMA.TABLE, loaded from every logical record.
>Insert option in effect for this table: INSERT
>
> Column Name Position Len Term Encl Datatype
>------------------------------ ---------- ----- ---- ----
>---------------------
>DATEOF FIRST * , CHARACTER
>
> SQL string for column : "dd-mon-yy"
>NUM1 NEXT * , CHARACTER
>
>NUM2 NEXT * , CHARACTER
>
>NUM3 NEXT * , CHARACTER
>
>NUM4 NEXT * , CHARACTER
>
>NUM5 NEXT * , CHARACTER
>
>NUM6 NEXT * , CHARACTER
>
>BNUM NEXT * , CHARACTER
>
>
>Record 1: Rejected - Error on table SCHEMA.TABLE, column DATEOF.
>ORA-00984: column not allowed here
>
>Record 2: Rejected - Error on table SCHEMA.TABLE, column DATEOF.
>ORA-00984: column not allowed here
>
>
>What am I doing wrong here?
>
>I have and almost identical control file for another sqlldr job that works
>fine, but there is no date field in it. The ORA-00984 message does not help
>me much at this point, obviously I'm missing something simple here, but I
>can't even find a similar problem in google, so here I am :-)
>
>TIA
Change
DATEOF "DD-MON-YY" in DATEOF DATE
NLS_DATE_FORMAT in the registry or environment will deal with the conversion.
You'd better replace the YY with RR though.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Oct 10 2002 - 17:50:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US