SQL Loader error when loading calculate a field that is not in input file
863157May 19 2011 — edited Jun 6 2011I have a strange issue with SQL Loader when I call a function to calculate a value for a field that doesn't exist in the source data file.
I use Oracl10g database and SQL Loader 10.2.0.1.0
In the example below, I read field1 and field2 from the data file and need to store them in the table in the respective fields.However field3 is not present
in the input data file, but is calculated by invoking a function and passing field1 and field2. However when I do this, I get an error on the next field. The
error says
Record 1: Rejected - Error on table TEST_TABLE, column FIELD4.
Initial enclosure character not found
Any help would be appreciated. All the examples I see skip field2. But in my case I want to store field2 as well.
Following are the control file and sample data.
Control File
===========
LOAD DATA
INFILE 'input_data.dat'
BADFILE 'bad_file.bad'
APPEND INTO TABLE TEST_TABLE
(
FIELD1 DECIMAL EXTERNAL TERMINATED BY "," ENCLOSED BY '"' ,
FIELD2 CHAR TERMINATED BY "," ENCLOSED BY '"' ,
FIELD3 "findfield3(:FIELD1, :FIELD2)",
FIELD4 CHAR ENCLOSED BY '"'
)
input_data.dat file
==================
"10","FIELD2","FIELD4"
SQL Script to create table
===========================
CREATE TABLE "TEST_TABLE"
( "FIELD1" NUMBER,
"FIELD2" VARCHAR2(10 BYTE),
"FIELD3" VARCHAR2(15 BYTE),
"FIELD4" VARCHAR2(10 BYTE)
)