Home » RDBMS Server » Server Utilities » SQL Loader Error (Oracle, 11g R2, Redhat Linux)
SQL Loader Error [message #576719] Thu, 07 February 2013 09:01 Go to next message
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
Re: SQL Loader Error [message #576721 is a reply to message #576719] Thu, 07 February 2013 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
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
Re: SQL Loader Error [message #576730 is a reply to message #576721] Thu, 07 February 2013 10:15 Go to previous messageGo to next message
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)
);
Re: SQL Loader Error [message #576741 is a reply to message #576730] Thu, 07 February 2013 11:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7967
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?
Re: SQL Loader Error [message #576746 is a reply to message #576741] Thu, 07 February 2013 11:33 Go to previous messageGo to next message
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.
Re: SQL Loader Error [message #576747 is a reply to message #576746] Thu, 07 February 2013 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
>The problem is my syscall is blowing up due to 13 null rows.
what is "syscall"?
what is "blowing up"?

Re: SQL Loader Error [message #576750 is a reply to message #576747] Thu, 07 February 2013 12:41 Go to previous messageGo to next message
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
Re: SQL Loader Error [message #576751 is a reply to message #576750] Thu, 07 February 2013 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
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?
Re: SQL Loader Error [message #576752 is a reply to message #576751] Thu, 07 February 2013 13:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7967
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?
Re: SQL Loader Error [message #576858 is a reply to message #576752] Fri, 08 February 2013 11:58 Go to previous messageGo to next message
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..


Re: SQL Loader Error [message #576861 is a reply to message #576858] Fri, 08 February 2013 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7967
Registered: November 2002
Location: California, USA
Senior Member
Does it work if you use conventional path instead of direct path load?
Re: SQL Loader Error [message #576986 is a reply to message #576861] Mon, 11 February 2013 10:22 Go to previous messageGo to next message
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
Re: SQL Loader Error [message #576995 is a reply to message #576986] Mon, 11 February 2013 12:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7967
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.
Re: SQL Loader Error [message #577237 is a reply to message #576995] Wed, 13 February 2013 11:47 Go to previous message
soph75
Messages: 7
Registered: February 2013
Junior Member
I am cleaning the data before loading into the target table. thanks
Previous Topic: Reg DAtabase directory
Next Topic: "ORA-00904 POLTYP: invalid identifier" when export
Goto Forum:
  


Current Time: Fri Aug 22 11:43:19 CDT 2014

Total time taken to generate the page: 0.09490 seconds