SET SQLBLANKLINES: How to Allow Blank Lines in SQLcl & SQL*Plus

If you’re trying to run a multi-line query in SQLcl or SQL*Plus, and you keep getting an error such as “Unknown Command”, but running it in SQL Developer causes no such error, maybe this post will help.

By default, SQLcl and SQL*Plus don’t allow blank lines in SQL statements. However, you can change this with the SET SQLBLANKLINES command.

Syntax

The syntax goes like this:

SET SQLBL[ANKLINES] {ON | OFF}

This means you can use either the full SQLBLANKLINES or its shorthand form SQLBL, and you can set it to either ON or OFF.

It’s OFF by default. Setting it to ON will enable you to include blank lines in your code.

Example

First, I’ll check my current setting:

SHOW SQLBLANKLINES

Result:

sqlblanklines OFF

Currently, support for blank lines is disabled.

Suppose we have the following SQL statement:

SELECT 3 * 10 

FROM DUAL;

Here’s what happens if I copy and paste that into SQLcl and try to execute it:

SQL> SELECT 3 * 10 
  2  
  3* FROM DUAL;

Error starting at line : 1 in command -
SELECT 3 * 10
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:

Error starting at line : 1 in command -
FROM DUAL
Error report -
Unknown Command

The statement fails, due to the blank line.

Set SQLBLANKLINES to ON

Now let’s set SQLBLANKLINES to ON:

SET SQLBLANKLINES ON

And run the query again:

SELECT 3 * 10 

FROM DUAL;

Now here’s what I get:

SQL> SELECT 3 * 10 
  2  
  3* FROM DUAL;

   3*10 
_______ 
     30

This time the statement succeeds.

Shorthand Form

You can alternatively use the shorthand form SQLBL.

Example of returning the current setting:

SHOW SQLBL

Result:

sqlblanklines ON

Example of switching it off, and showing it again:

SET SQLBL OFF
SHOW SQLBL

Result:

sqlblanklines OFF