This discussion is archived
3 Replies Latest reply: May 4, 2011 1:32 AM by 858930 RSS

sqlplus CRLF problem

858930 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    oh crap i have another error

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

    guess i create a new thread

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points