Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!news.maxwell.syr.edu!sn-xit-03!sn-xit-06!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: Sybrand Bakker <gooiditweg@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: sqlldr & date?
Date: Fri, 11 Oct 2002 00:50:55 +0200
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <ov0cqu8tpmd34odacfuece1e0tcm2fo0qn@4ax.com>
Reply-To: postbus@sybrandb.demon.nl
References: <C7np9.3047$Sk6.237033@news1.telusplanet.net>
X-Newsreader: Forte Agent 1.92/32.572
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@supernews.com
Lines: 103
Xref: newsfeed1.easynews.com comp.databases.oracle.server:163800
X-Received-Date: Thu, 10 Oct 2002 15:50:37 MST (news.easynews.com)

On Thu, 10 Oct 2002 22:25:38 GMT, Glen A Stromquist
<glen_stromquist@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@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