OraFAQ Forum: Server Utilities » SQL*Loader not giving error but no data is inserted

Home » RDBMS Server » Server Utilities » SQL*Loader not giving error but no data is inserted

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

SQL*Loader not giving error but no data is inserted [message #72304]

Thu, 08 May 2003 22:53

HelpNeeded
Messages: 4
Registered: May 2003

Junior Member

Hi,
I've a script which invokes SQL*Loader which reads a text file and populates a table. The job generates logs which specify if a certain record was inserted successfully or rejected due to errors.
I've run this job several times. Every time I see that the SQL*Loader logs specify that the data was inserted correctly. However, when I look into the table, no data is inserted. Can anyone help me in identifying what the problem might be?
Thanks

Report message to a moderator

Re: SQL*Loader not giving error but no data is inserted [message #72307 is a reply to message #72304]

Fri, 09 May 2003 06:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

please post some information!.
your control file, table strucutre, session snapshot or atleast the logfile

Report message to a moderator

Re: SQL*Loader not giving error but no data is inserted [message #72309 is a reply to message #72304]

Fri, 09 May 2003 10:27

HelpNeeded
Messages: 4
Registered: May 2003

Junior Member

Here is the log file - {it also gives details about the table structure) {Control file is generated dynamically using pl-sql) {One thing to note - sql*loader sometimes loads data and sometimes doesn't. But I always get the same log file)

SQL*Loader: Release 8.1.6.3.0 - Production on Fri May 9 08:57:29 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: /home05/ashyamsu/shellscripts/newip/ctl/ip_bad_13.ctl
Data File: /home05/ashyamsu/shellscripts/newip/data/ip_bad_13.txt
Bad File: /home05/ashyamsu/shellscripts/newip/bad/ip_bad_13.bad
Discard File: /home05/ashyamsu/shellscripts/newip/bad/ip_bad_13.dsc
(Allow all discards)

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

Table "STAGING_ADMIN_USER"."IP_BAD", 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
------------------------------ ---------- ----- ---- ---- ---------------------
IP3 FIRST * WHT CHARACTER
MIN_OCTET4 NEXT * WHT CHARACTER
MAX_OCTET4 NEXT * WHT CHARACTER
VERSION CONSTANT
Value is '13'
BAD_LEVEL NEXT * WHT CHARACTER
LEVEL_NAME NEXT * WHT CHARACTER
PARTITION_KEY CONSTANT
Value is '3'

Table "STAGING_ADMIN_USER"."IP_BAD":
2 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.

Space allocated for bind array: 64750 bytes(50 rows)
Space allocated for memory besides bind array: 0 bytes

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

Report message to a moderator

Re: SQL*Loader not giving error but no data is inserted [message #72310 is a reply to message #72309]

Fri, 09 May 2003 11:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

it seems that, data is loaded!~.
note that, you are appending the data.
Make sure u look into the correct logfile etc~
are there any triggers defined on the base table?

Report message to a moderator

Re: SQL*Loader not giving error but no data is inserted [message #72311 is a reply to message #72310]

Fri, 09 May 2003 11:25

HelpNeeded
Messages: 4
Registered: May 2003

Junior Member

It seems that from the logs that the data is loaded, but when I check the table, it is empty.
There are no triggers defined on the table.
And yes, I am looking at the right log file :-)

Report message to a moderator

Re: SQL*Loader not giving error but no data is inserted [message #72312 is a reply to message #72311]

Fri, 09 May 2003 12:31

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

the table should not be empty~
becuase, you are appending the records. NOT DELTING them.
If at any point of time, there are a correct load ( and assuming that there was no delete later), there should be some records there~.
what kind of data is loaded?
is there a possiblity that it is loading null data?
did you check the table from sqlplus?
select count(*) from table_name?

Report message to a moderator

Re: SQL*Loader not giving error but no data is inserted [message #72313 is a reply to message #72312]

Fri, 09 May 2003 12:58

HelpNeeded
Messages: 4
Registered: May 2003

Junior Member

Actaully the table is not empty. Its just that the data which is loaded (a given version) does not appear in the table, even though the logs say data was successfully loaded. The data files are there and they are huge. Another thing I would like to mention is, the same dataload succeeds if I run it second time (note - the table is partitioned)
Here is the control file -
LOAD DATA
INFILE '/home05/ashyamsu/shellscripts/newip/data/ip_bad_14.txt'
INTO TABLE ip_bad
APPEND
FIELDS TERMINATED BY 't'
TRAILING NULLCOLS
(
IP3
,MIN_OCTET4
,MAX_OCTET4
,VERSION constant 14
,BAD_LEVEL
,LEVEL_NAME
,PARTITION_KEY constant 4
)

Report message to a moderator

Previous Topic:	Oracle 8i 8.1.6 Export on Linux
Next Topic:	how can i import an appropriate index structure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 08 20:17:37 CST 2024