Home » RDBMS Server » Server Utilities » sql loader not creating bad file (oracle 10)
sql loader not creating bad file [message #530893] Fri, 11 November 2011 04:54 Go to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
hi guys,

having a weird problem. my sql loader is returning a code 2 but not creating a bad file for the rejected records. also logs look good.

any body seen this before?

any help appreciated.
Re: sql loader not creating bad file [message #530895 is a reply to message #530893] Fri, 11 November 2011 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It might help if you could post SQLLDR statement you ran, as well as (significant) parts of the log file.
Re: sql loader not creating bad file [message #530904 is a reply to message #530895] Fri, 11 November 2011 05:26 Go to previous messageGo to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
thats the weird thing the log file says this:
Sqlldr return code 2 Batch step 4 run finished sucessfully with warnings, some rows were rejected by sqlldr

however it appears that the all the records made it in.
Re: sql loader not creating bad file [message #530906 is a reply to message #530904] Fri, 11 November 2011 05:50 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Exit codes:
Code      Unix  Windows  Reason
-------   ----  -------  -------------------------------------------------------------
EX_SUCC : 0     0        all rows loaded successfully
EX_WARN : 2     2        all/some rows rejected or discarded; discontinued load
EX_FAIL : 1     3        command line/syntax errors, Oracle errors fatal to SQL*Loader
EX-FTL  : 3     4        OS related errors (like file open/close etc.)

So: if there are no rejected nor discarded rows, was your loading discontinued?
Re: sql loader not creating bad file [message #530909 is a reply to message #530906] Fri, 11 November 2011 05:52 Go to previous messageGo to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
we got the ex_warn: message but the load continued.
Re: sql loader not creating bad file [message #530912 is a reply to message #530909] Fri, 11 November 2011 05:54 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that you shouldn't worry, then.
Re: sql loader not creating bad file [message #530913 is a reply to message #530912] Fri, 11 November 2011 05:56 Go to previous messageGo to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
my client will worry, and will ask question as to the validity of our tests.
i would really like to get to the bottom of this.
Re: sql loader not creating bad file [message #530914 is a reply to message #530913] Fri, 11 November 2011 05:59 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
- no rejected rows
- no discarded rows
- load continued
- all rows loaded

Where do you exactly see a reason to be worried?
Re: sql loader not creating bad file [message #530915 is a reply to message #530914] Fri, 11 November 2011 06:00 Go to previous messageGo to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
the batch log outputs: Sqlldr return code 2 Batch step 4 run finished sucessfully with warnings, some rows were rejected by sqlldr
Re: sql loader not creating bad file [message #530916 is a reply to message #530915] Fri, 11 November 2011 06:02 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which contradicts what you said previously: "however it appears that the all the records made it in."

So what's the truth?
Re: sql loader not creating bad file [message #530918 is a reply to message #530916] Fri, 11 November 2011 06:05 Go to previous messageGo to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
both. that is the problem.

the batch log outputs: Sqlldr return code 2 Batch step 4 run finished sucessfully with warnings, some rows were rejected by sqlldr

however when i query the tables i can confirm that all records made it in.
i would expect a return code of 0 at this point.
Re: sql loader not creating bad file [message #530921 is a reply to message #530918] Fri, 11 November 2011 06:09 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you post SQLLDR statement you used? I asked for it, but it seems that you missed it. (Mask username/password or any other confidential information.)
Re: sql loader not creating bad file [message #530983 is a reply to message #530921] Fri, 11 November 2011 11:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Please post enough to reproduce the problem, including data file with a few rows, control file, structure of the table that you are loading into, and the command line that you are using to run SQL*Loader.

If your control file uses WHEN clauses, then the log file would show rows rejected due to failing the WHEN clause, but the same rows might be loaded under another WHEN clause. That is the only circumstance that I can guess at, without seeing some sample data, control file, table structure, and command line.

In oracle 10g, per the documentation section in the link below, according to the excerpt below that, all that exit code 2 or EX_WARN means is that some rows are rejected.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_params.htm#g1010455

Result 	Exit Code
All rows loaded successfully 	EX_SUCC
All or some rows rejected 	EX_WARN
All or some rows discarded 	EX_WARN
Discontinued load 	EX_WARN
Command-line or syntax errors 	EX_FAIL
Oracle errors nonrecoverable for SQL*Loader 	EX_FAIL
Operating system errors (such as file open/close and malloc) 	EX_FAIL

For UNIX, the exit codes are as follows:

EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL  3


For Windows NT, the exit codes are as follows:

EX_SUCC 0
EX_WARN 2
EX_FAIL 3
EX_FTL  4


I have provided an example below of how the log file can show that some rows are rejected by one WHEN clause, but loaded under another WHEN clause.

-- test.ctl:
load data
infile *
into table test_tab
when col1='1'
fields terminated by ','
(col1 position(1), col2)
into table test_tab
when col1='2'
fields terminated by ','
(col1 position(1), col3)
begindata
1,a,
2,b,


-- table, load, and results:
SCOTT@orcl_11gR2> create table test_tab
  2    (col1  number,
  3  	col2  varchar2 (4),
  4  	col3  varchar2 (4))
  5  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from test_tab
  2  /

      COL1 COL2 COL3
---------- ---- ----
         1 a
         2      b

2 rows selected.

SCOTT@orcl_11gR2>


-- test.log file showing that one row was rejected by each when clause, but loaded under the other when clause:

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Nov 11 10:35:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   test.ctl
Data File:      test.ctl
  Bad File:     test.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST_TAB, loaded when COL1 = 0X31(character '1')
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                    1     *   ,       CHARACTER            
COL2                                 NEXT     *   ,       CHARACTER            

Table TEST_TAB, loaded when COL1 = 0X32(character '2')
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                    1     *   ,       CHARACTER            
COL3                                 NEXT     *   ,       CHARACTER            


Table TEST_TAB:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  1 Row not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table TEST_TAB:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  1 Row not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  66048 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Nov 11 10:35:25 2011
Run ended on Fri Nov 11 10:35:25 2011

Elapsed time was:     00:00:00.20
CPU time was:         00:00:00.00


[Updated on: Fri, 11 November 2011 12:36]

Report message to a moderator

Re: sql loader not creating bad file [message #531376 is a reply to message #530983] Tue, 15 November 2011 11:37 Go to previous messageGo to next message
lastlad
Messages: 7
Registered: November 2011
Junior Member
OUTPUT OF THE LOG FILE IS:

Table STAGING:
923715 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 1
Total logical records read: 923715
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1290
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Tue Nov 15 16:01:35 2011
Run ended on Tue Nov 15 16:02:01 2011

Elapsed time was: 00:00:25.71
CPU time was: 00:00:08.75


HOWEVER LOADER SPITS TEH FOLLOWING TO THE BATCH.LOG

Sqlldr return code 2 Batch step 4 run finished sucessfully with warnings, some rows were rejected by sqlldr
Re: sql loader not creating bad file [message #531383 is a reply to message #531376] Tue, 15 November 2011 12:42 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
As previously requested, please post enough to reproduce the problem, including data file with a few rows, control file, structure of the table that you are loading into, and the command line that you are using to run SQL*Loader.

Additionally, do you have two different log files of some sort or are the single line and the rest part of the same file or what? What is the name of the log file in your control file or command line? Is this batch.log something that is generated at the operating system level and is an interpretation of the return code or what? I have never known SQL*Loader to output anything that looks like that.




Previous Topic: ORA-01652: unable to extend temp segment by 8192 in tablespace IDX_TAB
Next Topic: Oracle IMPDP
Goto Forum:
  


Current Time: Tue Feb 25 02:41:28 CST 2020