Home » RDBMS Server » Server Utilities » Skip records on condition SQL Loader (11g database)
icon3.gif  Skip records on condition SQL Loader [message #553464] Fri, 04 May 2012 15:37 Go to next message
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 #553467 is a reply to message #553464] Fri, 04 May 2012 15:54 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
What about when condition of
WHEN 1-1<>';'


[fixed typo]

[Updated on: Fri, 04 May 2012 15:55]

Report message to a moderator

Re: Skip records on condition SQL Loader [message #553469 is a reply to message #553467] Fri, 04 May 2012 17:15 Go to previous messageGo to next message
Littlefoot
Messages: 21528
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
1) Or
WHEN emp_id <> ''


2) Return codes

3) EX_WARN

4) Avoid commit? As far as I know, you can't do that (i.e. at least one commit - at the end of loading session - will be done).

Re: Skip records on condition SQL Loader [message #553471 is a reply to message #553464] Fri, 04 May 2012 17:28 Go to previous message
Barbara Boehmer
Messages: 8919
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.

Previous Topic: How to get the proper value in external table
Next Topic: export import
Goto Forum:
  


Current Time: Fri Jan 17 18:28:23 CST 2020