Home » RDBMS Server » Server Utilities » SQL loader problem (oracle 10g and Red Hat Linux 5)
SQL loader problem [message #562986] Tue, 07 August 2012 11:06 Go to next message
morad_dba
Messages: 93
Registered: June 2008
Member
Dear all,

I am trying to insert rows in two tables using sql loader.

I have two tables in database as

SQL> desc name
Name Null? Type
---------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

SQL> desc name3
Name Null? Type
--------------------- ----------- -------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE


I created controlfiles as

[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'
(id position(1:1)integer external, name position (3:6) , bd position (8:40) date "mon-dd-yyyy")
into table name3 truncate
when id='3'
(id position(1:1) integer external, name position (3:6), bd position (8:40) date "mon-dd-yyyy")
begindata
1 adil dec-10-2009
3 masum nov-19-2009


when i run sql loader as

[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 2.

no rows is inserted..

the log file contain entries as

[oracle@DBTEST sqldri]$ cat datafile.log

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table NAME, loaded when ID = 0X31(character '1')
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID 1:1 1 CHARACTER
NAME 3:6 4 CHARACTER
BD 8:40 33 DATE mon-dd-yyyy

Table NAME3, loaded when ID = 0X33(character '3')
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID 1:1 1 CHARACTER
NAME 3:6 4 CHARACTER
BD 8:40 33 DATE mon-dd-yyyy

Record 1: Rejected - Error on table NAME, column BD.
ORA-01843: not a valid month

Record 2: Rejected - Error on table NAME3, column BD.
ORA-01843: not a valid month

Table NAME:
0 Rows successfully loaded.
1 Row 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 NAME3:
0 Rows successfully loaded.
1 Row 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.

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

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

Run began on Tue Aug 07 23:30:07 2012
Run ended on Tue Aug 07 23:30:07 2012

Elapsed time was: 00:00:00.06
CPU time was: 00:00:00.01
[oracle@DBTEST sqldri]$


Can you please inform me what is wrong in my control file...

Regards,
Morad.

Report message to a moderator

Re: SQL loader problem [message #562991 is a reply to message #562986] Tue, 07 August 2012 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Your fields are not fixed length, for instance "masum" is not 4 characters but 5.
Use the option "fields terminated by ' '".
There are examples in the documentation and here, in this (sub)forum.

Regards
Michel

Report message to a moderator

Re: SQL loader problem [message #563049 is a reply to message #562991] Wed, 08 August 2012 01:50 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member

Dear all,

I rewrite the control file as

[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'
fields terminated by ','
(id, name, bd date "mon-dd-yyyy")
into table name3 truncate
when id='3'
fields terminated by ','
(id, name, bd date "mon-dd-yyyy")
begindata
1,adil,dec-10-2009
1,Rahman,Oct-13-2008
3,masum,nov-19-2009


Here... First 2 rows were inserted into NAME table...
But Third row was not inserted into Name3 table..

SQL> select * from name;

ID NAME BD
---------- -------------------- ---------
1 adil 10-DEC-09
1 Rahman 13-OCT-08

SQL> select * from name3;

no rows selected


Thie logfile entry is


[oracle@DBTEST sqldri]$ cat datafile.log

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Aug 8 14:06:50 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table NAME, loaded when ID = 0X31(character '1')
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
BD NEXT * , DATE mon-dd-yyyy

Table NAME3, loaded when ID = 0X33(character '3')
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID NEXT * , CHARACTER
NAME NEXT * , CHARACTER
BD NEXT * , DATE mon-dd-yyyy

Record 3: Discarded - failed all WHEN clauses.

Table NAME:
2 Rows 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.

Date cache:
Max Size: 1000
Entries : 2
Hits : 0
Misses : 0


Table NAME3:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
3 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: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 1
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Aug 08 14:06:50 2012
Run ended on Wed Aug 08 14:06:51 2012

Elapsed time was: 00:00:00.22
CPU time was: 00:00:00.00
[oracle@DBTEST sqldri]$


Can you please tell me why third row were not inserted in Name3 table

Regards,

Morad.

Report message to a moderator

Re: SQL loader problem [message #563051 is a reply to message #563049] Wed, 08 August 2012 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
fields terminated by ','

The fields are not terminated by a comma but by a space.

Regards
Michel

Report message to a moderator

Re: SQL loader problem [message #563052 is a reply to message #563051] Wed, 08 August 2012 02:13 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member


oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'
fields terminated by ','
(id, name, bd date "mon-dd-yyyy")
into table name3 truncate
when id='3'
fields terminated by ','
(id, name, bd date "mon-dd-yyyy")
begindata
1,adil,dec-10-2009
1,Rahman,Oct-13-2008
3,masum,nov-19-2009


Here all fields are terminated by comma....

Already first table were populated by two rows...

only third rows were not inserted...

SQL> select * from name;

ID NAME BD
---------- -------------------- ---------
1 adil 10-DEC-09
1 Rahman 13-OCT-08

SQL> select * from name3;

no rows selected


Can u please tell me what is wrong in control file


regards,
MOrad.

Report message to a moderator

Re: SQL loader problem [message #563054 is a reply to message #563052] Wed, 08 August 2012 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What's inside the log file?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

And post CREATE TABLE statements to create your tables then I should be able to test myself instead of to just try to guess.

Regards
Michel

[Updated on: Wed, 08 August 2012 02:22]

Report message to a moderator

Re: SQL loader problem [message #563056 is a reply to message #563054] Wed, 08 August 2012 02:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
When you load into multiple tables, even when the fields are delimited, you need to reset the position for each table after the first one.

options (direct=true)
load data
INFILE *
into table name truncate
when id='1'
fields terminated by ','
(id, name, bd date "mon-dd-yyyy")
into table name3 truncate
when id='3'
fields terminated by ','
(id position(1),
name, bd date "mon-dd-yyyy")
begindata
1,adil,dec-10-2009
1,Rahman,Oct-13-2008
3,masum,nov-19-2009

Report message to a moderator

Re: SQL loader problem [message #563057 is a reply to message #563056] Wed, 08 August 2012 03:23 Go to previous message
morad_dba
Messages: 93
Registered: June 2008
Member
Dear Barbara Boehmer,

Thank Barbara
Yor posted a very excellent and efficient answer ...

It works...

SQL> select * from name;

ID NAME BD
---------- -------------------- ---------
1 adil 10-DEC-09
1 Rahman 13-OCT-08

SQL> select * from name3;

ID NAME BD
---------- -------------------- ---------
3 masum 19-NOV-09

SQL>


All data have been inserted in all table as condition.

Thanks again...

Regards,
Morad.

Report message to a moderator

Previous Topic: Exporting schema using filesize parameter
Next Topic: Load from a text file
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Feb 11 18:25:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.