SQL ORA-01756: 带引号的字符串未正确终止
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1367295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01756: quoted string not properly terminated
提问by Irveen
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
回答by Quassnoi
This error is quite self-describing, you have an unterminated quote.
这个错误是不言自明的，你有一个未终止的引用。
You are trying to run an SQL Servercode in Oracle. This won't work.
您正在尝试SQL Server在Oracle. 这行不通。
You cannot just turn T-SQLinto PL/SQLby mere copying.
你不能只是把T-SQL成PL/SQL由单纯的复制。
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;
回答by RedFilter
Change line 6 to
将第 6 行更改为
set @query='select '+@colname+' FROM '+@tablename
回答by Romain Linsolas
The 6th line seems not correct in your example:
在您的示例中，第 6 行似乎不正确：
set @query='select '+@colname+' FROM '+@tablename+'
You finish the line with a '. Either you remove the +', either you finish your request with a wherestatement...
你用一个'. 要么删除+'，要么用where语句完成请求...
回答by Jon Spokes
This looks a lot like tSql rather than pl SQl, You might want to use || to concatenate strings in Oracle and varchar2 instead of nvarchar
这看起来很像 tSql 而不是 pl SQl，您可能想要使用 || 连接 Oracle 和 varchar2 中的字符串而不是 nvarchar
回答by Irveen
following is the correct way to answer the question........ running fine.......... thanks to all who helped......
以下是回答问题的正确方法......运行良好......感谢所有帮助......
-Irveen
-欧文
DECLARE
type fct_sales_summary_cur is table of dmi_user.fct_sales_summary_cmp_1%rowtype index by binary_integer;
cur_rec fct_sales_summary_cur;
colname NVARCHAR2(50);
tname NVARCHAR2(500);
query VARCHAR2(500);
cnt number:=1;
BEGIN
loop
SELECT colname
INTO colname
FROM dmi_user.FCT_SALES_META
where sno=cnt;
SELECT tablename
INTO tname
FROM dmi_user.fct_sales_meta
WHERE sno=cnt;
--query:='select * from dmi_user.fct_sales_summary_cmp';
query := 'SELECT '|| colname ||' FROM '||tname;
-- dbms_output.put_line(colname);
-- dbms_output.put_line(tname);
--dbms_output.put_line(query);
execute immediate query bulk collect into cur_rec;
--dbms_output.put_line(cur_rec);
dbms_output.put_line('------Table-Sno -----' || cnt);
for i in cur_rec.first..cur_rec.last loop
dbms_output.put_line(cur_rec(i).wk_units1);
end loop;
cnt:=cnt+1;
exit when cnt=4;
end loop;
END;
/
