How To: Use semicolons within Oracle INSERT Statements

If a user attempts to include a semicolon within an Oracle INSERT statement, SQL Plus may throw the following error: ORA-01756: quoted string not properly terminated

Sample Script with Error

INSERT INTO CAPS.DEVICE58 VALUES (13, 'test 
semicolon test ;
','DATICAL_USER','27-FEB-20','DATICAL_USER','27-FEB-20');

INSERT INTO CAPS.DEVICE58 VALUES (14, 'test 
without semicolon
','DATICAL_USER','27-FEB-20','DATICAL_USER','27-FEB-20');

INSERT INTO CAPS.DEVICE58 VALUES (15, 'another test 
with semicolon ;
','DATICAL_USER','27-FEB-20','DATICAL_USER','27-FEB-20');

Error Output

The last command output was:
SQL> 
SQL> 
SQL> SET SQLBLANKLINES ON
SQL> 
SQL> INSERT INTO CAPS.DEVICE58 VALUES (13, 'test
  2  semicolon test ;
ERROR:
ORA-01756: quoted string not properly terminated

Instructions

For any INSERT statements that include semicolons, wrap the statements with the following:

set sqlterminator "@" (at the beginning of the section)
set sqlterminator ";" (at the end of the section)
Also change the line terminators within those sections to be @ instead of ;

Corrected Sample Script

set sqlterminator "@"
INSERT INTO CAPS.DEVICE58 VALUES (13, 'test 
semicolon test ;
','DATICAL_USER','27-FEB-20','DATICAL_USER','27-FEB-20')@
set sqlterminator ";"

INSERT INTO CAPS.DEVICE58 VALUES (14, 'test 
without semicolon
','DATICAL_USER','27-FEB-20','DATICAL_USER','27-FEB-20');

set sqlterminator "@"
INSERT INTO CAPS.DEVICE58 VALUES (15, 'another test 
with semicolon ;
','DATICAL_USER','27-FEB-20','DATICAL_USER','27-FEB-20')@
set sqlterminator ";"

Be sure to set the set sqlterminator ";" at the end of the sections as well.

https://www.oreilly.com/library/view/oracle-sqlplus-the/0596007469/re96.html

Sample Script with Error

Error Output

Instructions

Corrected Sample Script

Related articles