CODEWITHSUNDEEP
sqloracle-databaselanguage-designwhitespacesqlplus
Matt Boehm
Matt Boehm

Reputation: 1912

Why doesn't ORACLE allow consecutive newline characters in commands?

I write:

CREATE TABLE Person ( 
name CHAR(10),

ssn INTEGER);

and save it to a file "a.sql".

If I then run it by typing "@a" in the SQL*Plus command prompt, it will tell me that the line starting with "ssn" is not recognized as a command, and is ignored.

From what I gather, it seems that sqlplus terminates a command if it encounters multiple newline characters in a row. Is this an accurate statement? If so, does anyone know if this is necessary/ why it chooses to do this?

Upvotes: 8

Views: 12662

Answers (3)

Abhijit Singh
Abhijit Singh

Reputation: 1

But if you are wanting to insert multiline text in a varchar2 or a clob field, you may use chr(10)

 insert into t values ('Hello,'||chr(10)||chr(10)||' How are you?');

 insert into t values (
 'Hello,

 How are you');

will not work for reasons explained above.

Upvotes: 0

Thilo
Thilo

Reputation: 262714

I don't know about the why, but a completely blank line terminates a command in SQL*Plus.

Quote from the SQL*Plus docs :

Ending a SQL Command: You can end a SQL command in one of three ways:

  • with a semicolon (;)
  • with a slash (/) on a line by itself
  • with a blank line

You can also change how blank lines are treated with SET SQLBLANKLINES

SQLBL[ANKLINES] {ON|OFF}

Controls whether SQL*Plus allows blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.

Upvotes: 18

Gary Myers
Gary Myers

Reputation: 35401

By default, SQLPlus does terminate (but not execute) a statement when a blank line is entered. It has always done this. It probably seemed like a good idea in the days before screen editors and query tools.

You can change that default behaviour with

set SQLBLANKLINES on

In which case you'd have to enter a line with just a full stop to terminate (but not execute) a statement.

Upvotes: 5

Related Questions