CODEWITHSUNDEEP
sqloracle-database
Sebas
Sebas

Reputation: 21

When No data found Oracle

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

Upvotes: 2

Views: 2063

Answers (2)

Miracle
Miracle

Reputation: 387

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
    BEGIN   
        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;
END ADD_OFERTA;

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56725

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;

Upvotes: 1

Related Questions