sqlloader problem

I would guess you are getting errors.  What is in the log file?

5 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  545 Rows not loaded because all fields were null.

but there is 1,2 mil rows. i don't undrestand
Record 1: Rejected - Error on table MASTER, column LCY_EXCH
ORA-01722: invalid number

the row is not pass look like some columns is not take it. lcy_exch har diffrent possition in diffrent rows.

when i select like that can i put the definition
select CONTRACT_REF_NO
||','||EVENT_SEQ_NO
||','||lcy_exch from master
can i say
select CONTRACT_REF_NO  varchar(2)
||','||EVENT_SEQ_NO number
...
..

>>can i say

No.  You cannot do that and you probably don't need to.

>>when i select like that can i put the definition

You can ensure formatting using specific functions like to_char and such but without knowing more about the actual data it is impossible to tell.

>>Record 1: Rejected - Error on table MASTER, column LCY_EXCH

You have a data issue.  Can you post some sample data and your table definition?

Remember this is a public website and you shouldn't post sensitive data.


Looks like you are using SQL to spool out data to be loaded into another database.  Are you sure all the data in the file is on the same line and in the correct format?

yes i using sql and select like that select a||','||b||','||c from table
is it another way to get data?
yes i can see all data and it's on one line but for ex. i have 20 columns but some rows is 20 and some rows is less ( 19, 18).
it's look like it don't take all coulmns from each row.

i am using this one for my spool
set echo off feedback off heading off
set linesize 3000 pagesize 0 newpage 0 space 0
set trimspool on

>>it's look like it don't take all coulmns from each row.

Really not possible.  If you use string concatentation like you say, if a column doesn't have a value, you get ,,

>>i have 20 columns but some rows is 20 and some rows is less ( 19, 18).

If you have 20 columns then you have 19 comma's guaranteed.

The only think that can mess you up is if some of the columns can have a comma in it.

Then you get into the clause in your control file:  OPTIONALLY ENCLOSED BY '"'  

Then you need to wrap your columns like:
select '"' || a||'","'||b||'","'||c || '"' from table

You still potentially have data issues if the data can contain double quotes and commas...

agree. is there another way to take data?

There are many ways.  It all depends on your exact requirements.

If you are moving data from one Oracle database to another, I would probably use export/import.

The more information you can provide us will keep us from vague concepts.

At this point it is more of a side note, but the reason that SQL*Loader stopped loading is because you have 51 errors.  The maximum number of errors allowed (by default) is 50.  You can change this with the ERRORS parameter, but you obviously have some data issues that you need to solve.

The LINESIZE setting that you are using seems pretty small.  I would use the maximum allowed for your OS.  If you don't know what that is, I would suggest trying to set it to some very large number and you will get an error message that tells you what the maximum is and use that.  This is a total guess as you are not supplying sample data for us to look at.

i know. problem is some columns  has more lines (i mean it has 2 or more line) and under sqlloader select, i cant not select it
it make new lines, and wrap the select statment.

Sorry but I do not understand that last post.

Are you saying some of the data in a column is wrapping to another line?

If so, that shouldn't be possible if you concatenated all the columns together.  It is very possible if you used the COLSEP trick posted around the Internet.

We really need to see some sample data that is causing the problem.

Yes it'd what i try to say. some columns is wrapped to next line.
it's look like that
i have a row like id, name, surname, address.
1, hil, nilson Olsen, 'som address'
2, kim, nilsonOlsen, somAdress'
...
the point is nilsonOlsen and Som adress in secound row is wrote some 2 lines in application GUI.
it's really look lik:
kim nilson, som address
       Olsen  

when i select from sqlplus the second line is not selected and because it wrap to next line. then next line started with some which is not correct.

Can you post the exact steps that you did to extract the data?  Also, post some actual sample data.  You can change every character to X if you want, just trying to see what you are seeing.  Also, post the structure of the table (column names, data types and lengths).

Also, are you copying it from the screen or using the spool command.

Another question would be are you trying to load it into the same database or a different database.  The reason I would ask that is that you may not need to use SQL*Loader at all.

ok what you suggest if not sqlloader. i can not use exp/imp because of the oracle version. (11  to 12 )

If it is different versions, then it isn't the same database.

Why can't you use exp/imp?  It certainly works just fine across versions.

You could also set up a database link between the 2 databases and just do an insert from one to the other.