3 Replies Latest reply: May 4, 2011 3:32 AM by 858930 RSS

    sqlplus CRLF problem

    858930
      Hi,

      My story is i am importing data into Oracle11g via Insert scripts from SQLPLUS (@import.sql )

      Example of data inside import.sql

      INSERT INTO T_CIMS_TEMPLATE (ID,APPLICABLETODEALER,ATTACH1,ATTACH2,BIRTHTEMPLATE,ADDRESS.....Values(1,......)
      INSERT INTO T_CIMS_TEMPLATE (ID,APPLICABLETODEALER,ATTACH1,ATTACH2,BIRTHTEMPLATE,ADDRESS.....Values(2,......)
      INSERT INTO T_CIMS_TEMPLATE (ID,APPLICABLETODEALER,ATTACH1,ATTACH2,BIRTHTEMPLATE,ADDRESS.....Values(3,......)

      The problem now is some of the Data in T_CIMS_TEMPLATE has CRLF like in the ADDRESS column.

      Example of problematic data inside import.sql

      INSERT INTO T_CIMS_TEMPLATE (ID,APPLICABLETODEALER,ATTACH1,ATTACH2,BIRTHTEMPLATE,ADDRESS.....Values(4,......'NO 3 STREET ALPHA,
      DRAGON ROAD
      NEW YORK')

      and it seems sqlplus will prompt me an error and skip that insert line probably due to syntax error.

      Is there a way to solve this. as currently i run that script manually from 'SQL Developer 3.0' and it works. but it very tedious as i have tons of data with CRLF.

      Thank You very for your time and any feedback
        • 1. Re: sqlplus CRLF problem
          Frank Kulash
          Hi,

          Welcome to the forum!

          Whenever you have a problem, post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. For example:
          CREATE TABLE     t_cims_template
          (     id     NUMBER     PRIMARY KEY
          ,     address     VARCHAR2 (200)
          );
          
          
          INSERT INTO T_CIMS_TEMPLATE (ID, ADDRESS)
               Values              (1,  '1 Elm st.');
          
          INSERT INTO T_CIMS_TEMPLATE (ID, ADDRESS) 
               Values               (4,  'NO 3 STREET ALPHA,
          DRAGON ROAD
          NEW YORK');
          Both INSERT statements above work for me. What are you doing differently?

          Do you have lines that are completely empty, or contain nothing except whitespace. like this:
          INSERT INTO T_CIMS_TEMPLATE (ID, ADDRESS) 
               Values               (94, 'NO 3 STREET ALPHA,
          DRAGON ROAD
          
          NEW YORK');
          ? If so, you need to issue the SQL*Plus command
          SET     SQLBLANKLINES     ON
          to allow blank lines in the middle of a SQL statement. Like other SET commands, you only have to do this once; it stays in effect until you end the session, or explicitly re-set it.
          • 2. Re: sqlplus CRLF problem
            858930
            SET     SQLBLANKLINES     ON


            Yes you are right there is a whitespace. Sorry for the earlier confusion.

            Thank you very much & Cheers.
            • 3. Re: sqlplus CRLF problem
              858930
              oh crap i have another error

              sp2-0027 input is too long(>2499 characters)-line ignored

              guess i create a new thread