ORA-01756: quoted string not properly terminated

Strings are enclosed in single quotes (‘) in Oracle Database. When you forget to add either of quotes, you get ORA-01756: quoted string not properly terminated error. So simply add the missing quote to resolve this error.

As per Oracle Message Guide,

ORA-01756: quoted string not properly terminated

Cause: A quoted string must be terminated with a single quote mark (‘).

Action: Insert the closing quote and retry the statement.

Let’s elaborate and learn more about this error in Oracle Database.

Reproduce ORA-01756 and solution

Create a custom table to hold information from the dba_objects and insert an additional record with object_name as ‘LET’S’.

SELECT object_name
     , object_type
  FROM dba_objects
 WHERE ROWNUM < 5

Below are the records in the table.

 

Example 1. Find out the record where object_name is I_CDEF2

Now, let’s write a select query to fetch information from the table where object name is I_CDEF2.

SELECT object_name
     , object_type
 FROM xxc.xxobject
WHERE object_name = 'I_CDEF2;

Output,

ERROR:
ORA-01756: quoted string not properly terminated

You got this error because a single quote is missing after the string I_CDEF2.

Let’s correct, put the missing quote at the end and run query again.

SELECT object_name
, object_type
FROM xxc.xxobject
WHERE object_name = 'I_CDEF2';

Example 2 : Find out a record where object_name is ‘LET’S’

SELECT object_name
, object_type
FROM xxc.xxobject
WHERE object_name = 'LET'S'

Output,

ERROR:
ORA-01756: quoted string not properly terminated

Now, the string is enclosed in quotes here, but still, you got the error. This happened because, the sting itself has quote as part of the data. You have to escape (‘) quotes to make this query work as shown below.

SELECT object_type
   , object_type
FROM xxc.xxobject
WHERE object_name = 'LET''S'

That’s it.

Summary

Although, it is basic error, still many beginner faces this when working with Oracle. We covered both the cases when you can encounter ora-01576 error and it’s solution.

I hope you find it useful. Please share.

Reference – Oracle Database Error Message