ora 01756 quoted string not properly terminated

ORA 01756 QUOTED STRING NOT PROPERLY TERMINATED SOFTWARE
ORA 01756 QUOTED STRING NOT PROPERLY TERMINATED CODE

The source code for the examples in this article can be downloaded from here. SQL> DECLARE 2 v_sql VARCHAR2(1024) 3 v_cnt PLS_INTEGER 4 BEGIN 5 v_sql := q'' 6 EXECUTE IMMEDIATE v_sql INTO v_cnt 7 DBMS_OUTPUT.PUT_LINE( 8 TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.' 9 ) 10 END 11 /4 tables in USER_OBJECTS.PL/SQL procedure successfully completed. However, in the interest of demonstrating the quoting mechanism in as simple and short an example as possible, we'll stick with it. The sharp-witted amongst us will notice that this statement doesn't in fact need to be dynamic (and even if it did, it should be using bind variables). The following is a contrived example of a dynamic SQL statement that includes literal quotes in the string. Action Insert the closing quote and retry the statement. Unfortunately, word processors often use alternate characters for both single quotes (see the characters around AUS in your code above) and double quotes (e.g. Database Error Messages Updated JReleases 23c, 21c, 19c ORA-01756 quoted string not properly terminated Cause A quoted string must be terminated with a single quote mark (').

The quoting mechanism doesn't make these problems go away, but it at least makes dynamic SQL a little bit easier to transfer between SQL editors and packages during development. You apparently used a word processor, such as Microsoft Word, to prepare your code. While providing a flexible solution to many programming problems, dynamic SQL can also be difficult to build, debug and support. Many developers will be familiar with dynamic SQL. using the quoting mechanism in dynamic sql SQL> DECLARE 2 v VARCHAR2(1024) 3 BEGIN 4 v := q'' 5 DBMS_OUTPUT.PUT_LINE(v) 6 END 7 /It's a string with embedded quotes.PL/SQL procedure successfully completed. The following example demonstrates how simple it is to use this mechanism when building a string that contains single quotes.

ORA 01756 QUOTED STRING NOT PROPERLY TERMINATED SOFTWARE

If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer). Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. The syntax is q'', where the '' characters can be any of the following as long as they do not already appear in the string. The mechanism is invoked with a simple 'q' in PL/SQL only. This is particularly useful for building dynamic SQL statements that contain quoted literals. This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This short article introduces Oracle's new quoting mechanism in PL/SQL.

Ora 01756 quoted string not properly terminated