ORA-01747: 无效的 user.table.column、table.column 或列规范 [英] ORA-01747: invalid user.table.column, table.column, or column specification

问题描述

在循环中调用立即执行时得到上述错误

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

这是什么意思?

这是代码片段

    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;

推荐答案

未加引号的标识符必须以字母字符开头(请参阅 此处的规则 6).您正在尝试为名称以数字开头的列分配值 1AO00、1AP00 等.

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, 1AP00 etc.

如果没有看到 CustomersPriceGroups 的表定义，我们不知道它是否有具有这些名称的列.如果是，那么它们必须被创建为带引号的标识符.如果是这样，您将不得不用引号(到处)引用它们，这并不理想 - 使代码更难阅读，更容易犯这样的错误，并且很难发现问题所在.甚至甲骨文说，在同一页上:

Without seeing the table definition for CustomersPriceGroups we 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:

注意:Oracle 不建议对数据库使用带引号的标识符对象名称.SQL*Plus 接受这些带引号的标识符，但是使用其他管理数据库的工具时，它们可能无效对象.

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.

在您的代码中，您在分配 sQ 时似乎使用了引号，但您显示的输出没有；但它也没有 saap. 架构标识符.那可能是因为您没有运行您认为的代码版本，但可能只是如果您重新输入数据而不是粘贴数据，则会丢失 - 您也没有显示 c.cuno 的早期输出.但也有可能是列名错误.

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.cuno either. But it's also possible you have, say, the case of the column name wrong.

如果 execute 抛出错误，您将不会在循环中看到当时正在执行的命令，因为调试在它之后 - 您看到的是成功的值，而不是那个这是打破.您需要检查函数返回的所有值；我怀疑 g 正在为 cpgs 返回一个实际上不是有效列名的值.

If the execute is 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 g is returning a value for cpgs that actually isn't a valid column name.

正如@nineside 所说，显示更多信息，尤其是完整的异常消息，将有助于确定问题所在.

As @ninesided says, showing more information, particularly the full exception message, will help identify what's wrong.

这篇关于ORA-01747: 无效的 user.table.column、table.column 或列规范的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！