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:

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

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:

So, what is reason behind that?

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

Ask A Question

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).

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

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.

Thanks
Ahmed