luismzk luismzk - 5 months ago 11
SQL Question

SQL NO_DATA FOUND Select exception

We're doing several SQL procedures in Oracle and this one in particular throws a weird result.

The procedure is:

create or replace procedure insertar_pedidodetalle ( pednum number, prod number, cant number) is

lugarlim number;
lugartien number;

begin

Select w.LUG_CLAVE into lugartien
from TIENDA t, PEDIDO p, LUGAR l, LUGAR w
WHERE p.TIENDA_TIE_CLAVE = t.tie_clave and p.ped_numero=pednum and
t.LUGAR_LUG_CLAVE = l.LUG_CLAVE and l.LUGAR_LUG_CLAVE = w.LUG_CLAVE;

Select l.LUGAR_LUG_CLAVE into lugarlim
from LUG_PRO l
WHERE l.PRODUCTO_PRO_CODIGO = prod and l.LUGAR_LUG_CLAVE = lugartien;

exception when NO_DATA_FOUND THEN begin
lugarlim := null;
end;
if (lugarlim is not NULL) then
DBMS_OUtPUT.PUT_LINE('Se ha Generado Un Pedido Exitosamente');
insert into DETALLE_PEDIDO values (SEQUENCE1.nextval, cant, prod, lugarlim, pednum);
else
DBMS_OUtPUT.PUT_LINE('El Producto solicitado no esta disponible en su region');
END IF;
end insertar_pedidodetalle;


The procedure should: ask for an order id, product id, and how many products. Give you the country in which a store is located (lugartien). Check if the store has that product available (lugarlim). If it does (lugarlim is not null), it will insert into detalle_pedido. If not (lugarlim is null), it says it's not available.
The exception appears to be an issue, whenever it doesn't find any proper values, it assigns NULL to lugarlim as it should, and the condition

if (lugarlim is not null)


works fine for the "else" (when lugarlim is NULL). But when it does find a value and is assigned to lugarlim, it won't print "Se ha generado un pedido exitosamente" as well as not inserting into detalle_pedido.

We're fairly new to procedures but can't quite figure this one out so any suggestion is appreciated.

Answer

Corrections to your code - bit of formatting sorts things out!

create or replace procedure insertar_pedidodetalle ( pednum number, prod number, cant number) is

  lugarlim number;
  lugartien number;

begin
  begin -- this is handy

    Select w.LUG_CLAVE into lugartien
    from TIENDA t, PEDIDO p, LUGAR l, LUGAR w
    WHERE p.TIENDA_TIE_CLAVE = t.tie_clave and p.ped_numero=pednum and 
    t.LUGAR_LUG_CLAVE = l.LUG_CLAVE and l.LUGAR_LUG_CLAVE = w.LUG_CLAVE; 

    Select l.LUGAR_LUG_CLAVE into lugarlim
    from LUG_PRO l
    WHERE l.PRODUCTO_PRO_CODIGO = prod and l.LUGAR_LUG_CLAVE = lugartien;

  exception when NO_DATA_FOUND THEN -- this is not required begin   
    lugarlim := null;
  end;

  if (lugarlim is not NULL) then
    DBMS_OUtPUT.PUT_LINE('Se ha Generado Un Pedido Exitosamente');
    insert into DETALLE_PEDIDO values
      (SEQUENCE1.nextval, cant, prod, lugarlim, pednum);  
  else  
    DBMS_OUtPUT.PUT_LINE('El Producto solicitado no esta disponible en su region');
  END IF;
end insertar_pedidodetalle;
Comments