Home » RDBMS Server » Server Utilities » SQL Loader - Unable to load data (ORACLE 11.1.0.6.0 WIN 8)
SQL Loader - Unable to load data [message #616029] Wed, 11 June 2014 13:46 Go to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Dear All,

I have created below table and tried to upload data from csv file but unable to load.

CREATE TABLE TEST_SQLLDR
(ID                                                          VARCHAR2(10),
 NAME                                                       VARCHAR2(4000),
 NUM                                                 VARCHAR2(40)
)
/


Below are the contents of control file
**************************************************************************************************
load data
infile 'D:\AMK\SQLLDR\TEST_SQLLDR.csv'
into table TEST_SQLLDR
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(ID ,NAME,NUM)
**************************************************************************************************



Below is sqlldr command

D:\AMK\SQLLDR>sqlldr scott/tiger control=TEST_SQLLDR.ctl log=log1.log

/*
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Jun 12 00:02:45 2014

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

Commit point reached - logical record count 54
*/


Above sqlldr command is running without any error but not inserting any data into table.
Please let me know how to handle multi-line data as in attached csv file.

Thanks,
Anil MK

[Updated on: Wed, 11 June 2014 13:51]

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616030 is a reply to message #616029] Wed, 11 June 2014 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORACLE 11.1.0.6.0 WIN Cool
is above a supported combination of OS version & Oracle version?

post complete content of log1.log file

BTW, I find it strange & misleading to have a column named "NUM" & for it to be datatype VARCHAR2.
Re: SQL Loader - Unable to load data [message #616031 is a reply to message #616029] Wed, 11 June 2014 14:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like your newline character is different on your operating system than mine, but if your records are consistently 3 rows per record, then you can use CONCATENATE 3, as demonstrated below.

SCOTT@orcl12c> HOST TYPE test_sqlldr.csv
AB1234,"This is oracle db
This is sqlldr
",10
AB1234,"This is Oracle SQL.
Oracle stores more data.
",9B
AB1234,"This is oracle db
This is sqlldr
",12
AB1234,"This is Oracle SQL.
Oracle stores more data.
",14
CD8764,"This is oracle db
This is sqlldr
",16
CD8764,"This is Oracle SQL.
Oracle stores more data.
",19
CD8764,"This is oracle db
This is sqlldr
",20
CD8764,"This is Oracle SQL.
Oracle stores more data.
",22
EF8945,"This is oracle db
This is sqlldr
",24
EF8945,"This is Oracle SQL.
Oracle stores more data.
",26
EF8945,"This is oracle db
This is sqlldr
",28
EF8945,"This is Oracle SQL.
Oracle stores more data.
",30

SCOTT@orcl12c> HOST TYPE test_sqlldr.ctl
load data
infile 'TEST_SQLLDR.csv'
CONCATENATE 3
into table TEST_SQLLDR
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(ID ,NAME,NUM)

SCOTT@orcl12c> CREATE TABLE TEST_SQLLDR
  2  (ID                                                          VARCHAR2(10),
  3   NAME                                                       VARCHAR2(4000),
  4   NUM                                                 VARCHAR2(40)
  5  )
  6  /

Table created.

SCOTT@orcl12c> HOST sqlldr scott/tiger control=TEST_SQLLDR.ctl log=log1.log

SQL*Loader: Release 12.1.0.1.0 - Production on Wed Jun 11 12:23:49 2014

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

Path used:      Conventional
Commit point reached - logical record count 12

Table TEST_SQLLDR:
  12 Rows successfully loaded.

Check the log file:
  log1.log
for more information about the load.

SCOTT@orcl12c> COLUMN name FORMAT A45
SCOTT@orcl12c> COLUMN num  FORMAT A10
SCOTT@orcl12c> SELECT * FROM test_sqlldr
  2  /

ID         NAME                                          NUM
---------- --------------------------------------------- ----------
AB1234     This is oracle dbThis is sqlldr               10
AB1234     This is Oracle SQL.Oracle stores more data.   9B
AB1234     This is oracle dbThis is sqlldr               12
AB1234     This is Oracle SQL.Oracle stores more data.   14
CD8764     This is oracle dbThis is sqlldr               16
CD8764     This is Oracle SQL.Oracle stores more data.   19
CD8764     This is oracle dbThis is sqlldr               20
CD8764     This is Oracle SQL.Oracle stores more data.   22
EF8945     This is oracle dbThis is sqlldr               24
EF8945     This is Oracle SQL.Oracle stores more data.   26
EF8945     This is oracle dbThis is sqlldr               28
EF8945     This is Oracle SQL.Oracle stores more data.   30

12 rows selected.

Re: SQL Loader - Unable to load data [message #616050 is a reply to message #616031] Thu, 12 June 2014 01:03 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi Barbara,

Thanks for the reply, I have tried with concatenate 3 option but still not loading the records.
And host command is not recognized. Please let me know is there any other way to solve


D:\AMK\SQLLDR>sqlldr scott/tiger control=TEST_SQLLDR.ctl log=log1.log

SQL*Loader: Release 11.1.0.6.0 - Production on Thu Jun 12 11:21:56 2014

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

Commit point reached - logical record count 18

SQL> select * from TEST_SQLLDR;

no rows selected



Thanks,
Anil MK
Re: SQL Loader - Unable to load data [message #616052 is a reply to message #616050] Thu, 12 June 2014 01:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why didn't you post contents of the log file? Information you provided is close to useless.
Re: SQL Loader - Unable to load data [message #616053 is a reply to message #616050] Thu, 12 June 2014 01:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
As others have said, we need to see the log file. I used the host command because I ran it from SQL*Plus. You didn't use the host command because you are running it from the operating system.
Re: SQL Loader - Unable to load data [message #616057 is a reply to message #616053] Thu, 12 June 2014 01:55 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,

Please find attached log file.

Thanks,
Anil MK
  • Attachment: log1.log
    (Size: 3.83KB, Downloaded 1719 times)
Re: SQL Loader - Unable to load data [message #616059 is a reply to message #616057] Thu, 12 June 2014 02:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:
Above sqlldr command is running without any error but not inserting any data into table


All the 18 rows have been rejected due to data error. None of the rows got loaded.

Re: SQL Loader - Unable to load data [message #616067 is a reply to message #616057] Thu, 12 June 2014 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your log is clear, your data do not match the description in the control file.
  0 Rows successfully loaded.
  18 Rows not loaded due to data errors.

Nothing has been loaded so there is nothing in the table.

Re: SQL Loader - Unable to load data [message #616069 is a reply to message #616067] Thu, 12 June 2014 02:48 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,

How to handle this to load the data? Please help.

Thanks,
Anil MK
Re: SQL Loader - Unable to load data [message #616072 is a reply to message #616069] Thu, 12 June 2014 02:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which data did you load? The same you attached in your first message? Which control file did you use?

Why wouldn't you do exactly what Barbara did? In her post, we see everything. In your posts, we see nothing. It looks as if you are doing everything NOT to load data (or do it as late as possible).
Re: SQL Loader - Unable to load data [message #616078 is a reply to message #616072] Thu, 12 June 2014 03:44 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,

Sorry I didn't execute as Barbara executed.
I have tried the same way as Barbara did without changing any steps.
Now I am getting error while running below step

SQL>  HOST sqlldr scott/tiger control=TEST_SQLLDR.ctl log=log1.log

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jun 12 13:33:47 2014

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

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12560: TNS:protocol adapter error


Please find attached log file.

Thanks,
Anil MK
  • Attachment: log1.log
    (Size: 0.26KB, Downloaded 1464 times)
Re: SQL Loader - Unable to load data [message #616082 is a reply to message #616078] Thu, 12 June 2014 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't try and run sqlloader from within sqlplus. Barbara's got it set up so it'll work, you apparently don't.
Re: SQL Loader - Unable to load data [message #616083 is a reply to message #616078] Thu, 12 June 2014 04:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Run sqlldr the way you did it in your first post. Follow the suggested steps and copy paste it back here.
Re: SQL Loader - Unable to load data [message #616085 is a reply to message #616082] Thu, 12 June 2014 04:11 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,

Please find SQL statements executed in same way as Barbara did.

SQL> HOST TYPE test_sqlldr.csv
AB1234,"This is oracle db
This is sqlldr

",10
AB1234,"This is Oracle SQL.

Oracle stores more data.

",9B
AB1234,"This is oracle db
This is sqlldr

",12
AB1234,"This is Oracle SQL.

Oracle stores more data.

",14
CD8764,"This is oracle db
This is sqlldr

",16
CD8764,"This is Oracle SQL.

Oracle stores more data.

",19
CD8764,"This is oracle db
This is sqlldr

",20
CD8764,"This is Oracle SQL.

Oracle stores more data.

",22
EF8945,"This is oracle db
This is sqlldr

",24
EF8945,"This is Oracle SQL.

Oracle stores more data.

",26
EF8945,"This is oracle db
This is sqlldr

",28
EF8945,"This is Oracle SQL.

Oracle stores more data.

",30

SQL> HOST TYPE test_sqlldr.ctl
load data
infile 'TEST_SQLLDR.csv'
CONCATENATE 3
into table TEST_SQLLDR
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(ID ,NAME,NUM)
SQL> CREATE TABLE TEST_SQLLDR
  2      (ID                                                          VARCHAR2
0),
  3       NAME                                                       VARCHAR2(
00),
  4       NUM                                                 VARCHAR2(40)
  5      )
  6      /

Table created.

SQL> HOST sqlldr scott/tiger control=TEST_SQLLDR.ctl log=log1.log

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jun 12 14:34:51 2014

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

Commit point reached - logical record count 18

SQL>


Please find attached log.

Thanks,
Anil MK
  • Attachment: log1.log
    (Size: 3.87KB, Downloaded 1757 times)
Re: SQL Loader - Unable to load data [message #616092 is a reply to message #616085] Thu, 12 June 2014 04:45 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's the difference between your and Barbara's input data. While she has consistent 3 lines per record, your values vary from 4 to 5:

/forum/fa/11955/0/

Is that really so? If it is, you can't use CONCATENATE as it combines the same number of physical records into one logical record.

(If you remove empty lines, data is successfully loaded).
Re: SQL Loader - Unable to load data [message #616108 is a reply to message #616092] Thu, 12 June 2014 05:31 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,

Is there a way handle such records as above. Please suggest.

Thanks,
Anil MK
Re: SQL Loader - Unable to load data [message #616119 is a reply to message #616108] Thu, 12 June 2014 07:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You know how it goes ... garbage in, garbage out. I don't know how to do that; *maybe* CONTINUEIF could help, but I can't spend any time in solving this problem at the moment. If everyone else fails, I'd put my bet on Barbara - she's THE expert with SQL*Loader. Wait until she wakes up.
Re: SQL Loader - Unable to load data [message #616132 is a reply to message #616119] Thu, 12 June 2014 08:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If the id column is consistently 6 characters with the 7th character a comma and there is no case where there is another line in the same data row where the 7th character is a comma, then you can use:

CONTINUEIF NEXT PRESERVE (7) != ','

as demonstrated below. If not, then you will need to find some other means of identifying where a record begins or ends. You may need to obtain your data in a different format.

SCOTT@orcl12c> HOST TYPE test_sqlldr.csv
AB1234,"This is oracle db
This is sqlldr

",10
AB1234,"This is Oracle SQL.

Oracle stores more data.

",9B
AB1234,"This is oracle db
This is sqlldr

",12
AB1234,"This is Oracle SQL.

Oracle stores more data.

",14
CD8764,"This is oracle db
This is sqlldr

",16
CD8764,"This is Oracle SQL.

Oracle stores more data.

",19
CD8764,"This is oracle db
This is sqlldr

",20
CD8764,"This is Oracle SQL.

Oracle stores more data.

",22
EF8945,"This is oracle db
This is sqlldr

",24
EF8945,"This is Oracle SQL.

Oracle stores more data.

",26
EF8945,"This is oracle db
This is sqlldr

",28
EF8945,"This is Oracle SQL.

Oracle stores more data.

",30

SCOTT@orcl12c> HOST TYPE test_sqlldr.ctl
load data
infile 'TEST_SQLLDR.csv'
CONTINUEIF NEXT PRESERVE (7) != ','
into table TEST_SQLLDR
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(ID ,NAME,NUM)

SCOTT@orcl12c> CREATE TABLE TEST_SQLLDR
  2  (ID                                                          VARCHAR2(10),
  3   NAME                                                       VARCHAR2(4000),
  4   NUM                                                 VARCHAR2(40)
  5  )
  6  /

Table created.

SCOTT@orcl12c> HOST sqlldr scott/tiger control=TEST_SQLLDR.ctl log=log1.log

SQL*Loader: Release 12.1.0.1.0 - Production on Thu Jun 12 06:24:40 2014

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

Path used:      Conventional
Commit point reached - logical record count 11
Commit point reached - logical record count 12

Table TEST_SQLLDR:
  12 Rows successfully loaded.

Check the log file:
  log1.log
for more information about the load.

SCOTT@orcl12c> COLUMN name FORMAT A45
SCOTT@orcl12c> COLUMN num  FORMAT A10
SCOTT@orcl12c> SELECT * FROM test_sqlldr
  2  /

ID         NAME                                          NUM
---------- --------------------------------------------- ----------
AB1234     This is oracle dbThis is sqlldr               10
AB1234     This is Oracle SQL.Oracle stores more data.   9B
AB1234     This is oracle dbThis is sqlldr               12
AB1234     This is Oracle SQL.Oracle stores more data.   14
CD8764     This is oracle dbThis is sqlldr               16
CD8764     This is Oracle SQL.Oracle stores more data.   19
CD8764     This is oracle dbThis is sqlldr               20
CD8764     This is Oracle SQL.Oracle stores more data.   22
EF8945     This is oracle dbThis is sqlldr               24
EF8945     This is Oracle SQL.Oracle stores more data.   26
EF8945     This is oracle dbThis is sqlldr               28
EF8945     This is Oracle SQL.Oracle stores more data.   30

12 rows selected.

Re: SQL Loader - Unable to load data [message #616156 is a reply to message #616132] Thu, 12 June 2014 10:58 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

HI Barbara,

Followed same steps but still issue while loading data.

SQL> HOST sqlldr CSMARTIRIS/Floren32@SMARTQ1 control=TEST_SQLLDR.ctl log=log1.lo
g

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jun 12 21:25:28 2014

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

Commit point reached - logical record count 11
Commit point reached - logical record count 12


Please find attached log file.

Thanks,
Anil MK
  • Attachment: log1.log
    (Size: 3.60KB, Downloaded 1423 times)
Re: SQL Loader - Unable to load data [message #616157 is a reply to message #616156] Thu, 12 June 2014 11:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Why don't you analyze the log file yourself? ID has length defined as 10, your data is more than that limit.
Re: SQL Loader - Unable to load data [message #616161 is a reply to message #616156] Thu, 12 June 2014 11:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You must have used a different control file, because, according to your log file, you are using fields terminated by '^' not ',', so that would cause it to read past the comma in position 7 and try to read the whole line as part of the id. You need to provide a complete post as I did, including the data, control file, run, and the log file. It worked for me, so if it is not working for you, then it is because you are using something different, either different data or different control file or both.
Re: SQL Loader - Unable to load data [message #616163 is a reply to message #616161] Thu, 12 June 2014 13:53 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Thanks Barbara, its working now after bringing excel single cell data in single line instead of multi-line (single cell) data.
I have changed excel cell data and it got resolved.
Please let me know if there is any option in sqlloader to handle single cell data which is divided into multi-line.

Thanks to all for your suggestion.

Regards,
Anil MK
Re: SQL Loader - Unable to load data [message #616164 is a reply to message #616163] Thu, 12 June 2014 14:11 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please let me know if there is any option in sqlloader to handle single cell data which is divided into multi-line.


Is this not what she showed you?

Previous Topic: How to estimate the size of dump file
Next Topic: .xls file into table by using sqlloader
Goto Forum:
  


Current Time: Thu Mar 28 09:51:45 CDT 2024