Отобрать из строки одну или несколько уникальных полседовательностей чисел

Я не совсем понял что должны означать флаги (l_sp и l_ep) в Вашем коде и поэтому не совсем понял как его исправить.

Можно построить запрос на основе регулярных выражений.

Вот такой запрос например вытаскивает из строки числовые последовательности:

select regexp_substr(p_dbs, '[[:digit:]]+', 1, level) 
  from dual
connect by regexp_substr(p_dbs, '[[:digit:]]+', 1, level) is not null

Как он работает: функция REGEXP_SUBSTR возвращает N-ю последовательность в строке, которая соответствует регулярному выражению. Регулярное выражение [[:digit:]]+ вытаскивает последовательности чисел. Рекурсивный запрос (connect by) будет вытаскивать все последовательности чисел по очереди пока они не закончатся (вернется пустая строка, т.е. null).

Теперь можно вытащить из строки уникальные последовательности нужной длины:

select t.numbers
  from (select regexp_substr(p_dbs, '[[:digit:]]+', 1, level) as numbers,
               level as l --Номер последовательности с начала строки
          from dual
        connect by regexp_substr(p_dbs, '[[:digit:]]+', 1, level) is not null) t
 where length(t.numbers) in (5, 6) --фильтр по длине
 group by t.numbers
 order by min(t.l) --Сортируем последовательности в оригинальном порядке (если это нужно) 
                   -- Если сортировка не нужна, то можно обойтись DISTINCT
                   -- (убрать GROUP BY и поле L из вложенного запроса)

Можно их вывести:

  for r in (select t.numbers
              from (select regexp_substr(p_dbs, '[[:digit:]]+', 1, level) as numbers,
                           level as l
                      from dual
                    connect by regexp_substr(p_dbs, '[[:digit:]]+', 1, level) is not null) t
             where length(t.numbers) in (5, 6)
             group by t.numbers
             order by min(t.l)) loop
    dbms_output.put_line(r.numbers);
  end loop;

Можно собрать в коллекцию:

select t.numbers bulk collect
  into t_values
  from (select regexp_substr(p_dbs, '[[:digit:]]+', 1, level) as numbers,
               level as l
          from dual
        connect by regexp_substr(p_dbs, '[[:digit:]]+', 1, level) is not null) t
 where length(t.numbers) in (5, 6)
 group by t.numbers
 order by min(t.l);

Дополнение по оригинальному коду

Я тестировал с ипользованиям конструкции from dual connect by regexp_substr, очень долго работает по сравнению с текущей версией. l_sp начало последовательности из чисел, l_ep конец последовательности цифр

Попробовал упростить оригинальный код. Вроде бы можно обойтись без флагов. Попробуйте такой пример:

declare
  p_dbs varchar(1000) := 'MK~RU_AR~ZEN~ADFORM_CP~3301071970_CI~FY22QO_OB~1X1_TS~APV-123895_VV~NONE_PI~123895_ID~GLD0009ADF__CV~PPV-123895_FF~CPM';
  type values_t is table of varchar(150);
  t_values values_t := values_t();
  l_char   varchar2(1 char);
  l_buffer varchar2(150 char);
begin
  for i in 1 .. length(p_dbs) loop
    l_char := substr(p_dbs, i, 1);
    --Проверка на цифру
    if length(trim(translate(l_char, '01234576789', ' '))) is null then
      -- Если цифра, добавляем ее в последовательность
      l_buffer := l_buffer || l_char;
    elsif l_buffer is not null then
      --Если не цифра, то проверяем есть ли последовательность
      --Выполняем все нужные проверки
      if length(l_buffer) in (5, 6) and not l_buffer MEMBER OF t_values then
        t_values.EXTEND;
        t_values(t_values.COUNT) := l_buffer;
        dbms_output.put_line(l_buffer);
      end if;
      l_buffer := '';
    end if;
  
  end loop;
  --Проверка на случай если числовая последовательность в самом конце строки
  if l_buffer is not null then
    if (length(l_buffer) = 5 or length(l_buffer) = 6) and not l_buffer
     MEMBER OF t_values then
      t_values.EXTEND;
      t_values(t_values.COUNT) := l_buffer;
      dbms_output.put_line(l_buffer);
    end if;
    l_buffer := '';
  end if;
end;

Соответственно, функция может выглядеть вот так:

create or replace function get_distinct_dbs_2(p_dbs in varchar2)
  return varchar2 deterministic as

  type values_t is table of varchar(150);
  t_values values_t := values_t();
  l_ret    varchar2(4000 char);
  l_char   varchar2(1 char);
  l_buffer varchar2(35 char);
begin
  for i in 1 .. length(p_dbs) loop
    l_char := substr(p_dbs, i, 1);
    --Проверка на цифру
    if length(trim(translate(l_char, '01234576789', ' '))) is null then
      -- Если цифра, добавляем ее в последовательность
      l_buffer := l_buffer || l_char;
    elsif l_buffer is not null then
      --Если не цифра, то проверяем есть ли последовательность
      --Выполняем все нужные проверки
      if length(l_buffer) in (5, 6) and not l_buffer MEMBER OF t_values then
        t_values.EXTEND;
        t_values(t_values.COUNT) := l_buffer;
        l_ret := l_ret || ';' || l_buffer;
      end if;
      l_buffer := '';
    end if;
  
  end loop;
  --Проверка на случай если числовая последовательность в самом конце строки
  if length(l_buffer) in (5, 6) and not l_buffer MEMBER OF t_values then
    l_ret := l_ret || ';' || l_buffer;
  end if;

  l_ret := ltrim(l_ret, ';');
  return l_ret;
end;

В Вашем коде судя по всему ошибка в двух взаимовлияющих условиях:

--Добавили цифру, начали последовательность.
if l_test=TRUE and l_sp=false  and l_ep=false then
    l_buffer := l_buffer || l_char;
    l_sp := true;
end if;
--Если последовательность начата, то добавляем цифру еще раз
if l_test=TRUE and l_sp=true  and l_ep=false then 
    l_buffer := l_buffer || l_char;
end if;

Из-за этого функция будет добавлять первый символ последовательности дважды, что приведет к ошибкам. Например, для входящей строки 1235a вернется значение 11235.