CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
sahana
sahana

Reputation: 9

ORA-06550:identifier must be declared

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

Upvotes: 0

Views: 2691

Answers (3)

EJ Egyed
EJ Egyed

Reputation: 6064

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

Upvotes: 1

kfinity
kfinity

Reputation: 9091

I would rewrite your code like this:

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

Upvotes: 1

ekochergin
ekochergin

Reputation: 4129

Every pl/sql block ends with '/'. For the database it means "and now execute all the code from above".

So, when you running this code only the first part gets executed and when it goes to the second begin-end blocks it fails because it doesn't know such a variable.

All you need to copy "declare" part from first plsql part across all the begin-end blocks in this script

Upvotes: 0

Related Questions