ORA-06550:identifier must be declared

Question

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

Answer 1

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