Code example: String replacement in PL/SQL

CREATE OR REPLACE PACKAGE QA_UTIL AS
  PROCEDURE imsi_update;
END QA_UTIL;

/

CREATE OR REPLACE PACKAGE BODY QA_UTIL AS
-------------------------------------------
  PROCEDURE imsi_update AS

    CURSOR imsi_rows_cur IS
    SELECT QAT2_TESTS_RAN.*, rowid FROM QAT2_TESTS_RAN
     WHERE generalrange1 LIKE '% IMSI = %'
       AND generalrange1 NOT LIKE '% IMSI = ''%'
       AND test_name LIKE 'VFES%';


    v_generalrange      QAT2_TESTS.generalrange1%TYPE;
    v_generalrange_out  QAT2_TESTS.generalrange1%TYPE;
    v_part_1_ind        NUMBER;
    v_part_1            QAT2_TESTS.generalrange1%TYPE;

    v_part_imsi_ind     NUMBER;
    v_part_imsi         QAT2_TESTS.generalrange1%TYPE;

    v_part_2_ind        NUMBER;
    v_part_2         QAT2_TESTS.generalrange1%TYPE;

  BEGIN
    FOR imsi_rows_rec IN imsi_rows_cur LOOP
      --select * from DEBUG_IPN_W_EDR where IMSI = 214010000000010 and DESCRIPTION='GPRS' and ACTION_TYPE_NAME= 'RELAY' and ACTION_REASON_NAME='Sub: SubInact'
      v_generalrange := imsi_rows_rec.generalrange1;

      v_part_1_ind := INSTR(v_generalrange,'IMSI = ') + LENGTH ('IMSI = ')-1;
      v_part_1 := SUBSTR(v_generalrange,0,v_part_1_ind);

      v_part_imsi_ind := v_part_1_ind+1;
      v_part_imsi  := SUBSTR(v_generalrange,v_part_imsi_ind,LENGTH('214010000000010'));

      v_part_2_ind := v_part_imsi_ind + LENGTH('214010000000010');
      v_part_2 := SUBSTR(v_generalrange,v_part_2_ind);

      v_generalrange_out := v_part_1||''''||v_part_imsi||''''||v_part_2;

      UPDATE QAT2_TESTS_RAN 
         SET generalrange1 = v_generalrange_out 
       WHERE QAT2_TESTS_RAN.rowid = imsi_rows_rec.rowid;
      commit;

    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END imsi_update;
-------------------------------------------
END QA_UTIL;

/