ERRO PROCEDURE DO SQL PARA O ORACLE

dmbs · Sex, 17 Fev 2012 12:52 pm

Boa tarde!

Estou com erros em uma procedure 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;
  /

Dom, 19 Fev 2012 8:57 pm

Olá dmbs,

Um primeiro ponto que você precisa entender: apesar dos bancos serem compatíveis com o SQL ANSI-padrão, eles geralmente apresentam linguagem de programação SQL diferenciadas. Por exemplo, o SQLSERVER tem a linguagem T-SQL, enquanto o ORACLE dispõe do PL/SQL.

Cada uma destas linguagens possuie recursos próprios e geralmente uma técnica de programação de um banco nem sempre é performática em outro. Por exemplo, CURSORES são um recurso bem rápido no ORACLE, mas em versões antigas do SQLSERVER eram uma "verdadeira carroça".

Se você é "bam-bam-bam" no SQLSERVER e "engatinha" no ORACLE, uma boa pedida é ir fazendo pesquisas no GOOGLE, informando o comando do SQLSERVER (ex: DEALLOCATE, FETCH NEXT) e combinando isso com a palavra ORACLE. Geralmente aparecem artigos comparando as duas linguagens de programação.

Bem, vamos à sua procedure:

A) Crie a tabela temporária FORA da procedure. Este é um comando DDL e o oracle não o executa dentro de uma procedure (a menos que seja um comando EXECUTE IMMEDIATE). Pesquisa sobre TEMPORARY TABLES no GOOGLE, associada com a palavra ORACLE (ou então TABELAS TEMPORARIAS). No caso da sua tabela temporária eu ainda recomendo que você reviste os DATATYPES das colunas (ex: COLLATE database_default existe no oracle?);

B) O PLSQL geralmente apresenta suas procedures ou blocos com a seguinte estrutura:

Selecionar tudo

DECLARE
   <decalracao variaveis>
BEGIN
   <comandos DML>
EXCEPTION
   <tratamente de excecoes>
END;

Ou seja, você pode declarar o cursor na área de DECLARE, mas só pode ir abrindo o cursor ou executando
SELECT´s DEPOIS do BEGIN;

C) Você precisa estudar como se trabalha com cursores. Segue aquí um MINI-RESUMO (que não o desobrgará de estudar este assunto:

Selecionar tudo

        DECLARE (ou CREATE OR REPLACE PROCEDURE ________ IS)
              CURSOR <nome cursor> IS
                  SELECT .. FROM .. WHERE ...;
               <outras variaveis>;
          BEGIN
              <comandos>
              OPEN <nome_cursor>;
              LOOP
                  FETCH <nome cursor> INTO <variaveis a serem declaradas na seção DECLARE>;
                  EXIT WHEN <nome cursor>%NOTFOUND;
                  <comandos>;
             END LOOP;
              CLOSE <nome cursor>;
              <comandos>;
         END;

Desconfio que não existe comando DESALLOCATE para os cursores (pesquise o assunto);

D) Dentro do PL/SQL não é possível executar o comando SELECT .. FROM... WHERE .... Você sempre precisa armazenar o resultado de um SELECT em VARIAVEIS. Esta variáveis podem ser atualizadas por um comando do tipo SELECT ...INTO <variaveis> FROM ... WHERE .... Dependendo do tipo (ex: se não retornar um array de dados) o seu "SELECT .. INTO" precisa retornar no máximo 1 linha;

E) Não existe o comando SET <variavel> no ORACLE. Você pode fazer algo como <variavel> := <algum valor>;

F) Não use "@@" no PL/SQL;

G) Não use EXECUTE <procedure> dentro bloco entre BEGIN .. END;. Basta digitar <procedure>(<relacao variaveis>);. Um ponto importante: a procedure que você chama dentro de sua procedure SPR_TESTE_STATUS precisa obrigatóriamente existir e estar corretamente compilada sem erros no banco de dados ORACLE;

Existem OTIMOS livros da ORACLE em PORTUGUES sobre programação PL/SQL, que lhe ajudariam bastante em sua situação. No forum você vai encontrar indicações sobre eles. Eu recomendo um de PROGRAMANDO PL/SQL para ORACLE 9i (ORACLE PRESS) que seria suficiente para lhe dar os parâmetros básicos de programação.

Boa sorte !

Sergio Coutinho

dmbs · Qui, 23 Fev 2012 8:11 am

Sergio,

Muito obrigada pelas dicas!
Consegui tirar alguns erros, mas ainda não consegui resolver o da linha 49 e 52

Selecionar tudo

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 .( *@...

abçs,
Denise

Selecionar tudo

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as ADM45


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

Qui, 23 Fev 2012 9:42 am

Denise,

Algumas dicas adicionais:

A) LINHA 49/58 : Você poderia remover o BEGIN e o END. Se precisar manter eles, então coloque um ";" na linha 58. Todos os comandos do PL/SQL terminam com ";"
B) LINHA 50: Não use SET para atribuir valores de variaveis. Use a sintaxe <NOME_VARIAVEL> := <VALOR>;
C) LINHAS 45/48/57/60 : Sobre o cursor, recomendo que você use a sintaxe de comandos que te passei:

Selecionar tudo

CREATE OR REPLACE PROCEDURE SPR_MAR_STATUS
IS
    <outras declaracoes>;
   CURSOR RECSET IS  SELECT .... FROM... ;
BEGIN
   OPEN RECSET;
   LOOP
       FETCH RECSET INTO ...;
       EXIT WHEN RECSET%NOTFOUND;
       <comando procedure>;
       <comando insert>;
   END LOOP;
   CLOSE RECSET;
   <outros comandos>;
END SPR_MAR_STATUS;

D) LINHA 60 : Remova o RETURN. O PL/SQL, ao chegar nesta linha, vai sair da procedure;
E) LINHA 52: Nunca chame uma procedure dentro de um bloco PL/SQL com EXECUTE. Basta informar nesta linha o nome da procedure e seus parametros (ex: "spr_ReqDateDiff( sContract, sLocation..., ...);"). Como dito anteriormente, sua procedure SPR_MAR_STATUS só será compilada com sucesso se "spr_ReqDateDiff" existir no banco de dados e estiver com o status VALID (sem erros);
G) LINHA 63/64: Você jamais vai conseguir executar um SELECT .. FROM dentro de um bloco PL/SQL. Sei que isso é possível no T-SQL (SQLSERVER), mas o ORACLE (PL/SQL) trabalha diferente;

Boa sorte,

Sergio Coutinho

ERRO PROCEDURE DO SQL PARA O ORACLE

Quem está online