Hi,

The problem that I am having arises while inserting data from a script file to a particular table.

The script file contains data for nearly 100 tables and nearly consits of 100,000 records approx in total. The inserts from the script file work fine for all the 99 tables but fail for one particular table.After checking the data through a text editor it is found that the inserts are failing on those records which have special character 'enter' i.e (carriage return, in this case) as a part of data. When I check the data of the failing records through TOAD these special character(carriage return) is clearly seen as two parallel lines. Now while the insert scripts are running at SQL prompt the special character (carriage return) breaks the line and rest of the insert statement goes into the next lines (depending upon the number of carriage return present in that particular record's data). As a result oracle reports an error and considers them as invalid insert statement and ignores these records. The rest of the valid records are inserted properly.

Now, its' mandatory from application point of view to store those (carriage returns) as a part of data as we are storing the XML script in this database table. When we insert the same data through our application then thers's no problem neither there is a problem if we do an export/import.

This problem arises only while inserting data though a script. Pls mention how to get rid of the problem without removing the carriage returns from the data. We can't stick to import/export as different systems could have different characterset and can fail the imports.So we decided to stick to creation of data through scripts.

thanks

Parijat Paul