How To: Use semicolons within Oracle INSERT Statements
- Former user (Deleted)
Owned by Former user (Deleted)
Feb 27, 2020
1 min read
Loading data...
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.
Related articles
https://www.oreilly.com/library/view/oracle-sqlplus-the/0596007469/re96.html
Copyright © Datical 2012-2020 - Proprietary and Confidential