SQLLDR-providing values explicitly

[hserdur]

Hi,

I am trying to upload data to a table using sqlldr. It works fine if i specify all the column values in .txt file, but if i specify explicitly in the control file its not working

LOAD DATA
INFILE 'example.txt'
DISCARDFILE 'example.dsc'
APPEND
INTO TABLE test_tab
FIELDS TERMINATED BY ','
(empid ,
ename "acb",
sal constant'1',
job "bbc")

my example.txt file goes like this
1,
2,
3,
4
I am getting the following err
Record 1: Rejected - Error on table TEST_TAB, column JOB.
ORA-00984: column not allowed here

Record 2: Rejected - Error on table TEST_TAB, column JOB.
ORA-00984: column not allowed here

Record 3: Rejected - Error on table TEST_TAB, column JOB.
ORA-00984: column not allowed here

Record 4: Rejected - Error on table TEST_TAB, column JOB.
ORA-00984: column not allowed here

I am expecting the following result
empid ename sal job
1 acb 1 bbc
2 acb 1 bbc
3 acb 1 bbc
4 acb 1 bbc

Plz suggest

[PAVB]

desc TEST_TAB please.

[LKBrwn_DBA]

You have to order the columns (fields) in the controlfile the same order as in the source file and THEN supply the constants.

[hserdur]

desc TEST_TAB please.

SQL> desc test_tab
Name Null? Type
----------------------------------------- -------- -----------------
EMPID NOT NULL NUMBER(5)
ENAME NOT NULL VARCHAR2(10)
SAL NOT NULL NUMBER(10)
JOB NOT NULL VARCHAR2(10)

[LKBrwn_DBA]

Try this:

LOAD DATA
INFILE 'example.txt'
DISCARDFILE 'example.dsc'
APPEND INTO TABLE test_tab
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
( empid
, ename CONSTANT 'abc'
, sal   CONSTANT '1'
, job   CONSTANT 'bbc')