hi,

The Problem:
Data import scripts in oracle 8i for a table containing line breaks/enter character as a part of its data is failing during execution of the script.

Problem Description:
This table has a field called xmlparameters varchar2(4000) and stores the formatted xml tags and values in it. The data to this field is entered through html pages/GUI and while formatting the tags through the GUI/textarea the user puts lots of "enter" to break the lines and align them in the textarea of the GUI.The data however gets saved properly in the above mentioned table. Now while running the oracle script from sql prompt to import data from this table to another table the records which contain the line break\enter character in its field fails and are neglected by the insert statement.So even the script completes successfully but it fails to insert all the data.It only inserts records which don't have the linebreakls in them.
I've checked the records which are failing in the script and found that because of the enter character\line break in the data for the given field , while constructing the insert statement oracle is breaking the line and writing the rest of the insert statement into a new line. So when the script is run oracle reports an error for these records as the line break causes the syntax error.

pls advice me about how to solve this problem while running the scripts.Its very very urgent for me.

thanks a lot

Parijat Paul