I have PL/SQL Procedure code, when I try to run it fails with, I tried replacing with "create or replace procedure create_index" - still it was error.
ORA-06550: 3 line 2, column 3: ORA-06550: 3 line 4, column 8: identifier 'IDXTS' must be declared line 5, column 3: ERROR at line: INTO idxts ORA-06550: 3 line 7, column 3: identifier 'CREATE_INDEX' must be declared line 7, column 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;
/
As you have your existing code, the procedure create_index
will only be available in the first PL/SQL block. Try combining all the calls to create_index
into a single PL/SQL block like this.
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;
/