Oracle SQL*Loader control file problem

[bple...@my-deja.com]

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....@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/

[Mike Krolewski]

In article <91e1gu$jc7$1...@nnrp1.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
mkrol...@rii.com
Usual disclaimers

--
Michael Krolewski
Rosetta Inpharmatics
mkrol...@rii.com
Usual disclaimers

[bple...@my-deja.com]

That did it. Thank you Mike.

Brian Pleshek
Maxim Group, Cincinnati, Ohio

In article <91e3sp$ldo$1...@nnrp1.deja.com>,

[ferrosio...@gmail.com]

Can Anyone help me in my control file , I am getting an error :

SQL*Loader-350: Syntax error at line 18. Expecting valid column specification, "," or ")", found "("

My CTL file is :


LOAD DATA
INFILE 'DSS_EMPLOYEE.DAT'
INTO TABLE EMPLOYEE
(
INPUT_DATE (1:2),
DESCRIPTION (3,4)
)

Thanks
Himanshu

[joel garry]

On Thursday, April 3, 2014 2:15:12 AM UTC-7, ferrosio...@gmail.com wrote:

>
> SQL*Loader-350: Syntax error at line 18. Expecting valid column specification, "," or ")", found "("
>
> My CTL file is :
>
> LOAD DATA
> INFILE 'DSS_EMPLOYEE.DAT'
> INTO TABLE EMPLOYEE
> (
> INPUT_DATE (1:2),
> DESCRIPTION (3,4)

What do you mean with the comma in 3,4? I don't see 18 lines, which line is 18?

Please don't respond to 14 year old threads. Start a new thread, and include all the information we can't see, like the exact versions.

jg
--
@home.com is bogus.
http://www.v3.co.uk/v3-uk/news/2337961/-archaic-microsoft-and-oracle-systems-ill-suited-for-digital-first-businesses-says-retail-boss