Could any one please let me know what is the exact use of "str '<EORD>\r\n'" statement in control file ?, while populating data from .dat file into database table.
When I am trying with this option, I am able to load a single record into a database table. If I remove this statement form control file, complete records from file get populated into table. I am using Linux server for imp data with sqlloader.
Also, Does there any difference to use this statement in control file on Linux server and on AIX server ?
Here is the structure of my ctl file.
into table CDSWEB.EZMAIL
fields terminated by '#<EOFD>#'
trailing nullcols (
The str keyword allows you to specify the end of line marker. In your case it's looking for the string <EORD> followed by a hidden carriage returns line feed the \n\r (the standard windows hidden end of line marker). If it works without this then I suggest that the format of the file has changed and the strong eord is no longer present and perhaps the file is now being transferred in binary mode which will mean the file ends up with just \n which is the standard unix format file and will work without being specified exactly.
Have you got a sample line of your current file that you are loading?
Thanks for suggestion, I tried with "str '\n'" instead of "str '<EORD>\r\n'" in control file and it worked properly.
But I am still confuse, Does it really a Linux issue ? as this doesn't work on Linux and the same work on AIX.
This is a sample record form .dat file which I am trying to load into database table.
100#<EOFD>#12#<EOFD>#0#<EOFD>##<EOFD>#2nd Year Term Letter#<EOFD>#80#<EOFD>#2000-01-05-05.00.00.000000#<EOFD>#1#<EOFD>#80#<EOFD>#IMAGE.dat.002.lob#<EOFD>#100#<EOFD>#1#<EOFD>#2nd Year Term Renewal Letter.JPG#<EOFD>#0#<EOFD>#image/pjpeg#<EOFD>##<EOFD>#1#<EOFD>#0#<EOFD>##<EOFD>#100#<EOFD>#100#<EOFD>##<EOFD>#<EORD>
Linux/unix use the same control characters so that shouldn't be a problem. Something must have changed the format of the file during its transfer from to the new server. Where does the source file come from and how did it get transferred to the 2 servers?
That makes no sense as scp from Linux to Linux shouldn't affect that. If you do an od -s on the file on the original box and then the new box (and the aix box). Do all the file appear exactly the same or have control characters been changed?
Od just dumps out every character ( hidden ot not) so it's an easy way to see exactly what is there.