Path: news.easynews.com!easynews!priapus.visi.com!news-out.visi.com!hermes.visi.com!uunet!ash.uu.net!news.boulder.noaa.gov!not-for-mail From: Thomas Gaines Newsgroups: comp.databases.oracle.tools Subject: Re: sqlldr problem with datetime fields Date: Thu, 08 Aug 2002 12:25:49 -0600 Organization: NOAA Boulder Lines: 72 Message-ID: <3D52B7AD.5F5A0549@noaa.gov> References: <9747aacb.0208051048.2e2f12a2@posting.google.com> <9747aacb.0208071346.24894a45@posting.google.com> NNTP-Posting-Host: tg2k.ngdc.noaa.gov Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: mwrns.noaa.gov 1028831100 29324 192.149.148.31 (8 Aug 2002 18:25:00 GMT) X-Complaints-To: usenet@news.boulder.noaa.gov NNTP-Posting-Date: Thu, 8 Aug 2002 18:25:00 +0000 (UTC) X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U) X-Accept-Language: en Xref: easynews comp.databases.oracle.tools:52908 X-Received-Date: Thu, 08 Aug 2002 11:27:51 MST (news.easynews.com) Good work, Anurag. We all should have caught this because the SQL*Loader datatypes are clearly and unambiguously identified in the Oracle8i and Oracle9i references describing SQL*Loader. As we all know by now, Oracle9i has changed quite a bit in terms of date/time storage and expression, and this is reflected in the SQL*Loader usage. Anyway, Jim just made a simple mistake of using Oracle9i syntax with Oracle8i software. Bye, Tom Anurag wrote: > You are right .. this baby fails in 8.1.7 > > I guess the error message threw us off. I was on the track that in some twisted version > of Oracle the lastmodified name is reserved word or something. > > The problem is the TIMESTAMP specification. Why? I don't know as yet .. and am in a hurry. > My guess is that TIMESTAMP is a valid ctl file specification for 9i .. but not for 8i. > > So here is what you should try in your ctl file: > load data > infile 'image_pp.txt' > into table image_pp > fields terminated by ';' optionally enclosed by '"' > (IMAGEID integer external, > ELEMENTNAME char, > OVERRIDEPATH char, > CREATED DATE "YYYY-MM-DD HH24:MI:SS", > LASTMODIFIED DATE "YYYY-MM-DD HH24:MI:SS", > LASTUSER integer external > ) > > I replaced TIMESTAMP with DATE ... things loaded fine then > > Anurag > > "Jim Flynn" wrote in message > news:9747aacb.0208071346.24894a45@posting.google.com... > > "Anurag" wrote in message news:... > > > I did test your scripts on a 9.2 version Oracle and it ran fine. > > > I can check on 8.16 and 8.1.7 versions also .. but frankly I don't expect > > > sqlldr to fail this load in any of these versions. > > > Please state your Oracle version and state what command line parameters > > > you are passing to sqlldr. > > > > > > Anurag > > > > Anurag, > > > > I am running 8.1.7. I am using this command line (about as simple as you can get!) > > > > sqlldr control=image_pp.ctl > > > > I then enter username and password. > > > > Thanks for the effort!! -- ===================================================== Thomas Gaines Professional Research Assistant / Senior DBA CIRES, NGDC/NOAA 303.497.3798 (office) 303.912.1241 (cell) thomas.gaines@noaa.gov =====================================================