Mini workflow on status in plsql oracle

Question

I tried to make mini workflow for status in application.
If i have one status of a issue then i can change it only on one defined else you get a information from application that You can't change it code is compiled and used by application caled IFS ERP i think it is simple but i have few errors when the trigger is on

for example: Failed executing statement (ORA-06550: linia 3, kolumna 27: PLS-00201: identifier 'WORKDONE' must be declared

Declare

status_old varchar2(20) :=&OLD:WO_STATUS_ID;
status_new varchar2(20) :=&NEW:WO_STATUS_ID;

begin 
if status_old like 'WorkRequest' and status_new not like 'UnderPreparation'  then
IFSAPP.Error_SYS.Record_General('C_PURCH_REQ_SUB1','You can't change status on  W przygotowaniu ');

ELSIF

status_old like 'UnderPreparation' and status_new not like 'Prepared'  then
IFSAPP.Error_SYS.Record_General('C_PURCH_REQ_SUB1','You can't change status on  Przygotowane ');

ELSIF

status_old like 'Prepared' and status_new not like 'Released'  then
IFSAPP.Error_SYS.Record_General('C_PURCH_REQ_SUB1','You can't change status on  Aktywowane');

ELSIF

status_old like 'Released' and status_new not like'Started'  then
IFSAPP.Error_SYS.Record_General('C_PURCH_REQ_SUB1','You can't change status on  Rozpoczęte ');

ELSIF

status_old like 'STARTED' and status_new not like 'WorkDone'  then
IFSAPP.Error_SYS.Record_General('C_PURCH_REQ_SUB1','You can't change status on  Wykonane');

ELSIF

status_old like 'WorkDone' and status_new not like 'Reported'  then
IFSAPP.Error_SYS.Record_General('C_PURCH_REQ_SUB1',' You can't change status on Odebrane');

end if;

end;

Is it possible to make it simplier ?

why do you use `&` and where the value `&NEW:WO_STATUS_ID` will be set? — hotfix, Jul 30 '19 at 06:37
What is the statement actually being executed, that throws that exception? It looks like it might be coming from an anonymous block rather than a trigger (and it would be helpful to include the full trigger code, not just the modified PL/SQL part, perhaps; assuming this is actually the trigger you refer to). — Alex Poole, Jul 30 '19 at 08:52

hotfix · Answer 1 · 2019-07-30T06:32:14.090

1

you should mask a ' in a string.

replace can't by can''t

further note: in the like pattern should always be '%' or '_'

edited Jul 30 '19 at 06:32

answered Jul 30 '19 at 06:09

hotfix

3,376
20
36

ofcourse :) i just translated it from polish to english - the error statement will be in Polish language – Artur Stolc Jul 30 '19 at 06:12
your example does not correspond to reality then. d Your error is in line 3, but there is no WORKDONE in line three. is this the code that causes the error? – hotfix Jul 30 '19 at 06:30

score 0 · Answer 2 · answered Jul 30 '19 at 09:56

Instead of this nested if ... else ... with partially repeated values you can use table of varchars (type) and check if old status matches corresponding next status. Like in this sample function:

create or replace function stat_change(i_old in varchar2, i_new in varchar2) 
  return number is

  v_ret number(1) := 0;
  type t_stat is table of varchar2(20);
  v_stat t_stat := t_stat('WorkRequest', 'UnderPreparation', 'Prepared', 
                          'Released', 'Started', 'WorkDone', 'Reported');
begin
  for i in 1..v_stat.count - 1 loop
    if v_stat(i) = i_old and v_stat(i + 1) = i_new then 
      return 1;
    end if;
  end loop;
  return 0;
end stat_change;

Test:

select stat_change('WorkDone', 'Started') from dual;  --> 0
select stat_change('Started', 'WorkDone') from dual;  --> 1
select stat_change('XYZ', 'PQR') from dual;           --> 0

Powodzenia.

Mini workflow on status in plsql oracle

2 Answers2