Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Loader error when loading calculate a field that is not in input file

863157May 19 2011 — edited Jun 6 2011
I 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)
)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2011
Added on May 19 2011
2 comments
735 views