Re: Oracle SQL*Loader control file problem

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/