ORA-06550:必须声明标识符

Question

我有PL/SQL过程代码，当我尝试运行它失败的时候，我尝试用"create or replace Procedure create_index“替换它--仍然是错误的。

ORA-06550: 3第2行，第3列: ORA-06550: 3第4行，第8列:标识符' idxts‘必须声明第5行，第3列: ERROR at line: INTO idxts ORA-06550: 3第7行，第3列:标识符'CREATE_INDEX’必须声明第7行，第3列：

DECLARE
  idxts VARCHAR2(100);

  PROCEDURE create_index(idx VARCHAR2, def VARCHAR2) IS
    fake NUMBER(1);
    BEGIN
      SELECT 1 INTO fake FROM user_indexes WHERE index_name = idx;
      EXCEPTION
        WHEN no_data_found THEN
          EXECUTE IMMEDIATE 'create index ' || idx || ' on ' || def || ' tablespace ' || idxts;
    END;

BEGIN
  SELECT
    nvl(min(value), 'NC_INDEXES')
  INTO idxts
  FROM nc_directory
  WHERE key = 'NC.TABLESPACE.INDEXES';
  create_index('IDX_QRTZ_T_ST_NFT', 'qrtz_triggers (trigger_state, next_fire_time)');
END;
/

BEGIN
  SELECT
    nvl(min(value), 'NC_INDEXES')
  INTO idxts
  FROM nc_directory
  WHERE key = 'NC.TABLESPACE.INDEXES';
  create_index('QRTZ_JOB_LISTENERSJOB_NAMEFK', 'qrtz_job_listeners (job_name)');
END;
/

BEGIN
  SELECT
    nvl(min(value), 'NC_INDEXES')
  INTO idxts
  FROM nc_directory
  WHERE key = 'NC.TABLESPACE.INDEXES';
  create_index('QRTZ_TRIGGERSJOB_NAMEFK', 'qrtz_triggers (job_name)');
END;
/

BEGIN
  SELECT
    nvl(min(value), 'NC_INDEXES')
  INTO idxts
  FROM nc_directory
  WHERE key = 'NC.TABLESPACE.INDEXES';
  create_index('QRTZ_TRIGGER_LISTENERSTRIGGEFK', 'qrtz_trigger_listeners (trigger_name, trigger_group)');
END;
/

Answer 1

由于您已经有了现有代码，因此过程create_index将只在第一个PL/SQL块中可用。尝试将所有对create_index的调用组合到单个PL/SQL块中，如下所示。

DECLARE
    idxts   VARCHAR2 (100);

    PROCEDURE create_index (idx VARCHAR2, def VARCHAR2)
    IS
        fake   NUMBER (1);
    BEGIN
        SELECT 1
          INTO fake
          FROM user_indexes
         WHERE index_name = idx;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            EXECUTE IMMEDIATE 'create index ' || idx || ' on ' || def || ' tablespace ' || idxts;
    END;
BEGIN
    SELECT NVL (MIN (VALUE), 'NC_INDEXES')
      INTO idxts
      FROM nc_directory
     WHERE key = 'NC.TABLESPACE.INDEXES';

    create_index ('IDX_QRTZ_T_ST_NFT', 'qrtz_triggers (trigger_state, next_fire_time)');
    create_index ('QRTZ_JOB_LISTENERSJOB_NAMEFK', 'qrtz_job_listeners (job_name)');
    create_index ('QRTZ_TRIGGERSJOB_NAMEFK', 'qrtz_triggers (job_name)');
    create_index ('QRTZ_TRIGGER_LISTENERSTRIGGEFK','qrtz_trigger_listeners (trigger_name, trigger_group)');
END;
/

Answer 2

我会像这样重写你的代码：

DECLARE

  PROCEDURE create_index(idx VARCHAR2, def VARCHAR2) IS
      idxts VARCHAR2(100);
      fake NUMBER(1);
    BEGIN
      SELECT
        nvl(min(value), 'NC_INDEXES')
      INTO idxts
      FROM nc_directory
      WHERE key = 'NC.TABLESPACE.INDEXES';

      SELECT 1 INTO fake FROM user_indexes WHERE index_name = idx;

    EXCEPTION
        WHEN no_data_found THEN
          EXECUTE IMMEDIATE 'create index ' || idx || ' on ' || def || ' tablespace ' || idxts;
    END;

BEGIN

  create_index('IDX_QRTZ_T_ST_NFT', 'qrtz_triggers (trigger_state, next_fire_time)');

  create_index('QRTZ_JOB_LISTENERSJOB_NAMEFK', 'qrtz_job_listeners (job_name)');

  create_index('QRTZ_TRIGGERSJOB_NAMEFK', 'qrtz_triggers (job_name)');

  create_index('QRTZ_TRIGGER_LISTENERSTRIGGEFK', 'qrtz_trigger_listeners (trigger_name, trigger_group)');
END;
/

Answer 3

每个pl/sql块都以'/‘结尾。对于数据库来说，这意味着“现在执行上面的所有代码”。

因此，当您运行这段代码时，只执行第一部分，当它转到第二个begin-end块时，它会失败，因为它不知道这样的变量。

您只需将第一个plsql部分中的"declare“部分复制到此脚本中的所有begin-end块中