OraFAQ Forum: Server Utilities » SQL Loader - Unable to load data

Home » RDBMS Server » Server Utilities » SQL Loader - Unable to load data (ORACLE 11.1.0.6.0 WIN 8)

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

SQL Loader - Unable to load data [message #616029]

Wed, 11 June 2014 13:46

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

Attachment: TEST_SQLLDR.csv
(Size: 0.64KB, Downloaded 2242 times)

[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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ORACLE 11.1.0.6.0 WIN Cool

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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616031 is a reply to message #616029]

Wed, 11 June 2014 14:27

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616050 is a reply to message #616031]

Thu, 12 June 2014 01:03

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

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616052 is a reply to message #616050]

Thu, 12 June 2014 01:06

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616053 is a reply to message #616050]

Thu, 12 June 2014 01:17

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616057 is a reply to message #616053]

Thu, 12 June 2014 01:55

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 2346 times)

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616059 is a reply to message #616057]

Thu, 12 June 2014 02:01

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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616067 is a reply to message #616057]

Thu, 12 June 2014 02:46

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616069 is a reply to message #616067]

Thu, 12 June 2014 02:48

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

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616072 is a reply to message #616069]

Thu, 12 June 2014 02:56

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616078 is a reply to message #616072]

Thu, 12 June 2014 03:44

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 2041 times)

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616082 is a reply to message #616078]

Thu, 12 June 2014 03:52

cookiemonster
Messages: 13961
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616083 is a reply to message #616078]

Thu, 12 June 2014 04:00

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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616085 is a reply to message #616082]

Thu, 12 June 2014 04:11

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 2371 times)

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616092 is a reply to message #616085]

Thu, 12 June 2014 04:45

Littlefoot
Messages: 21823
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/

/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).

Attachment: sqlldr_concatenate.png
(Size: 24.07KB, Downloaded 3799 times)

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616108 is a reply to message #616092]

Thu, 12 June 2014 05:31

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

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616119 is a reply to message #616108]

Thu, 12 June 2014 07:44

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616132 is a reply to message #616119]

Thu, 12 June 2014 08:29

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616156 is a reply to message #616132]

Thu, 12 June 2014 10:58

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 2011 times)

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616157 is a reply to message #616156]

Thu, 12 June 2014 11:05

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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616161 is a reply to message #616156]

Thu, 12 June 2014 11:27

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616163 is a reply to message #616161]

Thu, 12 June 2014 13:53

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

Report message to a moderator

Re: SQL Loader - Unable to load data [message #616164 is a reply to message #616163]

Thu, 12 June 2014 14:11

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 03:04:03 CST 2024