PL/SQL执行报错ORA-00933等问题排查与解决求助

问题描述 #

编写了一段PL/SQL代码，用于创建关联分区与表的视图、重建指定表的不可用索引及分区索引，但执行时出现ORA-00933（SQL命令未正确结束）、ORA-00903（无效表名）等报错。已尝试双单引号转义单引号，问题仍未解决。

原代码 #

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name VARCHAR2(30) := 'TEST_TABLE';
  v_view_name VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
  BEGIN 
  -- Create a view to associate partitions with tables  
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || v_view_name || ' AS 
  SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name 
   FROM all_ind_partitions aip 
    JOIN all_tab_partitions ait ON aip.index_owner = ait.table_owner  AND aip.index_name = ait.table_name 
WHERE aip.status = ''UNUSABLE''';
-- Rebuild unusable indexes for the specified table  
FOR rec_index IN (SELECT
    owner,
    index_name
FROM
    all_indexes
WHERE
    table_name = ' || v_table_name || '
    AND status = ''UNUSABLE'')   
 LOOP    
EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';

 END LOOP;
-- Rebuild unusable partitions for the specified table using the view   
  FOR rec_partition IN (SELECT index_owner,index_name,partition_name
FROM
    ' || v_view_name || '
WHERE
    table_name = ' || v_table_name || ') 
    LOOP 
    DECLARE 
    v_sql VARCHAR2(1000);
   BEGIN v_sql := 'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name || ' REBUILD PARTITION ' || rec_partition.partition_name;
   EXECUTE IMMEDIATE v_sql USING v_table_name; -- Bind the variable
   EXCEPTION WHEN OTHERS THEN -- Handle exceptions, e.g., log or ignore
   NULL;
  END;
  END LOOP;
  END;

报错信息 #

Error report -
   ORA-06550: line 25, column 24:
   PL/SQL: ORA-00933: SQL command not properly ended
   ORA-06550: line 18, column 23:
   PL/SQL: SQL Statement ignored
   ORA-06550: line 36, column 9:
   PL/SQL: ORA-00903: invalid table name
   ORA-06550: line 31, column 27:
   PL/SQL: SQL Statement ignored
   06550. 00000 -  "line %s, column %s:
%s"
   *Cause:    Usually a PL/SQL compilation error.
   *Action:

错误原因分析 #

1. ORA-00933错误：第一个游标（rec_index）的静态SQL中错误使用了字符串拼接语法' || v_table_name || '。静态SQL不能直接拼接变量，需直接引用PL/SQL变量。
2. ORA-00903错误：第二个游标（rec_partition）的静态SQL中错误用字符串拼接视图名' || v_view_name || '，静态SQL里表/视图名无法通过这种方式动态指定，需改用动态游标。
3. 额外问题：重建分区索引的EXECUTE IMMEDIATE语句中，USING v_table_name是多余的，SQL字符串里无对应绑定变量占位符。

修正后的代码 #

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name VARCHAR2(30) := 'TEST_TABLE';
  v_view_name VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
  v_full_view_name VARCHAR2(61) := v_table_owner || '.' || v_view_name;
  TYPE rec_partition_type IS RECORD (
    index_owner VARCHAR2(30),
    index_name VARCHAR2(30),
    partition_name VARCHAR2(30)
  );
  cur_partition SYS_REFCURSOR;
  rec_partition rec_partition_type;
BEGIN 
  -- 创建关联分区与表的视图（指定所有者并过滤目标用户数据）
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || v_full_view_name || ' AS 
    SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name 
    FROM all_ind_partitions aip 
    JOIN all_tab_partitions ait 
      ON aip.index_owner = ait.table_owner 
      AND aip.index_name = ait.table_name 
    WHERE aip.status = ''UNUSABLE'' 
      AND ait.table_owner = ''' || v_table_owner || '''';

  -- 重建指定表的不可用普通索引
  FOR rec_index IN (
    SELECT owner, index_name
    FROM all_indexes
    WHERE table_owner = v_table_owner
      AND table_name = v_table_name
      AND status = 'UNUSABLE'
  ) LOOP    
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
  END LOOP;

  -- 用动态游标查询视图，重建指定表的不可用分区索引
  OPEN cur_partition FOR '
    SELECT index_owner, index_name, partition_name
    FROM ' || v_full_view_name || '
    WHERE table_name = :tab_name'
  USING v_table_name;

  LOOP
    FETCH cur_partition INTO rec_partition;
    EXIT WHEN cur_partition%NOTFOUND;

    DECLARE 
      v_sql VARCHAR2(1000);
    BEGIN 
      v_sql := 'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name || ' REBUILD PARTITION ' || rec_partition.partition_name;
      EXECUTE IMMEDIATE v_sql;
    EXCEPTION 
      WHEN OTHERS THEN 
        -- 可选：添加错误日志，如 DBMS_OUTPUT.PUT_LINE('重建失败: ' || SQLERRM);
        NULL;
    END;
  END LOOP;
  CLOSE cur_partition;
END;
/

关键修正点 #

• 静态游标优化：第一个游标直接引用v_table_owner和v_table_name变量，添加所有者过滤确保查询精确。
• 动态游标替代：第二个游标改用SYS_REFCURSOR动态查询视图，通过USING绑定变量传递表名，避免静态SQL的语法错误。
• 视图创建优化：指定完整的所有者+视图名，同时过滤目标用户数据，避免视图包含无关信息。
• 移除冗余绑定：删除重建分区索引语句中多余的USING v_table_name。

内容的提问来源于stack exchange，提问作者M_Gh