Sql Loader: loading constant data

[DoraC]

Hi,

We are using SqlLoader to load files into our Oracle database, however at load time we need to specify certain constant values to be loaded as well. So, instead of coming from the Data files used to load the rest of the data, this constant data will somehow have to be specified in the control file. I've tried something like this:

Load data
into table Table
append
fields terminated by "|"
(
constant_column constant "Constant Value",
column1         char     ":column1",
column2         char     ":column2",
...
columnN         char     ":columnN"
)

there is of course no problem if the "constant" line is omitted, however - upon its inclusion - all rows reject...

Is this possible? The interspersing of datafile data and constant, control-file specified data???

Please help - any help appreciated!!!!!!!

dora

 

[AOLEURO]

Hi

I would only load columns with data from loader.

And then I would make a TRIGGER to change and set columns, like this:


create or replace trigger MyLoader
before insert on table MyTable
for each row
begin
:NEW.ColumnXX := '1';
:NEW.ColumnXY := sysdate;
:NEW.ColumnNm := InitCapNEW.ColumnNm);
end;

After loading data - then drop trigger.

You can control much more in the trigger – control group data relations and other stuff not described in constraints.

I also use raise_application_error to give back to the loader (log file) a reason why a row was rejected.

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[karluk]

Your syntax appears to be correct. I can load a mixture of constants and data using a similar control file. Are you getting an error message?

 

[topher0303]

Try single quotes and see if that works:

constant_column constant 'Constant Value',


If not, please post the error message.

Chris.

 

[THoey]

Here is a control file that I use that uses constants:

options(errors=10)
load data
infile 'c:\okc_xref.dat'
into table AGENT_XREF APPEND
(
AGENT_ID position(01:04) CHAR,
AGENT_NAME position(05:24) CHAR "INITCAPAGENT_NAME)",
DIALER_ID position(25:31) CHAR "LOWERDIALER_ID)",
SUPERVISOR position(32:50) CHAR "INITCAPSUPERVISOR)",
WORK_TYPE position(60:62) CHAR,
CENTER CONSTANT "OKC"
)

Your sample looks right. I am with Karluk, I would look in the log file and see why the records rejected. Wrong data type, constraint? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...