How To Allow Blank Lines In Your SQL Statements: SQLBLANKLINES

Today’s post will give you a quick and easy tip.

Did it ever happen to you to receive a script from a developer, that contains many blank lines within the SQL statements? Most likely the script was generated by a tool, and nobody reviewed the script to eliminate blank lines. Even more, the user or developer must have run the script in one of their tools, and the script did not throw any errors.

Now, here you are trying to run the script in SQL*Plus (on a side note, this is my favorite way to run scripts), and it is giving you errors. You look at the script, and notice all the empty lines in it.
Usually I would send the script back and ask to get the empty lines removed. However there is an easier way, which probably would work best for you, the developer, and your relationship with them!

And this is, to use the following SQL*Plus statement: SET SQLBLANKLINES ON

This statement has two options to turn ON or OFF blank lines. If blank lines are turned on, then you are allowed to use blank lines within the lines of your SQL statement.

By default the blank lines are OFF, meaning it is not allowed to have blank lines within a SQL statement. Below is an example on the usage

SQL>show SQLBLANKLINES 
sqlblanklines OFF

SQL>SET SQLBLANKLINES ON

SQL>select count(*) from dba_users
2
3 where username like 'D%';

  COUNT(*)
----------
        11

SQL>SET SQLBLANKLINES OFF

SQL>select count(*) from dba_users
2
3 where username like 'D%';

SP2-0734: unknown command beginning "where user..." - rest of line ignored.

You can also use a shorthand form of this command:

set SQLBL ON
show SQLBL

And that is the tip for today! Out of curiosity, did you use this before? Were you aware of this statement? I hope you learned something today!

Let me know in the comments! I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the November Special, only $13.99 CAD !