Re: SQL LOADER POSITIONS (X, X)

From: Val <vegas_girlie_at_hotmail.com>
Date: 27 Jun 2003 10:47:45 -0700
Message-ID: <7d04f924.0306270947.39edfcc@posting.google.com>

"Guido Konsolke" <Guido.Konsolke_at_triaton.com> wrote in message news:<1056705930.246844_at_news.thyssen.com>...
> "Val" <vegas_girlie_at_hotmail.com> schrieb im Newsbeitrag
> news:7d04f924.0306270025.62bfac58_at_posting.google.com...
> > hi -
> >
> > i am trying to load a flat file where the fields are defined as either
> > char (40), char (50), and date. The oracle database is also defined
> > as the flat files. When I load it I get an error of "invalid number"
> > as well as "data in column is exceeding max limit" although i checked
> > the input file and it does not exceed. The values are below what the
> > specified length is. Would I need to use the positions syntax and how
> > would that work. see below for an example:
> >
> > a - date
> > b - char (40)
> > c - char (50)
> >
> > would i also need to terminate by whitespace as well? I am confused as
> > to when and how to use the POSITIONS syntax.
>
> Hi Val,
>
> if the columns in your file are delimited by a special character, use the
> TERMINATED BY clause, else use the POSITION(x:y) syntax.
>
> What did you try to load the file? Please post your control file. If sqlldr
> complaints about exceeding well, use POSITON.
>
> BTW: in general it is no good idea to have columns CHAR(xx). Make
> them VARCHAR2 if possible.
>
> hth,
> Guido

The below is my control file. Unfortunately the dat file is a fixed format with extra padding in the fields if there are spaces. LOAD DATA
INFILE 'test.dat'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "test"
INSERT FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(a position (1:11),
b position (13:56),
c position (58:77),
d position (79:100),
e position (102:123),
f position (125:127),
g position (129:134),
h position (136:157),
i position (159:169) DATE 'YYYY/MM/DD',
j position (171:180) DATE 'YYYY/MM/DD',

k position (182:191),
l position (193:205))

this is the error message that I get:

SQL*Loader: Release 9.2.0.3.0 - Production on Thu Jun 26 21:57:48 2003

SQL*Loader-350: Syntax error at line 20. Expecting "," or ")", found keyword position. i DATE position (159:169) DATE 'YYYY/MM/DD', Received on Fri Jun 27 2003 - 12:47:45 CDT

Re: SQL LOADER POSITIONS (X, X) - HOW /WHENTO USE