ORA-00933: SQL语句未结束

Question

看不到任何明显的东西

错误在第6行ORA-00933: SQL命令未正确结束ORA-06512:在第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;

Answer 1

我不能访问你的数据库，但我可以猜出问题。

我认为您的分区名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

如果分区名称是SQL关键字(如SELECT )，或者是null，也可以生成此错误。

如果是null以外的任何情况，则需要双引用分区名称：

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