ERRO NO ORACLE

[dmbs]

Boa tarde!

Estou com erros em uma procedure QUANDO COMPILO e não consigo solucionar:
ORA-06550: linha 1, coluna 7:
PLS-00905: o objeto ADM45.SPR_MAR_STATUS é inválido
ORA-06550: linha 1, coluna 7:
PL/SQL: Statement ignored

Transformei a procedure abaixo de SQL para Oracle, o quê pode estar errado?
Por favor, ajudem-me!!!
Obrigada
dmbs

Selecionar tudo

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
/
create or replace procedure SPR_TESTE_STATUS
( 
  sdDateI   datetime,
  sdDateF   datetime,  
  sOrgUnit varchar(255),
  sDescend   varchar(03),  
  sCategory  varchar(25),
  sUID        varchar2,
  sReqType   varchar(25),
  sRequest   varchar(25)
) 
as
              sRStatus      varchar(25);
              sdOpenDate     datetime;
              sCurrStatus    varchar(25);
              sFromStatus    varchar(25);
              sLastStatus    varchar(25);
              sdLastStatus    datetime;
              sdActDate       datetime;
              siMinutes       integer;
              sContract     varchar(25);
              sLocation     varchar(50)
 begin         
  CREATE GLOBAL TEMPORARY TABLE tmp_TimeStatus
  (
    REQUEST varchar(25) COLLATE database_default, 
    RSTATUS varchar(25) COLLATE database_default, 
    MINUTES integer
  )
    
      -- tempo de permanencia nos status
    
    declare CURSOR curActions is
       select FROMSTATUS, CURRSTATUS, ACTDATE
         from ACTION
        where REQUEST := sRequest
          and STCHANGED := 1
        order by SEQUENCE
    open CURSOR curActions
    fetch next from curActions into sFromStatus, sCurrStatus, sdActDate
    set sLastStatus := sRStatus
    set sdLastStatus := sdOpenDate
    while (@@fetch_status <> -1) loop
    begin
      set sLastStatus := sFromStatus
      -- Obtem a diferença em minutos respeitando o periodo de trabalho
      execute spr_ReqDateDiff sContract, sLocation, sdLastStatus, sdActDate, 0, siMinutes output
      insert into tmp_TimeStatus(REQUEST,RSTATUS,MINUTES) values(sRequest, sLastStatus, iMinutes);
      commit;
      set sLastStatus := sCurrStatus
      set sdLastStatus   := sdActDate
      fetch next from curActions into sFromStatus, sCurrStatus, sdActDate
    end
   end loop;
    close CURSOR curActions;
    deallocate CURSOR curActions;
    
    select REQUEST AS 'Chamado', RSTATUS as 'Status', SUM(MINUTES) as 'Minutos' from TABLE tmp_TimeStatus
    group by REQUEST, RSTATUS; 
  
  
  end SPR_TESTE_STATUS;
  /

[dmbs]

Bom dia,

Consegui tirar alguns erros, mas ainda não consegui resolver o da linha 49 e 52 PLS-00103:
49 - PLS-00103: encontrado o símbolo "BEGIN" quando um dos símbolos era esperado: *&=+ at in loop mod lembrete not rem...
52 - PLS-00103: encontrado o símbolo "EXECUTE" quando um dos seguintes símbolos era esperado .( *@...
Alguém poderia ajudar-me, por favor!

Att,
Denise

Selecionar tudo

SQL> 
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered

SQL> /

Session altered

SQL> create or replace procedure SPR_MAR_STATUS
2 (
3 sdDateI datetime,
4 sdDateF datetime,
5 sOrgUnit varchar2,
6 sDescend varchar2,
7 sCategory varchar2,
8 sUID varchar2,
9 sReqType varchar2:=' ',
10 sRequest varchar2
11 )
12 as
13 sRStatus varchar2(25);
14 sdOpenDate datetime;
15 sCurrStatus varchar2(25);
16 sFromStatus varchar2(25);
17 sLastStatus varchar2(25);
18 sdLastStatus datetime;
19 sdActDate datetime;
20 siMinutes integer;
21 sContract varchar2(25);
22 sLocation varchar2(50);
23 sRSTATUS varchar2(50);
24 intMinutes integer;
25 TYPE t_CursorType IS REF CURSOR;
26 recSet t_CursorType;
27 
28 begin
29 -- limpa a tabela INFOMAN antes de iniciar
30 delete from INFOMAN where (ID = iID);
31 delete from INFOMAN where (ID = -iID);
32 commit;
33 
34 
35 
36 -- tempo de permanencia nos status
37 -- cria um cursor para as informacoes do chamado
38 open recSet for
39 select FROMSTATUS, CURRSTATUS, ACTDATE
40 from ACTION
41 where REQUEST := sRequest
42 and STCHANGED := 1
43 order by SEQUENCE;
44 
45 fetch recSet into sFromStatus, sCurrStatus, sdActDate;
46 set sLastStatus := sRStatus;
47 set sdLastStatus := sdOpenDate;
48 while (fetch_status <> -1) loop
49 begin
50 set sLastStatus := sFromStatus
51 -- Obtem a diferença em minutos respeitando o periodo de trabalho
52 execute spr_ReqDateDiff sContract, sLocation, sdLastStatus, sdActDate, 0, siMinutes output;
53 insert into INFOMAN(ID,LABEL1,CVALUES) values(sRequest, sLastStatus, iMinutes);
54 commit;
55 set sLastStatus := sCurrStatus;
56 set sdLastStatus := sdActDate;
57 fetch next from recSet into sFromStatus, sCurrStatus, sdActDate;
58 end
59 end loop;
60 return;
61 close recSet;
62 
63 select sRequest AS "Chamado", sLastStatus as "Status", SUM(iMinutes) as "Minutos" from INFOMAN
64 group by REQUEST, RSTATUS;
65 
66 
67 end SPR_TESTE_STATUS;
68 /
69