Home » RDBMS Server » Server Utilities » SQL loader (oracle 10g)
SQL loader [message #531113] Sun, 13 November 2011 11:09 Go to next message
Genesys
Messages: 44
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

Re: SQL loader [message #531114 is a reply to message #531113] Sun, 13 November 2011 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 26656
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL loader [message #531117 is a reply to message #531113] Sun, 13 November 2011 11:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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.

Re: SQL loader [message #531119 is a reply to message #531117] Sun, 13 November 2011 11:47 Go to previous messageGo to next message
Genesys
Messages: 44
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
Re: SQL loader [message #531121 is a reply to message #531119] Sun, 13 November 2011 12:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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:
10
20
30
40


-- 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>

Re: SQL loader [message #531123 is a reply to message #531121] Sun, 13 November 2011 12:08 Go to previous messageGo to next message
Genesys
Messages: 44
Registered: August 2010
Member
still getting the error show in log file
  • Attachment: snt.log
    (Size: 2.36KB, Downloaded 637 times)
Re: SQL loader [message #531125 is a reply to message #531123] Sun, 13 November 2011 12:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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.


Re: SQL loader [message #531141 is a reply to message #531125] Mon, 14 November 2011 00:32 Go to previous messageGo to next message
Genesys
Messages: 44
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 232 times)
Re: SQL loader [message #531142 is a reply to message #531141] Mon, 14 November 2011 00:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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.
Re: SQL loader [message #531144 is a reply to message #531142] Mon, 14 November 2011 00:53 Go to previous messageGo to next message
Genesys
Messages: 44
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.
Re: SQL loader [message #531145 is a reply to message #531144] Mon, 14 November 2011 01:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
SQL*Loader is a utility for loading text files. It does not recognize other formats.

Re: SQL loader [message #531164 is a reply to message #531145] Mon, 14 November 2011 03:52 Go to previous message
cookiemonster
Messages: 13805
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.
Previous Topic: Can i use SQLLDR in Procedure..??
Next Topic: Slow exports using exp command due to open sessions
Goto Forum:
  


Current Time: Wed Feb 19 13:45:59 CST 2020