Почему возникает ошибка в sql-триггере (БД Oracle)?

Question

[Vergileey]

Работаю с Oracle Web Center Content.

Поставили задачу сделать механизм автоматического присвоения регистрационного номера к документу. Решил создать триггер в БД, который работает при создании записи нового документа.

Передо мной стояли 2 задачи: обычная регистрация документа, регистрация прошедшим числом. С обычной регистрацией проблем нет, а вот с регистрацией прошедшей датой вышли трудности.
По сути если документ регистрируется прошедшей датой, то нужно найти последний документ в этой дате с нужной группой защиты и добавить букву после порядкового номера документа в регистрационный номер. Вроде, нашел, где вылетает ошибка (когда получаю регистрационный номер нужного документа), но не знаю, как исправить.
Вот код с комментариями:

create or replace
TRIGGER ZID_DOCMETA_TRG 
BEFORE INSERT ON DOCMETA
FOR EACH ROW
DECLARE CNT NUMBER := 0; -- порядковый номер регистрируемого документа
        doctype DOCTYPES.DDESCRIPTION%type;-- тип документа
        SEQURITYGROUP revisions.dsecuritygroup%TYPE; --группа защиты
        SIMV VARCHAR(10);  -- нужно для получения первых 2 символов типа документа
        data_reg_c date;
        --data_reg varchar(20); --дата регистрации документа, для регистрации задним числом
        data_reg varchar(10);
        last_id REVISIONS.DID%type; --последний документ зарегистрированный за день
        last_reg_nomer varchar2(30); --регистрационный номер последнего документа за день
        simvol varchar(10); --место, куда вставить символ при регистрации прошедшим числом числом

BEGIN
  SELECT  R.dsecuritygroup, r.dindate, d.ddescription  INTO SEQURITYGROUP, data_reg_c, doctype FROM REVISIONS R, doctypes d
  WHERE R.DID = :NEW.DID and d.ddoctype = r.ddoctype; --получаем значения полей
    SELECT to_char(data_reg_c,'dd.mm.yy') into data_reg from dual; 

     if (data_reg = to_char(sysdate,'dd.mm.yy')) then -- регистрация документа сегодняшним числом
      begin
        CNT := REG_NAMBER_SEQ.NEXTVAL;-- следующий порядковый номер из счетчика
        SELECT SUBSTR(doctype,1,2) INTO SIMV FROM DUAL; -- 2 символа типа документа
        IF ((SEQURITYGROUP = 'Общие') or (SEQURITYGROUP = 'Public')) THEN -- если общий документ
          SELECT  SIMV || '-' || '/' || CNT || '-' || TO_CHAR(SYSDATE,'YYYY') INTO :NEW.XZID_VKH_NOMER FROM DUAL;
        end if;  
        IF ((SEQURITYGROUP != 'Общие') and (SEQURITYGROUP != 'Public')) THEN  -- если есть группа защиты
          SELECT  SIMV || '-' || '/' || CNT || '-' || TO_CHAR(SYSDATE,'YYYY') || SEQURITYGROUP INTO :NEW.XZID_VKH_NOMER FROM DUAL;
        END IF;
      end;
     end if;
    
     if (data_reg < to_char(sysdate,'dd.mm.yy')) then - регистрация документа прошедшей датой
      begin
        SELECT MAX(did) into last_id from  DEV_OCS.Revisions
        where ((to_char(DINDATE,'dd.mm.yy') < data_reg) or (to_char(DINDATE,'dd.mm.yy') = data_reg)) and (dsecuritygroup = SEQURITYGROUP); -- здесь получаем последний id на который был зарегистрирован документ за введенное число или раньше с нужной группой защиты
        SELECT XZID_VKH_NOMER into last_reg_nomer from dev_ocs.docmeta
 where docmeta.did = last_id; --получаем регистрационный номер нужного документа. Вот в этом месте вылетает с ошибкой.
        SELECT substr(last_reg_nomer,instr(last_reg_nomer,'-'||to_CHAR(data_reg_c,'YYYY'))-1,1) into simvol from dual;
        IF ((SEQURITYGROUP = 'Общие') or (SEQURITYGROUP = 'Public')) THEN
          begin
            SELECT replace(last_reg_nomer,'-'||to_char(data_reg_c,'YYYY')) into last_reg_nomer from dual;
            if ((ascii(simvol)<97) or ascii(simvol)>122) then
              SELECT last_reg_nomer||chr(97)|| '-' || TO_CHAR(SYSDATE,'YYYY') INTO :NEW.XZID_VKH_NOMER FROM DUAL; 
            end if;
            if ((ascii(simvol)>96) and ascii(simvol)<122) then
              SELECT last_reg_nomer||chr(ascii(simvol)+1)|| '-' || TO_CHAR(SYSDATE,'YYYY') INTO :NEW.XZID_VKH_NOMER FROM DUAL; 
            end if;
            if (ascii(simvol)=122) then
             begin
               select replace(last_reg_nomer,'z','a') into last_reg_nomer from dual;
               SELECT last_reg_nomer||chr(97)|| '-' || TO_CHAR(SYSDATE,'YYYY') INTO :NEW.XZID_VKH_NOMER FROM DUAL; 
             end;
            end if;
          end;
        end if;
        IF ((SEQURITYGROUP != 'Общие') and (SEQURITYGROUP != 'Public')) then
          begin
            select replace(last_reg_nomer,'-'||to_char(data_reg_c,'YYYY')||SEQURITYGROUP) into last_reg_nomer from dual;
            if ((ascii(simvol)<97) or ascii(simvol)>122) then
              SELECT last_reg_nomer||chr(97)|| '-' || TO_CHAR(SYSDATE,'YYYY')||SEQURITYGROUP INTO :NEW.XZID_VKH_NOMER FROM DUAL; 
            end if;
            if ((ascii(simvol)>96) and ascii(simvol)<122) then
              SELECT last_reg_nomer||chr(ascii(simvol)+1)|| '-' || TO_CHAR(SYSDATE,'YYYY')||SEQURITYGROUP INTO :NEW.XZID_VKH_NOMER FROM DUAL; 
            end if;
            if (ascii(simvol)=122) then
              begin
                select replace(last_reg_nomer,'z','a') into last_reg_nomer from dual;
                SELECT last_reg_nomer||chr(97)|| '-' || TO_CHAR(SYSDATE,'YYYY')||SEQURITYGROUP INTO :NEW.XZID_VKH_NOMER FROM DUAL; 
              end;
            end if;
          end;
       end if;
     end;
    end if;
END;

Я в SQL не очень хорошо разбираюсь. Объясните, что я делаю не так.

Answer 1

[Алексей Скахин]

А где ошибка, как она выглядит?

Answer 2

[Vergileey]

Как я смог установить ошибка возникает в этих запросах:

SELECT MAX(did) into last_id from  DEV_OCS.Revisions
        where ((to_char(DINDATE,'dd.mm.yy') < data_reg) or (to_char(DINDATE,'dd.mm.yy') = data_reg)) and (dsecuritygroup = SEQURITYGROUP);
 -- здесь получаем последний id на который был зарегистрирован документ за введенное число или раньше с нужной группой защиты
        SELECT XZID_VKH_NOMER into last_reg_nomer from dev_ocs.docmeta
 where docmeta.did = last_id; --получаем регистрационный номер нужного документа. Вот в этом месте вылетает с ошибкой.

Если во втором запросе подставить значение индекса документа вместо last_id, то отрабатывает нормально. Если обрабатывать не в триггере, а просто в sql редакторе, то тоже работает. Но я не могу понять почему не работает так как есть.

Comments

[Алексей Скахин]

Второй запрос может свалиться с ошибкой, если вернет 0 ( ошибка: NO_DATA_FOUND ) или более 1 результата (TOO_MANY_ROWS ).
Оберните его тоже в MAX, чтобы гарантированно получить одну строку.
Или сделайте обработчики ошибок на TOO_MANY_ROWS и NO_DATA_FOUND, а там логирование, чтобы выявить причину.

[Vergileey]

@pihel оказалось что при поиске последнего id документа у меня запрос находил сам документ, который я добавляю. При попытке получить регистрационный номер из самого себя он получал пустой результат. Спасибо за подсказку.