时间戳

首页 > 解决方案 > ORA-00933: SQL 语句未结束

plsql - ORA-00933: SQL 语句未结束

问题描述

没有看到任何明显的东西

Error at line 6
ORA-00933: SQL command not properly ended
ORA-06512: at line 16

DECLARE
 CURSOR PartTables IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM DBA_TAB_PARTITIONS
    where TABLE_owner in ('FDW','FDWSTG','FDW3NF')
    ORDER BY TABLE_NAME, PARTITION_POSITION;

    cur SYS_REFCURSOR;
    r INTEGER;
    highValue VARCHAR2(100);

BEGIN
   FOR aTab IN PartTables LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
      IF highValue NOT IN ('DEFAULT', 'MAXVALUE') THEN
         OPEN cur FOR 'SELECT ROWNUM FROM '||aTab.TABLE_NAME||' PARTITION ('||aTab.PARTITION_NAME||') WHERE ROWNUM <= 1';
         FETCH cur INTO r;
         IF cur%NOTFOUND THEN
         
            dbms_output.put_line ('Partition dropped');
    --        EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' DROP PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
         END IF;
         CLOSE cur;    
      END IF;
   END LOOP;
END;

标签： plsql

解决方案

我无权访问您的数据库，但我可以猜测问题所在。

我认为您的分区名称aTab.PARTITION_NAME可能不是有效的 SQL 标识符。它可能有符号：

SQL> select rownum from some_table partition(some-part?name) where rownum <= 1;
select rownum from some_table partition(some-part?name) where rownum <= 1
                                       *
ERROR at line 1:
ORA-00933: SQL command not properly ended

或者也许它只是一个数字：

SQL> select rownum from some_table partition(0) where rownum <= 1;
select rownum from some_table partition(0) where rownum <= 1
                                       *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SELECT如果分区名称是 SQL 关键字，例如，或者它为空，也会产生此错误。

如果除 null 以外的任何其他情况，您将需要双引号分区名称：

         --                                                                v------- add these -------v
         OPEN cur FOR 'SELECT ROWNUM FROM '||aTab.TABLE_NAME||' PARTITION ("'||aTab.PARTITION_NAME||'") WHERE ROWNUM <= 1';

推荐阅读