0

I have a PL/SQL function and it doesn't seem to reach an exception when I encounter no_data_found. I have looked through and tried to add my select statement into its own begin block but I alway get syntax errors. Where do I need to put the exception ? Thank you

create or replace FUNCTION is_artikl_eligible_for_zamjena (
--input vars
inSifraArtikla IN A_ZAMJENA_ARTIKLI.SIFRA_ARTIKLA%type, 
inDatumPocetak IN VARCHAR2, 
inSkladiste IN A_ZAMJENA.SKL%type,
inProdavaonice IN A_ZAMJENA.POPIS_PROD%type
)
RETURN NUMBER
is 
existingSifraArtikla A_ZAMJENA_ARTIKLI.SIFRA_ARTIKLA%type;
existingBrojZamjene A_ZAMJENA.BROJ_ZAMJENE%type;
existingDatumKraj A_ZAMJENA.DATUM_KRAJ%type;
existingSkladiste A_ZAMJENA.SKL%type;
existingProdavaonice A_ZAMJENA.POPIS_PROD%type;
BEGIN

  dbms_output.enable();
  --check if there is sifra_artikla in a_zamjena_artikli where     a_zamjena.datumKraj != null or a_zamjena.datumKraj > sysdate
  for i IN (
  SELECT azam.BROJ_ZAMJENE, azam.DATUM_KRAJ, azam.SKL, azam.POPIS_PROD, azamAr.SIFRA_ARTIKLA
  FROM A_ZAMJENA azam JOIN A_ZAMJENA_ARTIKLI azamAr 
  ON  azam.BROJ_ZAMJENE = azamAr.BROJ_ZAMJENE
  WHERE azamAr.SIFRA_ARTIKLA = inSifraArtikla
  )

LOOP
existingBrojZamjene :=i.BROJ_ZAMJENE; 
--existingDatumKraj := TO_CHAR(COALESCE(i.DATUM_KRAJ,'21-01-25 00:00'));
existingDatumKraj := i.DATUM_KRAJ;
existingSkladiste := COALESCE(i.SKL, '0');
existingProdavaonice := COALESCE(i.POPIS_PROD, 0);
existingSifraArtikla := i.SIFRA_ARTIKLA;

  if existingDatumKraj IS NOT NULL AND existingDatumKraj < (sysdate -1) then --level 0
      dbms_output.put_line('Datum kraj postojeće zamjene nije null ali je završio');
      return 1;

     else
     if (existingDatumKraj IS NULL) OR (existingDatumKraj > sysdate) then --level 1
      dbms_output.put_line('Zamjene imaju preklapajuće datume, provjeravam da li su na istim skladištima ili prodavaonicama');

         if existingSkladiste != inSkladiste OR existingProdavaonice != inProdavaonice then -- level 2
          dbms_output.put_line('Zamjene imaju preklapajuće datume ali nisu na istim skladištima ili prodavaonicama');
           return 1;

         else -- level 2
         return 0;  
        end if; --level 2
     else --else za datum kraj, level 1
     dbms_output.put_line('Zamjene nemaju preklapajuće datume');
     return 1;

    end if; --level 1
  end if; --level 0
  END LOOP;

EXCEPTION WHEN NO_DATA_FOUND THEN 
dbms_output.put_line('nema retka');

RETURN 1;

END;
1
  • 6
    You are using a CURSOR FOR LOOP. If your query doesn't retrieve any rows, then a NO_DATA_FOUND exception will not be raised. Commented Sep 23, 2016 at 7:56

2 Answers 2

2

Untested, but I would probably use a local variable to check whether my cursor loop found any rows, and check that at the end. (It could be a Boolean or a counter or something else. I've used a counter below.)

create or replace function is_artikl_eligible_for_zamjena
    ( insifraartikla in a_zamjena_artikli.sifra_artikla%type
    , indatumpocetak in varchar2
    , inskladiste    in a_zamjena.skl%type
    , inprodavaonice in a_zamjena.popis_prod%type )
    return number
is
    rows_found pls_integer := 0;
begin
    dbms_output.enable();

    -- check if there is sifra_artikla in a_zamjena_artikli where a_zamjena.datumKraj != null or a_zamjena.datumKraj > sysdate
    for i in (
        select azam.broj_zamjene
             , azam.datum_kraj
             , coalesce(azam.skl,'0') as skl
             , coalesce(azam.popis_prod,0) as popis_prod
             , azamar.sifra_artikla
        from   a_zamjena azam
               join   a_zamjena_artikli azamar
               on     azamar.broj_zamjene = azam.broj_zamjene
        where  azamar.sifra_artikla = insifraartikla
    )    
    loop
        rows_found := rows_found +1;

        if i.datum_kraj < sysdate - 1 then
            --level 0
            dbms_output.put_line('Datum kraj postojeće zamjene nije null ali je završio');
            return 1;

        else
            if i.datum_kraj is null or i.datum_kraj > sysdate then
                --level 1
                dbms_output.put_line('Zamjene imaju preklapajuće datume, provjeravam da li su na istim skladištima ili prodavaonicama');

                if i.skl != inskladiste or i.popis_prod != inprodavaonice then
                    -- level 2
                    dbms_output.put_line('Zamjene imaju preklapajuće datume ali nisu na istim skladištima ili prodavaonicama');
                    return 1;

                else
                    -- level 2
                    return 0;
                end if; --level 2
            else
                --else za datum kraj, level 1
                dbms_output.put_line('Zamjene nemaju preklapajuće datume');
                return 1;

            end if; --level 1
        end if; --level 0
    end loop;

    if rows_found = 0 then
        raise no_data_found;
    end;
exception
    when no_data_found then
        dbms_output.put_line('nema retka');

        return 1;

end;

I'm not sure raising a no_data_found exception is a particularly good approach here, though. In fact I'm not keen on a return inside a loop either. Maybe the problem goes away if you just assign the value you want to return to a local variable during the loop, and check it at the end, handling the case where it has no value.

With a local variable and a single return point instead of manipulating exception handlers, it would look something like this:

create or replace function is_artikl_eligible_for_zamjena
    ( insifraartikla in a_zamjena_artikli.sifra_artikla%type
    , indatumpocetak in varchar2
    , inskladiste    in a_zamjena.skl%type
    , inprodavaonice in a_zamjena.popis_prod%type )
    return number
is
    l_result number;
begin
    dbms_output.enable();

    -- check if there is sifra_artikla in a_zamjena_artikli where a_zamjena.datumKraj != null or a_zamjena.datumKraj > sysdate
    for i in (
        select azam.broj_zamjene
             , azam.datum_kraj
             , coalesce(azam.skl,'0') as skl
             , coalesce(azam.popis_prod,0) as popis_prod
             , azamar.sifra_artikla
        from   a_zamjena azam
               join   a_zamjena_artikli azamar
               on     azam.broj_zamjene = azamar.broj_zamjene
        where  azamar.sifra_artikla = insifraartikla
    )    
    loop
        if i.datum_kraj < sysdate - 1 then
            --level 0
            dbms_output.put_line('Datum kraj postojeće zamjene nije null ali je završio');
            l_result := 1;

        else
            if i.datum_kraj is null or i.datum_kraj > sysdate then
                --level 1
                dbms_output.put_line('Zamjene imaju preklapajuće datume, provjeravam da li su na istim skladištima ili prodavaonicama');

                if i.skl != inskladiste or i.popis_prod != inprodavaonice then
                    -- level 2
                    dbms_output.put_line('Zamjene imaju preklapajuće datume ali nisu na istim skladištima ili prodavaonicama');
                    l_result := 1;

                else
                    -- level 2
                    l_result := 0;
                end if; --level 2
            else
                --else za datum kraj, level 1
                dbms_output.put_line('Zamjene nemaju preklapajuće datume');
                l_result := 1;

            end if; --level 1
        end if; --level 0
    end loop;

    if l_result is then
        dbms_output.put_line('nema retka');
        l_result := 1;
    end;

    return l_result;
end;

I'm not sure what the dbms_output.put_line calls are doing, but that's another question.

Sign up to request clarification or add additional context in comments.

3 Comments

Hey thank you very much, I'll try to implement your code and let you know the result. The dmbs_output.put_line are just development "logs" so I can see if my func is correct.
It also occurs to me that if the cursor fetches more than one row, the above versions will use the values from the last row only. Is that what you want? If you don't expect more than one row, then a plain select into and no loop would take you back into familiar no_data_found territory.
Correct, I need every row that has my item_no(sifra_artikla). I need to see if the warehouse (sifra_skladista) or shop_no (popis_prod) is the same/different for in_item_no and existing_item_no. If either of them is different for the same item_no, I can return 1, else I need to return 0.
1

You simply cannot raise a NO_DATA_FOUND exception using a for loop. You can do using a goto statement for insatance. See below example.

DECLARE
   v_attr char(88);
CURSOR  SELECT_USERS IS
 SELECT id 
 FROM USER_TABLE
 WHERE USERTYPE = 'X';
BEGIN
    FOR user_rec IN SELECT_USERS 
     LOOP    
        BEGIN
            SELECT attr 
            INTO v_attr 
            FROM ATTRIBUTE_TABLE
            WHERE user_id = user_rec.id;            
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               -- user does not have attribute, continue loop to next record.
               goto end_loop; ---Adding a label
         END;

        <<end_loop>> --Label
        null;         
    END LOOP;
END;

6 Comments

GOTO, seriously?
@WilliamRobertson..check out the context of the answer..its used to avoid exit loop..If you wish you can give a better answer. i just provided my views
I think EXIT can replace GOTO here.
No..I don't want to do anything when no data is found..The loop must continue.
How about CONTINUE?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.