Insert All into

Question

Is there a difference between below with newline before select

INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)

SELECT * FROM dual;

and without new line before select

INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)
SELECT * FROM dual;

I notice the first one doesn't insert anything in table and appears in log as

D
-
X

While second block without newline appears in log as 3 rows created.

++LOGS

SQL> desc LOOKUPTABLE_TYPE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOOKUPTABLE_NAME                          NOT NULL VARCHAR2(32)
 COLUMN_NAME                               NOT NULL VARCHAR2(32)
 COLUMN_ORDER                              NOT NULL NUMBER(2)
 COLUMN_TYPE                               NOT NULL NUMBER(1)
 COLUMN_MODE                               NOT NULL NUMBER(1)

SQL> INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)

SELECT * FROM dual;  2    3    4    5  SQL> 

D
-
X

SQL> commit
  2  ;

Commit complete.

SQL> select * from LOOKUPTABLE_TYPE where LOOKUPTABLE_NAME='LT_EMERGENCY_NUMBER';

no rows selected

SQL> 
SQL> 
SQL> 
SQL> INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)
SELECT * FROM dual;  2    3    4    5  

3 rows created.

SQL> select * from LOOKUPTABLE_TYPE where LOOKUPTABLE_NAME='LT_EMERGENCY_NUMBER';

LOOKUPTABLE_NAME                 COLUMN_NAME                      COLUMN_ORDER
-------------------------------- -------------------------------- ------------
COLUMN_TYPE COLUMN_MODE
----------- -----------
LT_EMERGENCY_NUMBER              BNUMBER                                     2
          0           1

LT_EMERGENCY_NUMBER              Description                                 3
          9           0

LT_EMERGENCY_NUMBER              STREAM                                      1
          0           1

Answer 1

There is no difference. You can add as much whitespace as you want (up to the maximum size for an SQL statement) without affecting the meaning of the query.

CREATE TABLE lookuptable_type (
  a VARCHAR2(50),
  b VARCHAR2(50),
  c INT,
  d INT,
  e INT
);

Then:

INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)

SELECT * FROM dual;

Will give:

3 rows affected

and:

INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)
SELECT * FROM dual;

will also give:

3 rows affected

db<>fiddle here

---

Update

Why does SQL/Plus show this?

D
-
X

SQL/Plus's default behaviour is to not accept blank lines in an SQL statement and assumes that this will terminate the statement (SQL/Plus documentation and relevant dba.stackechange question). To change this behaviour to allow blank lines you need to include the directive:

set sqlblanklines on

When SQL/Plus parses the statement with the blank line it will split it into two statements:

INSERT ALL
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','STREAM',1,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','BNUMBER',2,0,1)
INTO LOOKUPTABLE_TYPE VALUES ('LT_EMERGENCY_NUMBER','Description',3,9,0)

and

SELECT * FROM dual;

The first is invalid syntax and the second is a valid statement and SQL/Plus will process it and display the output from the query.

If you enable blank lines then it will process the complete statement.