sqlcl Multi-Line Paste Issue

What is the issue?

I noticed that sometimes when I copy SQL statement from web and paste it on sqlcl , it is copied on many lines and generating errors.

For example, when I copied the following statement:

1

And pasted into sqlcl running on the windows command window, it was pasted as follows:

2

So, it was copied as three separate statements instead of one statement, which led to the errors shows above.

 

If you paste the same above statement on the sql*plus running on the same windows command window, it will accept it as one statement, like:

3

So, what is reason behind that?

After a lot of research and after discussing it with Jeff Smith and getting inputs from “TR Rudkin”:

http://www.thatjeffsmith.com/ask-a-question/comment-page-3/#comment-131653

I found the following interesting remarks:

If I compares the statement with the above issue with another statement that is working fine, I found that both are having a different end of lines special characters.

For the statement that is working without the above issue, the end of line is: “CRLF” which is Carriage Return + Line Feed (Hexa  codes: 0D0A).

4

For the statement that is having the above paste issue, the end of line is: “LF” which is Line Feed (Hexa  codes: 0A).

5

I guess the reason behind this issue may be the source of the statement. Some statements may be formatted with Unix end of line format while others are formatted for Widnows.

It worth mentioning that sqlplus absorbs this difference without issue, but sqlcl error out with lines that end up with LF only.

Also, it worth mentioning that if you just copy/paste the code that has this issue into a text editor like “Notepad++’, it will automatically adjust the end of lines to CRLF, then if you copied that code from Notepad++, it will work fine.

Advertisements

2 thoughts on “sqlcl Multi-Line Paste Issue

  1. So as I had expected it was the line terminations. While I don’t know the real source of the problem., read ‘who’s fault is it’. It is related to Java on windows and the windows clipboard. Your solution of pasting to an intermediate app and re-coping may be the easiest solution.

    you may want to try setting an option the sqlcl’s sql.bat references set:
    JAVA_OPTS=-Dsystem.line.separator=’\n’

    You should be able to set this in the windows environment and run sql.bat to start sqlcl.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s