OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL loader (oracle 10g)

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

SQL loader [message #531113]

Sun, 13 November 2011 11:09

Genesys
Messages: 45
Registered: August 2010

Member

Hi All,

SQL> desc teable2;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER

and control file:snt.ctl

LOAD DATA
INFILE 'c:\snt.csv'
INTO table teable2
fields terminated by "|"
TRAILING NULLCOLS
( col1 char(8000))

and snt.csv file
10
20
30
40

but while running the below command get error in log file
HOST sqlLDR hr/hr CONTROL=snt.ctl LOG=snt.log

able TEABLE2, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1 FIRST 8000 | CHARACTER

value used for ROWS parameter changed from 64 to 31
Record 1: Rejected - Error on table TEABLE2, column COL1.
ORA-01722: invalid number

Record 2: Rejected - Error on table TEABLE2, column COL1.
ORA-01722: invalid number

1) can i use the below code in my ctl file, which the CSV file having the only one column?
fields terminated by "|"

2)hopefully Invalid is error occur due to the datatype mismatch between the table coulmn and ctl file column.

can you people clarify on the same please

thanks
Genesys

Report message to a moderator

Re: SQL loader [message #531114 is a reply to message #531113]

Sun, 13 November 2011 11:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: SQL loader [message #531117 is a reply to message #531113]

Sun, 13 November 2011 11:34

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

What you posted works for me, so either you are using something different from what you posted or there is a difference between our systems and settings. Obviously you don't need 8000 characters for two-digit numbers. Please post a few rows of actual data. It is also best to use the actual data type, rather than relying on implicit conversions.

Report message to a moderator

Re: SQL loader [message #531119 is a reply to message #531117]

Sun, 13 November 2011 11:47

Genesys
Messages: 45
Registered: August 2010

Member

Hi,

thanks for the updates,

"It is also best to use the actual data type, rather than relying on implicit conversions means in ctl file have to use
(col1 number(12))?

Please provide any existing examples or new examples for this issue.

thanks
Genesys

Report message to a moderator

Re: SQL loader [message #531121 is a reply to message #531119]

Sun, 13 November 2011 12:01

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

The following example uses INTEGER EXTERNAL. The datatypes for SQL*Loader are different from the table datatypes. You can find them in the section on SQL*Loader of the Utilities Guide in the online documentation.

-- snt.csv:

-- snt.ctl:

LOAD DATA
INFILE snt.csv
INTO table teable2
fields terminated by "|"
TRAILING NULLCOLS
(col1 INTEGER EXTERNAL)

-- table, load, and results:

HR@orcl_11gR2> create table teable2
  2    (col1  number)
  3  /

Table created.

HR@orcl_11gR2> HOST sqlLDR hr/hr CONTROL=snt.ctl LOG=snt.log

HR@orcl_11gR2> select * from teable2
  2  /

      COL1
----------
        10
        20
        30
        40

4 rows selected.

HR@orcl_11gR2>

Report message to a moderator

Re: SQL loader [message #531123 is a reply to message #531121]

Sun, 13 November 2011 12:08

Genesys
Messages: 45
Registered: August 2010

Member

still getting the error show in log file

Attachment: snt.log
(Size: 2.36KB, Downloaded 2047 times)

Report message to a moderator

Re: SQL loader [message #531125 is a reply to message #531123]

Sun, 13 November 2011 12:14

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

Like I said before, what you posted would not produce that error. You need to post a few rows of actual data. Obviously you have some data that are not numbers or not being recognized as numbers for some reason. It could be something as simple as nls_numeric_characters parameter not matching. For example for one thousand two hundred thirty-four and five tenths, some countries use one of the below and some countries use the other.

1,234.5
1.234,5

If you want help, you need to post what you are actually using. Otherwise, you can expect to be ignored. We cannot diagnose what we cannot see.

Report message to a moderator

Re: SQL loader [message #531141 is a reply to message #531125]

Mon, 14 November 2011 00:32

Genesys
Messages: 45
Registered: August 2010

Member

Hi,

In December I have some work in production environment, have to use this sql loader for that I'am practicing the with simple data.

see my snt.csv file if anything is wrong in this file

Attachment: snt.csv
(Size: 13.50KB, Downloaded 1628 times)

Report message to a moderator

Re: SQL loader [message #531142 is a reply to message #531141]

Mon, 14 November 2011 00:36

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

What you have posted is not a text file. It appears to be an excel file. If so, that would be the problem. You need to export your excel file to a text file, before you can use SQL*Loader to load the data from the text file.

Report message to a moderator

Re: SQL loader [message #531144 is a reply to message #531142]

Mon, 14 November 2011 00:53

Genesys
Messages: 45
Registered: August 2010

Member

thats great now running sucesfully with out any issue.

thanks for the help

and if possible can you explain why we can't use the excel file for the same.

Report message to a moderator

Re: SQL loader [message #531145 is a reply to message #531144]

Mon, 14 November 2011 01:03

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

SQL*Loader is a utility for loading text files. It does not recognize other formats.

Report message to a moderator

Re: SQL loader [message #531164 is a reply to message #531145]

Mon, 14 November 2011 03:52

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

More to the point, your csv is not a csv. A proper csv can be opened with a text editor and read as plain text. What you've attached can not. It looks like you saved it as an xls and then changed the file extension manually. That doesn't work. If you save the file as a csv in the first place it should be fine.

Report message to a moderator

Previous Topic:	Can i use SQLLDR in Procedure..??
Next Topic:	Slow exports using exp command due to open sessions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 18:09:03 CST 2025