When No data found Oracle

Question

CREATE OR REPLACE PROCEDURE ADD_OFERTA(valorO IN NUMBER, ali IN VARCHAR,subast IN NUMBER) AS    

    fech DATE;  
    cedCompr INTEGER;
    reqpor INTEGER;
    reqmin INTEGER;
    inicial NUMBER;
    mejor NUMBER;
    tmp INTEGER;
    prueba NUMBER;                      
BEGIN    
fech := SYSDATE;

    select porcentaje into reqpor from requisito;
    select incremento into reqmin from requisito;
    select precioInicial INTO inicial FROM subasta Where fk_idofertaganadora is null and PK_idSubasta=subast;
  EXCEPTION
        WHEN NO_DATA_FOUND then
            INICIAL:=0;
SELECT pk_idParticipante into cedCompr from PARTICIPANTE p where p.FK_ALIASUSUARIO = ali;     

select o.valorOferta into mejor from subasta s inner join oferta o on(s.fk_idofertaganadora=o.pk_idoferta) where s.pk_idsubasta=subast; 


EXCEPTION     
        WHEN NO_DATA_FOUND THEN-- ERROR here
            INICIAL:=0;

select greatest((inicial),(mejor+reqmin),(mejor*(reqpor/100 + 1))) into prueba from dual;

 IF (valorO>=prueba) THEN

        INSERT INTO OFERTA(PK_idOferta,fecha,valoroferta,fk_idcomprador,fk_idsubasta) VALUES(ID_OFERTA.NEXTVAL,fech,valorO,cedCompr,subast);
        update subasta set FK_idofertaganadora=ID_OFERTA.CURRVAL where pk_idsubasta=subast;
    END IF;
END ADD_OFERTA;

My problem is that in the second Exeption Oracle displays:

Error(19,5): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma raise return select update when while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Error(28,15): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map

Did any of these answers work for you? If so, you should click the green check mark beside it to indicate that the questions has been answered. You can also upvote any answers that were helpful. — RBarryYoung

RBarryYoung RBarryYoung · Accepted Answer · 2018-03-15T20:51:25

You cannot have the same exception more than once in the same block. AFAIK, the EXCEPTION statement works like an Error-Catcher for the whole block, so you would want to break your proc into two different blocks, like this (I think, I have never done this myself):

CREATE OR REPLACE PROCEDURE ADD_OFERTA(valorO IN NUMBER, ali IN VARCHAR,subast IN NUMBER) AS    

    fech DATE;  
    cedCompr INTEGER;
    reqpor INTEGER;
    reqmin INTEGER;
    inicial NUMBER;
    mejor NUMBER;
    tmp INTEGER;
    prueba NUMBER;                      
BEGIN    
fech := SYSDATE;

  BEGIN
    select porcentaje into reqpor from requisito;
    select incremento into reqmin from requisito;
    select precioInicial INTO inicial FROM subasta Where fk_idofertaganadora is null and PK_idSubasta=subast;

    EXCEPTION
        WHEN NO_DATA_FOUND then
            INICIAL:=0;
  END

  BEGIN
    SELECT pk_idParticipante into cedCompr from PARTICIPANTE p where p.FK_ALIASUSUARIO = ali;     

    select o.valorOferta into mejor from subasta s inner join oferta o on(s.fk_idofertaganadora=o.pk_idoferta) where s.pk_idsubasta=subast; 

    EXCEPTION     
        WHEN NO_DATA_FOUND THEN-- ERROR here
            INICIAL:=0;
  END

  select greatest((inicial),(mejor+reqmin),(mejor*(reqpor/100 + 1))) into prueba from dual;

  IF (valorO>=prueba) THEN

        INSERT INTO OFERTA(PK_idOferta,fecha,valoroferta,fk_idcomprador,fk_idsubasta) VALUES(ID_OFERTA.NEXTVAL,fech,valorO,cedCompr,subast);
        update subasta set FK_idofertaganadora=ID_OFERTA.CURRVAL where pk_idsubasta=subast;
  END IF;
END ADD_OFERTA;

When No data found Oracle

2 Answers