 Posted 6/15/2010 3:37:25 PM
|
|
|
|
| ORA-01756: | quoted string not properly terminated |
| ORA-01756: | quoted string not properly terminated | | Cause: | A quoted string must be terminated with a single quote mark (&apos . | | Action: | Insert the closing quote and retry the statement. |
|
|
|
Posted 6/15/2010 3:37:57 PM
|
|
|
|
Single quote within a string data type like CHAR, VARCHAR2, NVARCHAR etc. will end up during runtime with error
ORA-01756: quoted string not properly terminated
Cause of The Error:
--------------------------------
In Oracle, single quote is the string delimiter for all string data types. That is data type of CHAR, VARCHAR2 etc. If single quote is one of the characters inside the string, then it should be represented by two single quotes (''). The first single quote is an escape character which signals that a special character is following - here another single quote.
If one single quote is used, Oracle will interpret it as the ending string delimiter and fail with ORA-1756.
Example of The Error And Solution:
---------------------------------------------------
Within PL/SQL,
SQL> !vi my_proc.sql
create or replace procedure my_proc(var in varchar2)as
begin
dbms_output.put_line(var);
end;
/
SQL> @my_proc.sql
Procedure created.
SQL> exec my_proc('can't this be run');
ERROR:
ORA-01756: quoted string not properly terminated
SQL> exec my_proc('can''t this be run');
can't this be run
In first example it returned error and in second case it does not because to include single quote(') another single quote (') is used which is termed as escape character.
PL/SQL procedure successfully completed.
Another example with SQL.
SQL> select 'a'data' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select 'a''data' from dual;
'A''DA
------
a'data
|
|
|
Posted 6/15/2010 3:39:40 PM
|
|
|
|
please let me know the issue with following script (sql,oracle 10g)
1 DECLARE @colname AS NVARCHAR(50)
2 DECLARE @tablename AS NVARCHAR(500)
3 DEClARE @query AS NVARCHAR(500)
4 SET @colname = 'select wk_units1 from cnt_sls_dm.fct_sales_summary'
5 SET @tablename = 'SELECT tablename from dmi_user.fct_sales_meta'
6 set @query='select '+@colname+' FROM '+@tablename+'
7* EXECUTE sp_executesql @query
SQL> /
ERROR:
ORA-01756: quoted string not properly terminated
|
|
|
Posted 6/15/2010 3:39:56 PM
|
|
|
|
| This error is quite self-describing, you have an unterminated quote. You are trying to run an SQL Server code in Oracle. This won't work. You cannot just turn T-SQL into PL/SQL by mere copying. I corrected the syntax, but most probably you will need much more work than that. DECLARE
colname NVARCHAR2(50);
tname NVARCHAR2(500);
query NVARCHAR2(500);
BEGIN
SELECT wk_units1 INTO colname
FROM cnt_sls_dm.fct_sales_summary;
SELECT tablename INTO tname
FROM dmi_user.fct_sales_meta;
query := 'SELECT ' || colname || ' FROM ' || tname;
END;
|
|
|
Posted 6/15/2010 3:43:16 PM
|
|
|
|
You started a string with the single quote syntax, but forgot to close the string again with a single quote: SQL> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT; ERROR:ORA-01756: quoted string not properly terminated Make sure you close all strings, the single quotes need to be in pair: SQL> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT'; VALUE -------------------------------------------------------------------------------- DD-MON-RR Sometimes it happens that you have a single quote inside a string.
When this is the case, you need to escape it using another single quote: -- Unescaped raises an error SQL> select 'I'm a string' from dual; ERROR:ORA-01756: quoted string not properly terminated SQL> select 'I''m a string' from dual; 'I''MASTRING ------------ I'm a string Starting as of Oracle 9, you can use a different syntax: SQL> select q'{I'm a string}' from dual;Q'{I'MASTRIN------------ I'm a string SQL> select q'(I'm a string)' from dual; Q'(I'MASTRIN ------------ I'm a string SQL> select q'[I'm a string]' from dual; Q'[I'MASTRIN ------------ I'm a string SQL> select q'1I'm a string1' from dual; Q'1I'MASTRIN ------------ I'm a string
|
|
|
Posted 6/15/2010 3:48:23 PM
|
|
|
|
SQL> select q'( it's done )' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
|
|
|
Posted 6/15/2010 3:48:37 PM
|
|
|
|
What is your database version ? Q-quote operator introduced in Oracle 10g and see the example, you might be facing this error.
This is in SQL *PLUS V 9.2.0.1.0
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select q'( it's done )' from dual;
ERROR:ORA-01756: quoted string not properly terminated
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 11 12:58:12 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn hr/hrConnected.
SQL> select q'( it's done )' from dual;
Q'(IT'SDONE
-----------
it's done
|
|
|
Posted 6/15/2010 3:52:08 PM
|
|
|
|
Error: | ORA-01756: quoted string not properly terminated | Cause: | You tried to execute a statement that contained a string that was not surrounded by two single quotes. One of the quotes was entered without the second accompanying quote. | Action: | The options to resolve this Oracle error are: - Rewrite the statement so that the string is surrounded by two single quotes.
For example, if you had tried to execute the following SELECT statement: SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_name = 'IBM;
You would receive the following error message:
|
You can correct this SQL statement by surrounding the string (ie: IBM) with two single quotes as follows: SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_name = 'IBM';
|
|
|
|