在应该跨分区运行的此Oracle SQL Update查询中，我在做什么错? [英] What am I doing wrong in this Oracle SQL Update query that's supposed run across partitions?

问题描述

这是我要运行的查询(由同事提供)

Here's the query I'm trying to run(supplied by a colleague)

FOR partition IN 1..32 LOOP
    lQuery := 'UPDATE CORE.tbl PARTITION(tbl' || LPAD(partition, 2, '0') '|| ') SET p1 = NULL '
           || 'WHERE p1 IS NOT NULL';

    EXECUTE IMMEDIATE lQuery;
END LOOP;

其中，CORE是数据库，而tbl是表的名称.但是，当我尝试运行脚本时，Sql开发人员会抛出很多错误.

where CORE is the db and tbl is the name of the table. However, Sql developer throws a bunch of errors when I try to run the script.

Error starting at line 1 in command:
FOR partition IN 1..32 LOOP
Error report:
Unknown Command

Error starting at line 2 in command:
lQuery := 'UPDATE CORE.user_login PARTITION(user_login' || LPAD(partition, 2, '0') '|| ') SET password_md5 = NULL '
Error report:
Unknown Command

Error starting at line 3 in command:
|| 'WHERE password_md5 IS NOT NULL'
Error report:
Unknown Command

Error starting at line 5 in command:
EXECUTE IMMEDIATE lQuery
Error report:
ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "LQUERY" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "LQUERY" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Error starting at line 6 in command:
END LOOP
Error report:
Unknown Command

除SQL中的简单脚本外，我从未使用过其他任何东西，而且似乎无法弄清查询中断的原因.解决问题的任何帮助将不胜感激

I have never worked with anything more than simple scripts in SQL and can't seem to figure out why the query is breaking. Any help on solving the issue would be appreciated

推荐答案

我认为根本的问题是，您需要将该代码包装在BEGIN..END中，以使其成为单个有效命令.

I think the fundamental problem is that you need to wrap that code in BEGIN..END for it to be a single valid command.

正如另一个答案中指出的那样，您在代码中也存在语法错误.

As pointed out in another answer, you also have a syntax error within the code.

DECLARE
  lQuery VARCHAR2(1000);
BEGIN
  FOR partition IN 1..32 LOOP
    lQuery := 'UPDATE CORE.tbl PARTITION(tbl' || LPAD(partition, 2, '0') || ') SET p1 = NULL '
           || 'WHERE p1 IS NOT NULL';

    EXECUTE IMMEDIATE lQuery;
  END LOOP;
END;

这篇关于在应该跨分区运行的此Oracle SQL Update查询中，我在做什么错?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！