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”:
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.
Donate to support our blogging work
3 thoughts on “sqlcl Multi-Line Paste Issue”
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:
You should be able to set this in the windows environment and run sql.bat to start sqlcl.
LikeLiked by 1 person
Thanks Rudkin for update. I tried setting the JAVA_OPTS in both sql.bat and windows environment, but for some reason it didn’t fix the paste issue.
Thanks for the pointing in the right direction. What worked for me was going into notepad++ under windows, then Edit -> EOL Conversion -> Window Format. This normalized the line endings.