删除索引 ，然后插入语句，然后创建返回索引，在过程中不起作用

Question

create or replace PROCEDURE INSSEL_TBL_TEST AS
BEGIN
DECLARE
  TBL varchar2(50):= 'ESSCSHSTMTBPTEST2';
  tblNm varchar2(50);
CURSOR tableIndexList IS 
(select ES_INDEX_NM,ES_TBL_NM,ES_CRT_INDEX_STMT,ES_DROP_INDEX_STMT FROM ESSP.ESSINDEXCONF WHERE ES_TBL_NM = TBL);
BEGIN
  FOR drpInd in tableIndexList LOOP
    EXECUTE IMMEDIATE 'drpInd.ES_DROP_INDEX_STMT';
    COMMIT;
  END LOOP;
  execute immediate ('insert into ESSPREP.' ||UPPER(TBL)|| ' select * from ESSP.' ||UPPER(TBL)|| '@ESSPREPLINKESSP');
  FOR drpInd in tableIndexList Loop
    execute immediate 'drpInd.ES_CRT_INDEX_STMT';
    COMMIT;
  END LOOP;
END;
END;

以上是我在插入语句之前删除索引的过程，然后重新创建索引。但它不起作用，任何人都可以帮助？

Answer 1

行

EXECUTE IMMEDIATE 'drpInd.ES_DROP_INDEX_STMT';

是错误的。您正在尝试使用光标中的值，但实际上您使用的是字符串文字。要修复此错误，只需删除引号：

EXECUTE IMMEDIATE drpInd.ES_DROP_INDEX_STMT;

此外，正如@XING所说，提交在这里是多余的。

Answer 2

您的syntax不正确。见下文：

create or replace PROCEDURE INSSEL_TBL_TEST 
AS
  TBL varchar2(50):= 'ESSCSHSTMTBPTEST2';
  tblNm varchar2(50);
CURSOR tableIndexList IS 
(select ES_INDEX_NM,ES_TBL_NM,ES_CRT_INDEX_STMT,ES_DROP_INDEX_STMT FROM ESSP.ESSINDEXCONF WHERE ES_TBL_NM = TBL);
BEGIN
  FOR drpInd in tableIndexList LOOP
    EXECUTE IMMEDIATE drpInd.ES_DROP_INDEX_STMT;
    --COMMIT;-- DROP is a DDL hence no need for COMMIT
  END LOOP;
  execute immediate 'insert into ESSPREP.' ||UPPER(TBL)|| ' select * from ESSP.' ||UPPER(TBL)|| '@ESSPREPLINKESSP';
  FOR drpInd in tableIndexList Loop
    execute immediate drpInd.ES_CRT_INDEX_STMT;
  END LOOP;
END;