Re: Oracle SQL*Loader control file problem

From: <bpleshek1_at_my-deja.com>
Date: Mon, 18 Dec 2000 19:11:55 GMT
Message-ID: <91lnhl$4t9$1@nnrp1.deja.com>

That did it. Thank you Mike.

Brian Pleshek
Maxim Group, Cincinnati, Ohio

In article <91e3sp$ldo$1_at_nnrp1.deja.com>,   Mike Krolewski <mikkrolewski_at_my-deja.com> wrote:
> In article <91e1gu$jc7$1_at_nnrp1.deja.com>,
> bpleshek1_at_my-deja.com wrote:
> > I'm trying to load an Oracle table from a text file. I'm getting
> > errors in my control file. I'm trying to concatenate some fields in
> > the input text file to form one field in Oracle. Below is my
 control
> > file and the error message I get. I appreciate any help.
> >
> > Thanks,
> >
> > Brian Pleshek
> > brian.pleshek_at_lmberry.com
> >
> > ***Control file:
> >
> > LOAD DATA
> > INTO TABLE LISTLOAD
> > (
> > PHONE POSITION(14:23) CHAR,
> > ACCOUNT_NO POSITION(14:23) || POSITION
 (11:13)
> > CHAR,
> > STATE POSITION(40:41) CHAR,
> > ADDRESS_LINE_1 POSITION(43:82) CHAR,
> > ADDRESS_LINE_2 POSITION(83:108) CHAR,
> > ADDRESS_LINE_3 POSITION(123:162) CHAR,
> > ADDRESS_LINE_4 POSITION(163:202) CHAR,
> > ADDRESS_LINE_5 POSITION(203:242) CHAR,
> > LATA_CODE POSITION(243:245) CHAR,
> > VERT_SVCS_SPEND POSITION(254:264) CHAR
> > )
> >
> > ***ERROR MESSAGE:
> >
> > SQL*Loader-350: Syntax error at line 5.
> > Illegal combination of non-alphanumeric characters
> > ACCOUNT_NO POSITION(14:23) || POSITION
 (11:13)
> >
> > ***Another Try:
> >
> > ACCOUNT_NO CONCATENATE(POSITION
 (11:13),:PHONE)
> > CHAR,
> >
> > ***ERROR MESSAGE:
> >
> > SQL*Loader-350: Syntax error at line 5.
> > Expecting valid column specification, "," or ")", found keyword
> > concatenate.
> > ACCOUNT_NO CONCATENATE(POSITION
 (11:13),:PHONE)
> > ^
> >
> > Again, I appreciate any help.
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >

>

> try :

>

> ACCOUNT_NO POSITION(11:23) char " substr(:account_no, 4) || substr
> (:account_no, 1,3) ",

>

> It is outlined in the SQLLoader manual chapter 6
> Applying SQL Operators to Fields

>

> A wide variety of SQL operators can be applied to field data with the
> SQL string. This string can contain any combination of SQL expressions
> that are recognized by Oracle as valid for the VALUES clause of an
> INSERT statement. In general, any SQL function that returns a single
> value can be used. See the information about expressions in the
> Oracle8i SQL Reference.

>
> The column name and the name of the column in the SQL string must
 match
> exactly, including the quotation marks, as in this example of
 pecifying
> the control file:

>

> LOAD DATA
> INFILE *
> APPEND INTO TABLE XXX
> (
> "LAST" position(1:7) char "UPPER(:\"LAST\)",
> FIRST position(8:15) char "UPPER(:FIRST)"
> )
> BEGINDATA
> Phil Locke
> Jason Durbin

>

> The SQL string must be enclosed in double quotation marks. In the
> preceding example, LAST must be in quotation marks because it is a
> SQL*Loader keyword. FIRST is not a SQL*Loader keyword and therefore
> does not require quotation

>

> etc....

>

> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers

>

> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers

>

> Sent via Deja.com
> http://www.deja.com/

>

Sent via Deja.com
http://www.deja.com/ Received on Mon Dec 18 2000 - 13:11:55 CST