The ORA-01756: quoted string not correctly terminated issue occurs when one of the surrounding single quotes in the character, string, or date value is missing. In Oracle, the string value, character value, and date value are all wrapped by a single quote mark. If any of the single quote marks is missing, the string will not be properly terminated. The error message ORA-01756: quoted string not correctly terminated will be shown.
If either the opening or closing single quotations are absent, or if any single quote is inserted inside the string, the error will occur. If a single quote is discovered, Oracle will consider it to be the beginning of a string and another single quotation to be the end of a string. Oracle will throw an error ORA-01756: quoted string not correctly terminated if the closing quote is not found.
When this ORA-01756 error occurs
You attempted to run a statement that included a string that was not enclosed by two single quotes. One of the quotes was entered without the second quote that accompanied it. Oracle will display an error message if the beginning or closing single quotes are missing, or if a single quote is inserted in between two strings.
insert into emp values (1, 'kim);
Error starting at line : 8 in command -
insert into emp values (1, 'kim);
Error at Command Line : 8 Column : 28
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"Root Cause
A string will be recognized by Oracle if it is enclosed by single quotes. The string should begin and end with a single quotation mark. Oracle could not end a string if one of the single quotes was missing. The error message ORA-01756: quoted string not properly terminated will be displayed if the string is not properly ended.
Solution 1
Oracle does not recognize strings that do not begin with a single quotation and end with a single quote. The end of a single quotation is regarded as the start of a string. Oracle will recognize a string with unexpected content. The absence of a single quotation results in an unidentifiable string.
Problem
insert into emp values (1, kim');
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"Solution
insert into emp values (1, 'kim');
1 row inserted.Solution 2
If the string begins with a single quotation and does not end with a single quote, Oracle does not recognize it as a string. Even if the string is terminated, the content will continue to be recognized. Oracle will examine the string with additional content until it reaches the next single quotation. As a consequence, unexpected content will be created.
Problem
insert into emp values (1, 'kim);
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"Solution
insert into emp values (1, 'kim');
1 row inserted.Solution 3
If a string has a single quotation in between, the single quote is treated as the string’s end. The string in between will be truncated as a result of this. If a string contains a single quotation, the quote must be escaped. The single quotation will be escaped by putting another single quote after it. Oracle will identify two single quotes in a string as a single quote in a string.
Problem
insert into test values (1, 'This's very good');
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"Solution
insert into test values (1, 'This''s very good');
1 row inserted.Solution 4
The error ORA-01756: quoted string not properly terminated occurs in select statements while identifying the string. If the string contains a missed quotes, the error will occur.
Problem 1
select * from emp where name = 'kim;
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"Solution
select * from emp where name = 'kim';Solution 5
This is an another example using the select statement.
Problem
select 'Emp id = ||id from emp;
ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"Solution
select 'Emp id = '||id from emp;