Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Posts
    10

    Unanswered: New line in update with sqlplus file

    I'm trying to load in a file where some of the fields have newlines. I want to keep these new lines in there, but I get the following error for my update statements:

    SQL> @540.sql
    SP2-0734: unknown command beginning "import Sys..." - rest of line ignored.

    the statements are along the lines of:

    update table set field = 'content
    that has
    multiple lines'
    where whatever = 1;

    I searched but everything seems to be about replacing the new lines in the text. I would rather not mess with that. Individual insert/update statements work fine. In fact I have some inserts with multiline data that work just fine. It's only the updates in the file that fail.

    Thanks,
    Steve

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I suspect you have two newlines in a row. SQLPlus hates that, and assumes you are rewriting the previous statement.

  3. #3
    Join Date
    Mar 2008
    Posts
    10
    It's windows, and was default \r\n. I changed them to just be \n, but I still got the same error. Though when I think about it, like I said the inserts work, but the updates. SQLPlus treats them different?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Unless & until I see evidence to the contrary, I agree with MCrowley.

    Use CUT & PASTE along with <code tags> showing an UPDATE that fails.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2008
    Posts
    10
    The text in question contains semi-colons. It looks like the parser assumes that a line ending with a semi-colon is terminated, rather than looking for an end quote. I guess for inserts they look for ); instead.

    update table set field = 'line1;line2;
    line3';

    That statement will fail. I tried adding a space at the end, but it still terminates it. The only way I could make it not terminate was to add non-whitespace after it. I need the data to contain semi-colons, so what can I do?

    And I take back what I said about individual update statements working. That was in EMS Oracle Manager. It still fails in sqlplus.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >so what can I do?
    I'd solve this problem using PERL, but I realize that PERL is only for hard core coders.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2008
    Posts
    10
    Well I have to give the scripts to the dbas as straight sql, so I can't use a scripting language to do the work. Or are you suggesting to use perl in some other fashion(some type of string replacement)?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why don't you do something like the following.

    Code:
    update table set field = 'content'||chr(10)||'that has'||chr(10)||'multiple  lines'
    where whatever = 1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Mar 2008
    Posts
    10
    I was trying to avoid that if possible since it makes the content of the update practically impossible to read. Is it possible that there could be some way to do it by setting various sqlplus options? I tried changing sqlt, and cmds to something other than ;, but it didn't work. Though I may have done it wrong.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by sjv
    I was trying to avoid that if possible since it makes the content of the update practically impossible to read. Is it possible that there could be some way to do it by setting various sqlplus options? I tried changing sqlt, and cmds to something other than ;, but it didn't work. Though I may have done it wrong.
    Not true, it will do exactly what you wanted. It will insert line feeds into the varchar2 column that you are updating.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Mar 2008
    Posts
    10
    no, no. I don't mean the data in the db will be difficult to read. I know that it will be fine in there. I'm talking about in the actual script file, it makes it difficult to read.

  12. #12
    Join Date
    Mar 2008
    Location
    Seattle
    Posts
    11
    Yeah... it is tough to read, but I can't think of another good way to do what you're trying. Maybe you could just have a hefty comment above explaining what it all means.

  13. #13
    Join Date
    Mar 2008
    Posts
    10
    Oh well, I guess that's how it is then. Thanks for the help guys.

Posting Permissions

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