时间戳

首页 > 解决方案 > ORA-06550: 必须声明标识符

oracle - ORA-06550: 必须声明标识符

问题描述

我有 PL/SQL 过程代码，当我尝试运行它失败时，我尝试用“创建或替换过程 create_index”替换 - 仍然是错误。

ORA-06550：3 第 2 行，第 3 列：ORA-06550：3 第 4 行，第 8 列：标识符“IDXTS”必须声明第 5 行，第 3 列：错误在行：INTO idxts ORA-06550：3 第 7 列，列3：必须在第 7 行第 3 列声明标识符“CREATE_INDEX”：

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;
/

标签： oraclestored-proceduresplsql

解决方案

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

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;
/

推荐阅读