| how to stop sql loader if any row is bad [message #150203] |
Tue, 06 December 2005 13:41  |
samifarooq
Messages: 15
Registered: November 2005
|
Junior Member |
|
|
Hi
I am loading data into table using sqlldr(SQL Loader).
how can i prevent data to append/insert into table if any row in the data file/csv file is bad or empty, or mismatch datatype etc.
when i try with bad data file it is still enter good data into table, but in my case i dont want even the good row to be inserted in the table if any row is bad.if any row is bad it should exit without inserting data.
pls let me know.
Thanks
sami
|
|
|
|
| Re: how to stop sql loader if any row is bad [message #150209 is a reply to message #150203] |
Tue, 06 December 2005 14:08  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Either use direct option as shown
or
load to a staging table.
If you are not happy with the loaded data, dump the data in staging table.
If you are happy with the load then, insert data from staging table into your target table.
Please see session.
--
-- Our table
--
oracle@mutation#desc mutation scott.dept
Table:scott.dept
Name Null? Type
------------------------- -------- ------------------------
1 DEPTNO NUMBER(2)
2 DNAME VARCHAR2(14)
3 LOC VARCHAR2(13)
--
-- Our CTL
--
oracle@mutation#cat dept.ctl
Load Data
infile 'dept.data'
append INTO TABLE dept
FIELDS TERMINATED BY "," trailing nullcols
(
deptno,
dname,
loc
)
--
-- The datafile i used.
-- Third record is faulty. It will not be loaded
oracle@mutation#cat dept.data
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
301,SALES,CHICAGO
40,OPERATIONS,BOSTON
--
-- Default case. This what usually happens
-- Only Bad records are not loaded.
--
oracle@mutation#sqlldr userid=scott/tiger control=dept.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Tue Dec 6 14:50:36 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
oracle@mutation#query mutation scott.dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
oracle@mutation#trunc mutation scott.dept
Table truncated.
--
-- using DIRECT option.
-- No records are loaded ,even if one of them are bad.
--
oracle@mutation#sqlldr userid=scott/tiger control=dept.ctl direct=y errors=0
SQL*Loader: Release 9.2.0.4.0 - Production on Tue Dec 6 14:58:59 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Load completed - logical record count 4.
oracle@mutation#query mutation scott.dept
no rows selected
--
-- Without DIRECT option and using only ERRORS option.
-- Now sql*loader allows NO errors.
-- If sqlldr comes through any bad record, process stops.
-- But whatever inserted before, will be committed.
--
oracle@mutation#sqlldr userid=scott/tiger control=dept.ctl errors=0
SQL*Loader: Release 9.2.0.4.0 - Production on Tue Dec 6 14:59:19 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
oracle@mutation#query mutation scott.dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
[Updated on: Tue, 06 December 2005 14:28] Report message to a moderator |
|
|
|
| Re: how to stop sql loader if any row is bad [message #150212 is a reply to message #150209] |
Tue, 06 December 2005 14:42 |
samifarooq
Messages: 15
Registered: November 2005
|
Junior Member |
|
|
Thank you Mahesh,
when i tried using the option Direct, i am getting error, but if try without direct option it is inserting good data.
pls see the following.
[oracle@dl1 java]$ sqlldr control=test.cntl direct=y errors=0
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Dec 6 15:35:38 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-951: Error calling once/load initialization
ORA-00942: table or view does not exist
Thanks
sami
|
|
|
|
|
|
| Re: how to stop sql loader if any row is bad [message #150408 is a reply to message #150203] |
Wed, 07 December 2005 15:23 |
samifarooq
Messages: 15
Registered: November 2005
|
Junior Member |
|
|
Hi Mahesh,
Can i give NOT NULL in the control file, because we are creating table which accepts the null values, but i dont have to insert data into table if the file column has any null values,
pls see my control file
test.cntl:
load data
infile '/home/oracle/java/test.csv'
append into table pub.table_test
fields terminated by ","
optionally enclosed by '"'
(custno not null,
loginid ,
permission ,
delivery )
Thanks in advance
Sami
|
|
|
|
| Re: how to stop sql loader if any row is bad [message #150409 is a reply to message #150408] |
Wed, 07 December 2005 15:38 |
samifarooq
Messages: 15
Registered: November 2005
|
Junior Member |
|
|
I think i have to use WHEN clause in the control file
eg: test.cntl
load data
infile '/home/oracle/test/java/test.csv'
append into table pub.sami_test
WHEN custno != ' ' <========== here we have to use WHEN
fields terminated by ","
optionally enclosed by '"'
(custno ,
loginid ,
permission ,
delivery )
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
