Hi Tom,

I'm running into some issues with a SQL*Loader control file. In my data file, I have the following columns:

column1 = organization_name
column2 = attribute1_value
column3 = attribute1_value2
column4 = attribute1_value3
column5 = attribute2_value

You can see the actual data in the control file below.


The table I'm trying to load into is defined as follows:

CREATE TABLE interface_table
(
organization_name VARCHAR2(100),
attribute1_name VARCHAR2(50),
attribute1_value VARCHAR2(100),
attribute2_name VARCHAR2(50),
attribute2_value VARCHAR2(100)
);


Here's my control file. I'd like to concatenate attribute1_value, attribute1_value2, attribute1_value3 columns from the data file and load them into attribute1_value column in the table. I'd also like to default attribute1_name and attribute2_name columns in the table to certain default values (e.g. "PARTNER TYPE" and "ANNUAL REVENUE").


LOAD DATA
INFILE *
INTO TABLE interface_table
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
organization_name,
attribute1_value ":attribute1_value || +++ || :attribute1_value2|| +++ || :attribute1_value3",
attribute1_value2 FILLER,
attribute1_value3 FILLER,
attribute2_value,
attribute1_name "PARTNER TYPE",
attribute2_name "ANNUAL REVENUE"
)
BEGINDATA
Best Organization,Reseller,VAD,OEM,25000000




interface_table should look like this after the data is loaded:


organization_name attribute1_name attribute1_value attribute2_name attribute2_value
----------------- --------------- -------------------- --------------- ----------------
Best Organization PATNER TYPE Reseller+++VAD+++OEM ANNUAL REVENUE 25000000



However, when I ran the SQL*Loader, I got the following error:

SQL*Loader: Release 8.0.6.3.0 - Production on Wed May 12 10:43:14 2004

(c) Copyright 1999 Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 11.
Expecting "," or ")", found "FILLER".
attribute1_value2 FILLER,



Can you shed some light on this? Also is my logic correct?
thanks

I have SQL*Loader 9.2.0.1.0 installed on my system now.

Back to the originally question, somehow I am still not able to do what I want to do. I'd like to skip some columns in the data file (using FILLER). For the skipped columns, I want to concatenat them and put them into one single column. Here's the control file:

LOAD DATA
INFILE *
INTO TABLE interface_table
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
organization_name,
attribute1_value ":attribute1_value || :attribute1_value2 || :attribute1_value3",
attribute1_value2 FILLER,
attribute1_value3 FILLER,
attribute2_value,
attribute1_name "'PARTNER TYPE'",
attribute2_name "'ANNUAL REVENUE'"
)
BEGINDATA
Best Organization,Reseller,VAD,OEM,25000000



I got the following error from the above control file:

SQL*Loader: Release 9.2.0.1.0 - Production on Thu May 13 09:44:09 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL*Loader-291: Invalid bind variable ATTRIBUTE1_VALUE2 in SQL string for column
ATTRIBUTE1_VALUE.


It seems that the columns that are marked as FILLER cannot be used anywhere else. Is there a solution to this problem?

Thanks

The solution provided in the following link simulates FILLER in a pre-8i release of SQL*Loader. Is it also possible to simulate BOUNDFILLER?

</code> http://asktom.oracle.com/~tkyte/SkipCols/index.html <code>

Tom,
I have seen some people put
INFILE '-' (a dash in sigle quote)
in their sqlldr control file. Then they can do something like this:
cat mydatafile.dat | sqlldr userid=u/p@tns control=my.ctl

and it works well. But I couldnt find this being documented in the Oracle Utility Guide, could you explain how this works? Or is it an undocumented but wellknown trick? It seems '-' is a magic switch that allows sqlldr to accept input data from stdio redirection.
Thanks!

Help - How can I load this using SQL*LOADER ?

how do I build in dynamism into the control files ?

The name of the incoming file is not going to tell me if the file is one of these 5 formats.
Notice that only the last 3 or 4 fields are different... but there is no clue for me, to say which format the SQL*LOADER script is to expect.


ACCNT_ID, PHONE, PROGRAM, LIST_DATE, START_DATE, MESSAGE_CELL, MIN4, ALT_NUMBER

ACCNT_ID, PHONE, PROGRAM, LIST_DATE, START_DATE, MESSAGE_CELL, TRANS_DATE, MIN4, ALT_NUMBER



ACCNT_ID, PHONE, PROGRAM, LIST_DATE, START_DATE, MESSAGE_CELL

ACCNT_ID, PHONE, PROGRAM, LIST_DATE, START_DATE, MESSAGE_CELL., TRANS_DATE, MIN4

ACCNT_ID, PHONE, PROGRAM, LIST_DATE, START_DATE, MESSAGE_CELL, MIN4

If you are on Unix, why not write several .ctl files for each scenario? Then you could, if comma delimited:

head -n 1 <data file> | awk -F, '{print $NF}'

That will print the last field, and you can check for syntax of the field. After you determine that, you can pick the appropriate .ctl file to use.

To check other fields you can:

Next to last field:
head -n 1 <data file> | awk -F, '{print $(NF-1)}'

Third from last field:
head -n 1 <data file> | awk -F, '{print $(NF-2)}'

and so on, so you can check any of those last fields.

Better yet, do they include column headings? We have a vendor ship with column headings so we can see what columns are in the file, then we strip the column headings before loading.

Looks like link:
http://asktom.oracle.com/~tkyte/SkipCols/index.html
is no more available. I am looking for version 8.0.6 sql loader where we can skip columns. (Similiar to Filler as we have in 8i)

Thanks in advance
~ GK ~