Geek Answers Handbook

Why does ORACLE not allow consecutive newlines in commands?

Writing:

  • : CREATE TABLE Person (
  • : name CHAR (10),
  • : ssn INTEGER);

and save it in the "a.sql" file (the colon represents the beginning of the line, not in the actual code.)

If I run it by typing "@a" at 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 am collecting, it seems that sqlplus terminates the command if it encounters several newlines in a line. Is this an exact statement? If so, does anyone know if this is necessary / why does he decide to do this?

+6
sql oracle language-design whitespace sqlplus
source share
3 answers

I don’t know why, but a complete blank line completes the command in SQL * Plus.

Quote from SQL * Plus docs :

Completing an SQL Command : You can complete an SQL command in one of three ways:

  • with semicolon (;)
  • with a slash (/) on a separate line
  • with an empty string

You can also change how empty rows are processed with SET SQLBLANKLINES

SQLBL [ANKLINES] {ON | OFF}

Whether SQL * Plus controls empty strings in a SQL command or script. ON interprets blank lines and new lines as part of an SQL command or script. OFF, the default value, does not allow empty lines or new lines in an SQL command or script or script.

Enter BLOCKTERMINATOR to stop writing the SQL command without running the SQL command. Enter the SQLTERMINATOR character to stop writing the SQL command and start the SQL statement.

+15
source share

By default, SQLPlus completes (but does not execute) the statement when an empty string is entered. It has always been done. This was probably a good idea in the days before screen editors and query tools.

You can change this default behavior with

set SQLBLANKLINES to

In this case, you will need to enter a line with a full stop to complete (but not execute) the instruction.

+5
source share

But if you want to insert multi-line text in the varchar2 or clob field, you can 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 the reasons described above.

0
source share

More articles:


All Articles