DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001

    problems while inserting data through scripts


    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.


    Parijat Paul

  2. #2
    Join Date
    Aug 2001
    Waterloo, On
    Why don't you use SQL*loader?

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i

    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Feb 2002

    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.


  4. #4
    Join Date
    Dec 2001

    paul's reply


    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.