Did you get any Error, what is in default log file generated by sql loader
Could you please share the DB version/ OS , Your Control file. and some sample data/ table structure,
Check this link for Sql loader Exp :Oracle SQL*Loader Loader
Please use below steps, To insert data into table through SQL loader
1. Create table in your database
2.Then create test.txt file(the data that you want to load) into c drive
and save file with .txt extn
3. Create control file and save in the same location where you saved .txt file(means c drive)
into table test_n
fields terminated by ','
and save file with .ctl extn
4.Go to command promte and go to c drive and type below command to insert data into table
sqlldr schema_name/password@database_name CONTROL = control_file_name
* control_file_name will be your control file.
Hi..I got the issue
suppose you have a record like
and you have below table structurea nd control file
create table test_n
and control file like below
into table test_n
fields terminated by '<EOFD>EOFD>'
then sql loader read this value as 3 values one for id1 ,second for id2 and third for id3 and insert only one row. because you specified as fields terminated by <EOFD><EORD> when SQL loader see this will insert the new value into new column.
Hope you undserstand my point
I think I get your point...but what if my table contains just one column (msisdn) and the data file contains the list of records for that single column separated by <EOFD>>EORD>?
How does SQL loader understand that it has finished one row and is now reading a record from datafile for the second row?
Also, just noticed that my data file has a single space before every record:
233207332711<EOFD><EORD> 233208660745<EOFD>EORD> 233200767380
could this be an issue?
Thanks, this helped, inserted 5 records, after reading 64 rows
The log file shows:
Total logical records skipped: 0
Total logical records read: 64
Total logical records rejected: 51
Total logical records discarded: 0
And all records rejected were due to:
Rejected - Error on table T1, column MSISDN.
ORA-01722: invalid number
Continue_load can only be used with direct path, not conventional path. You need to specify the record terminator as well as the field terminator, which are the same when your data is only one column. Please see the example below. As to your numeric problem, as you can see, it does not reproduce below. Your problem could be due to many things. It could be trying to read something other than what you think it is trying to read, due to something else being wrong. It could be that you need to use:
(msisdn "to_number (ltrim (rtrim (:msisdn)))")
to removing leading and trailing spaces and convert it to a number. You need to provide a copy and paste of a complete example as I have done below.
-- contents of loading_data.dat:
233207332711<EOFD><EORD> 233208660745<EOFD><EORD> 233200767380<EOFD><EORD>
infile loading_data.dat "str '<EOFD><EORD>'"
into table T1
fields terminated by '<EOFD><EORD> '
SCOTT@orcl12c_11gR2> create table t1 (msisdn number)
SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=control_file_name.ctl log=test.log
SCOTT@orcl12c_11gR2> set numwidth 20
SCOTT@orcl12c_11gR2> select * from t1
3 rows selected.
Thanks, your answer was correct.
I had not specified the filed terminator and record terminator distinctly in my control file.
I modified my control file to the following and the entire record got loaded, the numeric problem did not arise! Thank you once again!
infile "data_file_name.dat" "str'<EOFD><EORD>'"
into table T1
fields terminated by '<EOFD><EORD> 'trailing nullcols