--시퀀스 테이블 하나 만들어서 쓰시는건 어떠세요.

CREATE TABLE TB_SEQ AS 

WITH A AS (
SELECT CHR(64+LEVEL) V FROM DUAL CONNECT BY LEVEL <= 26
) , B AS (
SELECT  TO_CHAR(LEVEL,'FM00') V FROM DUAL CONNECT BY LEVEL <= 99
) , C AS (
SELECT A.V || B.V V  FROM A , B  
)

SELECT AA.V || BB.V || CC.V SEQ 
  FROM C AA , C BB , C CC

with t(a) as

(

select 'A01A01A99' from dual union all

select 'A01A01B01' frmo dual
)

select case when regexp_substr(a,'[0-9]+$')+1 ='100' then '01' 

else lpad(to_number(regexp_substr(a,'[0-9]+$')+1 ,2,'0') end

from t

WITH t AS
(
-- 주석 제거해 가면서 case 별로 테스트 해보세요 --
SELECT '' seq FROM dual WHERE 1=2      -- Case 0 (Null)
UNION ALL SELECT 'A01A01A01' FROM dual -- Case 1 (_________)
UNION ALL SELECT 'A01A01A99' FROM dual -- Case 2 (_______99)
--UNION ALL SELECT 'A01A90Z99' FROM dual -- Case 3 (______Z99)
--UNION ALL SELECT 'A01A99Z99' FROM dual -- Case 4 (____99Z99)
--UNION ALL SELECT 'A01Z99Z99' FROM dual -- Case 5 (___Z99Z99)
--UNION ALL SELECT 'C99Z99Z99' FROM dual -- Case 6 (_99Z99Z99)
--UNION ALL SELECT 'Z99Z99Z99' FROM dual -- Case 7 (Z99Z99Z99)
)
SELECT seq curr_seq
     , CASE WHEN v6 IS NULL THEN 'A01A01A01'
            WHEN v6 < '99'  THEN v1||v2||v3||v4||v5||x6
            WHEN v5 < 'Z'   THEN v1||v2||v3||v4||x5||'01'
            WHEN v4 < '99'  THEN v1||v2||v3||x4||   'A01'
            WHEN v3 < 'Z'   THEN v1||v2||x3||     '01A01'
            WHEN v2 < '99'  THEN v1||x2||        'A01A01'
            WHEN v1 < 'Z'   THEN x1||          '01A01A01'
            ELSE 'out of bound'
        END next_seq
  FROM (SELECT seq
             , v1, v2, v3, v4, v5, v6
             , CHR(ASCII(v1) + 1) x1, LPAD(v2 + 1, 2, '0') x2
             , CHR(ASCII(v3) + 1) x3, LPAD(v4 + 1, 2, '0') x4
             , CHR(ASCII(v5) + 1) x5, LPAD(v6 + 1, 2, '0') x6
          FROM (SELECT seq
                     , SUBSTR(seq, 1, 1) v1, SUBSTR(seq, 2, 2) v2
                     , SUBSTR(seq, 4, 1) v3, SUBSTR(seq, 5, 2) v4
                     , SUBSTR(seq, 7, 1) v5, SUBSTR(seq, 8, 2) v6
                  FROM (SELECT MAX(seq) seq FROM t)
                )
        )
;

어우~ 순번을 왜 저런식으로 구성을 하는지 이해가 가질 않네요;;