Sqlplus and empty lines

At some point you've probably hit the issue that sqlplus does not like commands with blank lines in them - there is an easy fix for it though and it seems there is also something inbuilt that can remove them for you...(and its been around forever as far as i know....)

So for example i have this bit of SQL

select sysdate

from dual;

If i type it line by line it just fails and returns to the prompt after the second line

However if i paste it in i get slightly different errors


SQL>
select sysdate

from dual;SQL>   2  SQL>
SP2-0042: unknown command "from dual" - rest of line ignored.






What the useful feature is is the command below

SQL> set sqlblanklines on



Once this is set sqlplus happily accepts empty lines even when typing in the command manually

SQL>
select sysdate

from dual;SQL>   2    3

SYSDATE
---------
14-APR-15



What's also useful is if you now ed this file (which opens vi in my case) and then save and exit when you return to sqlplus you'll see all the blank lines have gone!


SQL> ed

Wrote file afiedt.buf

  1  select sysdate
  2* from dual
  3  /

SYSDATE
---------
14-APR-15

SQL>

A useful trick - especially in cases where you've been sent a long statement and dont want to mess around removing the empty lines