OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL Loader Error (Oracle, 11g R2, Redhat Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader Error [message #576719]

Thu, 07 February 2013 09:01

soph75
Messages: 7
Registered: February 2013

Junior Member

Hello,

I'm trying to load data into a table using SQL Loader but getting a failure error below. Did anyone encountered similar issue?

Log File
========

SQL*Loader: Release 11.2.0.2.0 - Production on Wed Feb 6 23:54:25 2013

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

Control File: /opt/Infor/Outbound_Marketing/7.2.2/EM/metadata/trans.ldr
Data File: /opt/Infor/Outbound_Marketing/7.2.2/EM/logs/trans.log
Bad File: trans.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 CME_DATA_STAGE_TRANS, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DATE_STR FIRST * CHARACTER
Terminator string : '~,~'
CAMPAIGN_SSKEY NEXT * CHARACTER
Terminator string : '~,~'
CELL_SSKEY NEXT * CHARACTER
Terminator string : '~,~'
DIMENSION_SSKEY NEXT * CHARACTER
Terminator string : '~,~'
DIMENSION_UNIQUE_ID NEXT * CHARACTER
Terminator string : '~,~'
CMEID NEXT * CHARACTER
Terminator string : '~,~'
SENT_DATE_STR NEXT * CHARACTER
Terminator string : '~,~'
STATUS NEXT * CHARACTER
Terminator string : '~,~'
VALUE NEXT 2000 CHARACTER
Terminator string : '~,~'
"trans.log" [readonly] 84L, 3357C
VALUE NEXT 2000 CHARACTER
Terminator string : '~,~'
VALUE2 NEXT * CHARACTER
Terminator string : '~,~'
OP NEXT * CHARACTER
Terminator string : '~,~'
SQL string for column : "nvl(:op,0)"

value used for ROWS parameter changed from 64 to 55
Record 379873: Discarded - all columns null.
Record 396780: Discarded - all columns null.
Record 413063: Discarded - all columns null.
Record 414139: Discarded - all columns null.
Record 419631: Discarded - all columns null.
Record 429773: Discarded - all columns null.
Record 429776: Discarded - all columns null.
Record 431422: Discarded - all columns null.
Record 479639: Discarded - all columns null.
Record 693485: Discarded - all columns null.
Record 696417: Discarded - all columns null.
Record 810543: Discarded - all columns null.
Record 816269: Discarded - all columns null.

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

Space allocated for bind array: 252010 bytes(55 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 822378
Total logical records rejected: 0
Total logical records discarded: 13

Run began on Wed Feb 06 23:54:25 2013
Run ended on Wed Feb 06 23:55:43 2013

Elapsed time was: 00:01:18.14
CPU time was: 00:00:10.50

Control File
============

load data
infile 'specify in job'
badfile 'trans.bad'
append
into table CME_DATA_STAGE_TRANS
fields terminated by "~,~"
TRAILING NULLCOLS
(date_str, campaign_sskey, cell_sskey, dimension_sskey, dimension_unique_ID, cmeid, sent_date_str, status, value CHAR(2000), value2, op "nvl(:op,0)")

Sample Data
============

2013-01-16 14:35:45~,~34072~,~34072_44~,~2891044~,~MB~,~494801.69a85~,~2013-01-16 14:35:45~,~~,~MPA~,~~,~G
2013-01-16 14:35:47~,~34072~,~34072_4~,~3607969~,~MB~,~494801.69a86~,~2013-01-16 14:35:47~,~~,~MPA~,~~,~G
2013-01-16 14:35:48~,~34072~,~34072_108~,~2317029~,~MB~,~494801.69a87~,~2013-01-16 14:35:48~,~~,~MPA~,~~,~G
2013-01-16 23:12:48~,~34072~,~34072_108~,~2317029~,~MB~,~494801.69a87~,~2013-01-16 14:35:48~,~{AC16E120-013C4492225D-7FF9-00BBF65C}~,~

Interval~,~R
2013-01-16 14:35:49~,~34072~,~34072_110~,~2588482~,~MB~,~494801.69a88~,~2013-01-16 14:35:49~,~~,~MPA~,~~,~G
2013-01-16 14:35:51~,~34072~,~34072_110~,~2685560~,~MB~,~494801.69a89~,~2013-01-16 14:35:51~,~~,~MPA~,~~,~G
2013-01-16 14:35:52~,~34072~,~34072_69~,~7908201~,~MB~,~494801.69a8a~,~2013-01-16 14:35:52~,~~,~MPA~,~~,~G

Thanks,
Soph

Report message to a moderator

Re: SQL Loader Error [message #576721 is a reply to message #576719]

Thu, 07 February 2013 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

post content of trans.bad file

Report message to a moderator

Re: SQL Loader Error [message #576730 is a reply to message #576721]

Thu, 07 February 2013 10:15

soph75
Messages: 7
Registered: February 2013

Junior Member

It did not produce trans.bad file - I see where the problem is in the datafile where there are few null fields... Is it possible to skip all null fields columns in ctl file without altering the DDL target table? look below NULL row

Datafile sample
===============
2013-01-16 14:35:48~,~34072~,~34072_108~,~2317029~,~MB~,~494801.69a87~,~2013-01-16 14:35:48~,~~,~MPA~,~~,~G
2013-01-16 23:12:48~,~34072~,~34072_108~,~2317029~,~MB~,~494801.69a87~,~2013-01-16 14:35:48~,~{AC16E120-013C4492225D-7FF9-00BBF65C}~,~

Interval~,~R
2013-01-16 14:35:49~,~34072~,~34072_110~,~2588482~,~MB~,~494801.69a88~,~2013-01-16 14:35:49~,~~,~MPA~,~~,~G
2013-01-16 14:35:51~,~34072~,~34072_110~,~2685560~,~MB~,~494801.69a89~,~2013-01-16 14:35:51~,~~,~MPA~,~~,~G

Target DDL
==========
CREATE TABLE "NAME"
(DATE_STR VARCHAR2(50 BYTE) NOT NULL,
CAMPAIGN_SSKEY VARCHAR2(50 BYTE),
CELL_SSKEY VARCHAR2(50 BYTE),
DIMENSION_SSKEY VARCHAR2(255 BYTE),
CMEID VARCHAR2(255 BYTE),
SENT_DATE_STR VARCHAR2(255 BYTE),
STATUS VARCHAR2(255 BYTE),
VALUE VARCHAR2(2000 BYTE),
OP CHAR(100 BYTE) NOT NULL ,
VALUE2 VARCHAR2(2000 BYTE),
DIMENSION_UNIQUE_ID VARCHAR2(2 BYTE)
);

Report message to a moderator

Re: SQL Loader Error [message #576741 is a reply to message #576730]

Thu, 07 February 2013 11:18

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Your SQL*Loader log file indicates that 822378 rows were read, 822365 of them were loaded, and 13 rows were not loaded due to all columns in those rows being null. That seems to be what you want it to do. So, what's the problem?

Report message to a moderator

Re: SQL Loader Error [message #576746 is a reply to message #576741]

Thu, 07 February 2013 11:33

soph75
Messages: 7
Registered: February 2013

Junior Member

The problem is my syscall is blowing up due to 13 null rows. if SQL*Loader is indicating ==> ...13 rows were not loaded due to all columns in those rows being null. It does not mean they were implicitly skipped... it is still failing to append data.

Report message to a moderator

Re: SQL Loader Error [message #576747 is a reply to message #576746]

Thu, 07 February 2013 11:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>The problem is my syscall is blowing up due to 13 null rows.
what is "syscall"?
what is "blowing up"?

Report message to a moderator

Re: SQL Loader Error [message #576750 is a reply to message #576747]

Thu, 07 February 2013 12:41

soph75
Messages: 7
Registered: February 2013

Junior Member

syscall is a system call that allows ETL to access outside programs and services. in my case is SQL Loader via call script below

control=$$INSTALLROOTDIR/EM/metadata/trans.ldr log=trans.log bad=trans.bad data=$$INSTALLROOTDIR/EM/logs/trans.log

Blowing up = failing to proceed

Report message to a moderator

Re: SQL Loader Error [message #576751 is a reply to message #576750]

Thu, 07 February 2013 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Garbage In; Garbage Out

What prevent you from removing the bad records prior to starting SQLLoader?

You have "bad data" in the input file; so how exactly can we assist you?

Report message to a moderator

Re: SQL Loader Error [message #576752 is a reply to message #576751]

Thu, 07 February 2013 13:22

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

The SQL*Loader log file indicates that the entire load was completed. I don't understand what you think is "blowing up" or "failing to proceed". Does the same thing happen when you test with a small data file with a few good rows? Is this really a problem with your ETL not handling a SQL*Loader return code or some such thing? Does it just stop when any SQL*Loader run, successful or not, finishes?

Report message to a moderator

Re: SQL Loader Error [message #576858 is a reply to message #576752]

Fri, 08 February 2013 11:58

soph75
Messages: 7
Registered: February 2013

Junior Member

I've tested loading a file using cmd prompt and it's working

C:\Users\scherrak>sqlldr user/pass@db control=C:\sqlloader\transldrte
st.ctl log=c:\sqlloader\trans_testdata.log -direct

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Feb 7 16:59:05 2013

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

Load completed - logical record count 21.

===================

The ETL app is not returning any relevant oracle or SQL*Loader error code.. it does stop just after "Commit point reached - logical record count 34821"

It does load data when I clean up the file manually. it must be something with syscall that's preventing ETL to continue..

Report message to a moderator

Re: SQL Loader Error [message #576861 is a reply to message #576858]

Fri, 08 February 2013 12:43

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Does it work if you use conventional path instead of direct path load?

Report message to a moderator

Re: SQL Loader Error [message #576986 is a reply to message #576861]

Mon, 11 February 2013 10:22

soph75
Messages: 7
Registered: February 2013

Junior Member

Yes it does work conventional path.

C:\Users\scherrak>sqlldr user/pass@asdev control=C:\sqlloader\transldrte
st.ctl.txt log=c:\sqlloader\trans_testdata.log

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Feb 11 11:05:23 2013

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

Commit point reached - logical record count 20
Commit point reached - logical record count 21

Report message to a moderator

Re: SQL Loader Error [message #576995 is a reply to message #576986]

Mon, 11 February 2013 12:00

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

I believe that when SQL*Loader uses the direct path it expects all data to be valid, but the conventional path can deal with rejected rows. So, I think you need to either clean up the data before loading or use the conventional path.

Report message to a moderator

Re: SQL Loader Error [message #577237 is a reply to message #576995]

Wed, 13 February 2013 11:47

soph75
Messages: 7
Registered: February 2013

Junior Member

I am cleaning the data before loading into the target table. thanks

Report message to a moderator

Previous Topic:	Reg DAtabase directory
Next Topic:	Tracing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:26:16 CST 2025