hi everybody
well im trying to insert records via SQL Loader in multiple tables but i can only insert in the first table see the code below:
LOAD DATA
INFILE 'fixed_assets.dat'
BADFILE 'fixed_assets.bad'
DISCARDFILE 'fixed_assets.dsc'
REPLACE INTO TABLE XX_FA_MASS_ADDITIONS_2
WHEN DESCRIPTION='foundation'
(
MASS_ADDITION_ID INTEGER EXTERNAL TERMINATED BY ";",
ASSET_NUMBER CHAR(15) TERMINATED BY ";",
DESCRIPTION CHAR(80) TERMINATED BY ";",
ASSET_CATEGORY_ID INTEGER EXTERNAL TERMINATED BY ";",
BOOK_TYPE_CODE CHAR(15) TERMINATED BY ";"
)
INTO TABLE XX_FA_MASS_ADDITIONS
WHEN DESCRIPTION='foundations'
(
MASS_ADDITION_ID INTEGER EXTERNAL TERMINATED BY ";",
ASSET_NUMBER CHAR(15) TERMINATED BY ";",
DESCRIPTION CHAR(80) TERMINATED BY ";",
ASSET_CATEGORY_ID INTEGER EXTERNAL TERMINATED BY ";",
BOOK_TYPE_CODE CHAR(15) TERMINATED BY ";"
)
wen im viewing the log file , its saying 'Record 2: Discarded - failed all WHEN clauses.' i checked the WHEN clause but could not find anything wrong
can anybody tell me wats wrong in that?
data not loaded through SQL Loader
-
- Posts: 130
- Joined: Sun Mar 11, 2007 1:47 am
- Location: India
Hi,
Try this...
1. LOAD DATA
2. INFILE file_name / *
3. INSERT/APPEND/REPLACE/TRUNCATE
4. CONTINUE IF THIS (1) = character
5. INTO TABLE EMP
6. ( empid SEQUENCE (MAX,1),
7. first_name Position (01:30) CHAR,
8. last_name Position (31:60) CHAR,
9. hire_date Position ( 61:72) DATE,
10. emp_no Position ( 73:85) CHAR
11. INTO TABLE DEPT
12. WHEN DEPTNO!= '^Z'
13. (Emp_no Position (73:85) CHAR,
14. Dept_no Position (86:95) INTEGER_EXTERNAL)
15. BEGIN DATA - - if you are using * on line 2.
If you have any queries please let me know..
Try this...
1. LOAD DATA
2. INFILE file_name / *
3. INSERT/APPEND/REPLACE/TRUNCATE
4. CONTINUE IF THIS (1) = character
5. INTO TABLE EMP
6. ( empid SEQUENCE (MAX,1),
7. first_name Position (01:30) CHAR,
8. last_name Position (31:60) CHAR,
9. hire_date Position ( 61:72) DATE,
10. emp_no Position ( 73:85) CHAR
11. INTO TABLE DEPT
12. WHEN DEPTNO!= '^Z'
13. (Emp_no Position (73:85) CHAR,
14. Dept_no Position (86:95) INTEGER_EXTERNAL)
15. BEGIN DATA - - if you are using * on line 2.
If you have any queries please let me know..
-
- Posts: 40
- Joined: Tue Jun 12, 2007 11:25 am
- Location: India
- Contact:
If u r insert or repalce
what every data put into control file
after LOAD DATA
u can write : INFILE *
and postion muat me noted
1st table like : 001,babu,30(no number(4),name varchar2(10),deptno number(4)) then u can write like in the way
0001babu 0030
in the 2nt table u must write WHEN condition if u have 2nd table then write like in this way
001babu 00300001babu 0030
what every data put into control file
after LOAD DATA
u can write : INFILE *
and postion muat me noted
1st table like : 001,babu,30(no number(4),name varchar2(10),deptno number(4)) then u can write like in the way
0001babu 0030
in the 2nt table u must write WHEN condition if u have 2nd table then write like in this way
001babu 00300001babu 0030
-
- Posts: 4
- Joined: Sat Mar 10, 2007 7:58 pm
- Location: USA
-
- Posts: 130
- Joined: Sun Mar 11, 2007 1:47 am
- Location: India
Hi,
First we can trnasfer the control file to the respective folder called "BIN"
Registration steps for Concurrent program:
--------------------------------------------------
1)create the Executable.
Here we can set the Execution method as "SQL* LOADER"
2)Create the concurrent program and attch executable to the concurrent program.
3)create the request group and concurrent program attcah to the request group.
4)create the responibility and request group attch to the responsibility.
5)create the user and responsibility attach to the user.
First we can trnasfer the control file to the respective folder called "BIN"
Registration steps for Concurrent program:
--------------------------------------------------
1)create the Executable.
Here we can set the Execution method as "SQL* LOADER"
2)Create the concurrent program and attch executable to the concurrent program.
3)create the request group and concurrent program attcah to the request group.
4)create the responibility and request group attch to the responsibility.
5)create the user and responsibility attach to the user.
Who is online
Users browsing this forum: No registered users and 2 guests