 Skip records on condition SQL Loader [message #553464] |
Fri, 04 May 2012 15:37  |
namitanamburi
Messages: 35
Registered: March 2009
|
Member |
|
|
These are few questions that I tried to find about sql loader, tried googling and ended up with Orafaq .
1) Is there a way that you can skip few records in the control file.
Assume the control file is loading a file with three records.
CREATE TABLE emp_tab
(
Emp_id NUMBER(15,0),
Name CHAR(25),
Age NUMBER(15,0)
);
The text file is like this name.txt
1;sam;19;
2;jai;22;
;pam;33;
LOAD DATA
INFILE 'C:\name.txt'
BADFILE 'C:\name.bad'
DISCARDFILE 'C:\name.dsc'
APPEND
INTO emp_tab
fields terminated by ";"
TRAILING NULLCOLS
( Emp_id ,
name,
age)
I want to skip the record 3 in the text file as it has no id, is there a way to do this.Can we skip a record based on a condition?
2) What needs to be included in the control file in order to get a return code?
3) Assume the return code = 0 for success and return code = 1 for failure, what will be the return code if 60 out 100 records are loaded and 40 are discarded and written to .bad file?
4) SQL loader does a auto commit, meaning the moment you run the control file, the records are inserted and commited, is there a way to avoid it ?
Thanks
Nammu
|
|
|
|
|
|
|
|
| Re: Skip records on condition SQL Loader [message #553471 is a reply to message #553464] |
Fri, 04 May 2012 17:28  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Quote:
1) Is there a way that you can skip few records in the control file.
Yes, you can use a WHEN clause after the INTO clause. To skip loading a record when a column is blank, use:
WHEN column_name != BLANKS
Quote:
2) What needs to be included in the control file in order to get a return code?
Nothing in the control file. If using Windows, then run SQL*Loader from a batch file. In your batch (.bat) file, immediately after your SQL*Loader command line, to display the exit code:
echo %errorlevel%
Quote:
3) Assume the return code = 0 for success and return code = 1 for failure, what will be the return code if 60 out 100 records are loaded and 40 are discarded and written to .bad file?
Discards result in an exit code of 2. Exit codes can be found here:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL1048
Quote:
4) SQL loader does a auto commit, meaning the moment you run the control file, the records are inserted and commited, is there a way to avoid it ?
Not that I know of.
Please see the example below that demonstrates the issues above.
-- name.txt:
1;sam;19
2;jai;22
;pam;33
-- test.ctl:
LOAD DATA
INFILE 'name.txt'
BADFILE 'name.bad'
DISCARDFILE 'name.dsc'
APPEND
INTO TABLE emp_tab
WHEN emp_id != BLANKS
FIELDS TERMINATED BY ";"
TRAILING NULLCOLS
(emp_id, name, age)
-- test.bat:
sqlldr scott/tiger control=test.ctl log=test.log
echo %errorlevel%
-- create table:
SCOTT@orcl_11gR2> CREATE TABLE emp_tab
2 (emp_id NUMBER (15, 0),
3 name CHAR (25),
4 age NUMBER (15, 0))
5 /
Table created.
-- load data and display exit code:
SCOTT@orcl_11gR2> host test.bat
C:\my_oracle_files>sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri May 4 15:10:24 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
C:\my_oracle_files>echo 2
2
-- results after load:
SCOTT@orcl_11gR2> SELECT * FROM emp_tab
2 /
EMP_ID NAME AGE
---------- ------------------------- ----------
1 sam 19
2 jai 22
2 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
