ORA-06550:必须声明标识符

Question

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

ORA-06550:3第2行，第3列：ORA-06550:3第4行，第8列：必须声明标识符"IDXTS"第5行第3列：第行出现错误：INTO idxtsORA-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块都以"/"结尾。对于数据库来说，它意味着"；并且现在执行来自上面的所有代码"；。

所以，当你运行这个代码时，只有第一部分被执行，当它进入第二个开始-结束块时，它会失败，因为它不知道这样一个变量。

所有你需要复制的"；声明"；这个脚本中所有开始-结束块的第一个plsql部分的一部分