oracle ORA-01747: 无效的 user.table.column、table.column 或列规范
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9459204/
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-01747: invalid user.table.column, table.column, or column specification
提问by TonyP
Get the above error when the execute immediate is called in a loop
在循环中调用立即执行时得到上述错误
Update CustomersPriceGroups set 1AO00=:disc Where cuno=:cuno
Parameters: disc=66 cuno=000974
Update CustomersPriceGroups set 1AP00=:disc Where cuno=:cuno
Parameters: disc=70.5 cuno=000974
Update CustomersPriceGroups set 1AQ00=:disc Where cuno=:cuno
Parameters: disc=66 cuno=000974
Update CustomersPriceGroups set 1ZA00=:disc Where cuno=:cuno
Parameters: disc=60 cuno=000974
What does this mean ?
这是什么意思 ？
Here is the code fragment
这是代码片段
c:=PriceWorx.frcPriceListCustomers('020','221');
LOOP
fetch c into comno,cuno,nama,cpls;
exit when c%notfound;
dbms_output.put_Line(cuno);
g:=priceWorx.frcPriceListItemGroups('020','221');
d:=priceworx.frcCustomerDiscounts('020','221',cuno);
loop
fetch g into comno,cpgs,n;
fetch d into comno,cpls,cuno,cpgs,stdt,tdat,qanp,disc,src;
--dbms_output.put(chr(9)||cpgs);
sQ:='Update saap.CustomersPriceGroups set "'|| trim(cpgs)||'"=:disc '
|| ' Where cuno=:cuno';
execute immediate sQ using disc,cuno;
commit;
dbms_output.put_line( sQ );
dbms_output.put_line( chr(9)||'Parameters: disc='|| disc||' cuno='||cuno);
exit when g%notfound;
end loop;
close g;
close d;
end loop;
采纳答案by Alex Poole
Unquoted identifiers must begin with an alphabetic character (see rule 6 here). You're trying to assign a value to a column with a name starting with a number 1AO00, 1AP00etc.
未加引号的标识符必须以字母字符开头（请参阅此处的规则 6）。你试图将值分配给列一个名字开始与多家1AO00，1AP00等等。
Without seeing the table definition for CustomersPriceGroupswe don't know if it has columns with those names. If it does then they must have been created as quoted identifiers. If so you'll have to refer to them (everywhere) with quotes, which is not ideal - makes the code a bit harder to read, makes it easy to make a mistake like this, and can be hard to spot what's wrong. Even Oracle say, on the same page:
没有看到表定义，CustomersPriceGroups我们不知道它是否有具有这些名称的列。如果是，那么它们一定是作为带引号的标识符创建的。如果是这样，您将不得不用引号（到处）引用它们，这并不理想 - 使代码更难阅读，更容易犯这样的错误，并且很难发现问题所在。甚至甲骨文说，在同一页上：
Note: Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects.
注意：Oracle 不建议对数据库对象名称使用带引号的标识符。SQL*Plus 接受这些带引号的标识符，但在使用其他管理数据库对象的工具时，它们可能无效。
In you code you appear to be using quotes when you assign sQ, but the output you show doesn't; but it doesn't have the saap.schema identifier either. That may be because you're not running the version of the code you think, but might just have been
lost if you retyped the data instead of pasting it - you're not showing the earlier output of c.cunoeither. But it's also possible you have, say, the case of the column name wrong.
在您的代码中，您在分配时似乎使用了引号sQ，但您显示的输出没有；但它也没有saap.架构标识符。这可能是因为您没有运行您认为的代码版本，但如果您重新输入数据而不是粘贴数据，则可能只是丢失了 - 您没有显示c.cuno任何一个的早期输出。但也有可能你有列名错误的情况。
If the executeis throwing the error, you won't see the command being executed that time around the loop because the debug comes after it - you're seeing the successful values, not the one that's breaking. You need to check all the values being returned by the functions; I suspect that gis returning a value for cpgsthat actually isn't a valid column name.
如果execute抛出错误，您将不会在循环中看到当时正在执行的命令，因为调试是在它之后进行的 - 您看到的是成功的值，而不是破坏的值。您需要检查函数返回的所有值；我怀疑g返回的值cpgs实际上不是有效的列名。
As @ninesided says, showing more information, particularly the full exception message, will help identify what's wrong.
正如@nineside 所说，显示更多信息，尤其是完整的异常消息，将有助于确定问题所在。
回答by yurin
check your query for double comma.
检查您的查询是否有双逗号。
insert into TABLE_NAME (COLUMN1, COLUMN2,,COLUMN3) values(1,2,3);
(there is extra comma after COLUMN2).
（COLUMN2 后有额外的逗号）。
Update: recently (some people have special talents) i succeed to get same exception with new approach:
更新：最近（有些人有特殊才能）我成功地用新方法得到了同样的例外：
update TABLE_NAME set COLUMN1=7, set COLUMN2=8
(second SET is redundant)
（第二个 SET 是多余的）
回答by ninesided
It means that the Oracle parser thinks that one of your columns is not valid. This might be because you've incorrectly referenced a column, the column name is reserved word, or because you have a syntax error in the UPDATEstatement that makes Oracle think that something which is not a column, is a column. It would really help to see the full statement that is being executed, the definition of the CustomersPriceGroupstable and the full text of the exception being raised, as it will often tell whichcolumn is at fault.
这意味着 Oracle 解析器认为您的列之一无效。这可能是因为您错误地引用了列，列名是保留字，或者因为您在UPDATE语句中存在语法错误，使 Oracle 认为不是列的东西是列。查看正在执行的完整语句、CustomersPriceGroups表的定义和引发的异常的全文真的很有帮助，因为它通常会告诉哪个列有问题。
回答by Abhi Rampal
In addition to reasons cited in other answers here, you may also need to check that none of your table column names have a name which is considered a special/reserved word in oracle database.
除了此处其他答案中引用的原因之外，您可能还需要检查您的表列名中没有一个被认为是 oracle 数据库中的特殊/保留字的名称。
In my case I had a table column name uid. uid is a reserved word in oracle and therefore I was getting this error.
就我而言，我有一个表列名 uid。uid 是 oracle 中的保留字，因此我收到此错误。
Luckly, my table was a new table and I had no data in it. I was a able to use oracle DROP table command to delete the table and create a new one with a modified name for the problem column.
幸运的是，我的表是一个新表，里面没有数据。我能够使用 oracle DROP table 命令删除表并为问题列创建一个具有修改名称的新表。
I also had trouble with renaming the problem column as oracle wouldn't let me and kept throwing errors.
我在重命名问题列时也遇到了麻烦，因为 oracle 不允许我并不断抛出错误。
回答by Junchen Liu
if you add a extra "," at the end of the set statement instead of a syntax error, you will get ORA-01747, which is very very odd from Oracle e.g
如果你在 set 语句的末尾添加一个额外的“,”而不是语法错误，你会得到 ORA-01747，这在 Oracle 中很奇怪，例如
update table1
set col1 = 'Y', --this odd 1
where col2 = 123
and col3 = 456
回答by Jaikrat
And I was writing query like. I had to remove [and ]
我正在编写查询之类的。我不得不删除[和]
UPDATE SN.TableName
SET [EXPIRY_DATE] = systimestamp + INTERVAL '12' HOUR,
WHERE [USER_ID] ='12345'
We recently moved from SQL Server to Oracle.
我们最近从 SQL Server 迁移到 Oracle。
回答by Alireza Alallah
You used oracle keyword in your SQL statement
您在 SQL 语句中使用了 oracle 关键字
回答by Abhijit Patra
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-01747: 无效的 user.table.column、table.column 或列规范
You will get when you miss the column relation when you compare both column id your is will not be the same check both id in your database Here is the sample Example which I was facing:
当您比较两个列 id 时，如果您错过了列关系，您将得到不同的检查数据库中的两个 id 以下是我面临的示例示例：
UPDATE TABLE_NAME SET APPROVED_BY='1000',CHECK_CONDITION=ID, WHERE CONSUMER_ID='200'
here Issue you will get when 'CHECK_CONDITION' and 'ID' both column id will no same If both id will same this time your query will execute fine, Check id Id both Column you compare in your code.
这里当'CHECK_CONDITION' 和'ID' 两个列的id 不相同时，您会得到问题
回答by Upen
For me, the issue was due to use to column name "CLUSTER" which is a reserved word in Oracle. I was trying to insert into the column. Renaming the column fixed my issue.
对我来说，问题是由于使用列名“CLUSTER”，这是 Oracle 中的保留字。我试图插入到列中。重命名列解决了我的问题。
insert into table (JOB_NAME, VERSION, CLUSTER, REPO, CREATE_TS) VALUES ('abc', 169, 'abc.war', '1.3', 'test.com', 'test', '26-Aug-19 04.27.09.000000949 PM')
Error at Command Line : 1 Column : 83
Error report -
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
回答by Rafa?
The cause may also be when you group by a different set of columns than in select for example:
原因也可能是当您按与 select 中不同的一组列进行分组时，例如：
select tab.a, tab.b, count(*)
from ...
where...
group by tab.a, tab.c;
