problems while inserting data through scripts

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

Why don't you use SQL*loader?

Parijat,

Can you provide sample text of your insert statement where Carriage return is inserting...

I have tried insert these characters '



' into a table and its went fine. One more thing is check out whether the carriage return string is in within single quotes.

Thanks,

Shan.

The datatype of the field is varchar2(1024) and the data is inserted through java codes/J2EE applications. Infact this field stores the XML parameters in it and as such contains carriage returns in it. These carriage returns get created while entering the xml parameters in the text area of the web page which is used to enter data to this field. We can't enter these xml parameters all in one line (without carriage returns) while entering data through the web page as that wont be acceptable to the xml developers who will use the data later.

thanks

Parijat