-
SQLLDR-providing values explicitly
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
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Order in the...
You have to order the columns (fields) in the controlfile the same order as in the source file and THEN supply the constants.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Originally Posted by PAVB
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)
-
Bad boy, BAD boy...SIT!
Try this:
Code:
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')
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|