Oracle PL SQL - Are NO DATA FOUND Exceptions bad for stored procedure performance

Question

I m writing a stored procedure that needs to have a lot of conditioning in it   With the general knowledge from C  NET coding that exceptions can hurt performance  I ve always avoided using them in PL SQL as well   My conditioning in this stored proc mostly revolves around whether or not a record exists  which I could do one of two ways   SELECT COUNT    INTO var WHERE condition  IF var  gt  0 THEN    SELECT NEEDED FIELD INTO otherVar WHERE condition         -or-  SELECT NEEDED FIELD INTO var WHERE condition  EXCEPTION WHEN NO DATA FOUND        The second case seems a bit more elegant to me  because then I can use NEEDED FIELD  which I would have had to select in the first statement after the condition in the first case   Less code   But if the stored procedure will run faster using the COUNT     then I don t mind typing a little more to make up processing speed   Any hints   Am I missing another possibility   EDIT I should have mentioned that this is all already nested in a FOR LOOP   Not sure if this makes a difference with using a cursor  since I don t think I can DECLARE the cursor as a select in the FOR LOOP

User · Answer

Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables   BEGIN     FOR rec IN  SELECT a needed field b other field                   FROM table1 a                   LEFT OUTER JOIN table2 b                     ON a needed field   b condition field                  WHERE a column            LOOP        IF rec other field IS NOT NULL THEN          -- whatever processing needs to be done to other field        END IF      END LOOP  END

User · Answer

If it s important you really need to benchmark both options   Having said that  I have always used the exception method  the reasoning being it s better to only hit the database once

User · Answer

DCookie  I just want to point out that you can leave off the lines that say  EXCEPTION     WHEN OTHERS THEN         RAISE    You ll get the same effect if you leave off the exception block all together  and the line number reported for the exception will be the line where the exception is actually thrown  not the line in the exception block where it was re-raised

User · Answer

The first  excellent  answer stated -  The method with count   is unsafe  If another session deletes the row that met the condition after the line with the count     and before the line with the select     into  the code will throw an exception that will not get handled   Not so   Within a given logical Unit of Work Oracle is totally consistent   Even if someone commits the delete of the row between a count and a select Oracle will  for the active session  obtain the data from the logs   If it cannot  you will get a  snapshot too old  error

User · Answer

The count    will never raise exception because it always returns actual count or 0 - zero  no matter what  I d use count

User · Answer

If it s important you really need to benchmark both options   Having said that  I have always used the exception method  the reasoning being it s better to only hit the database once

User · Answer

Since SELECT INTO assumes that a single row will be returned  you can use a statement of the form   SELECT MAX column    INTO var   FROM table  WHERE conditions   IF var IS NOT NULL THEN       The SELECT will give you the value if one is available  and a value of NULL instead of a NO DATA FOUND exception   The overhead introduced by MAX   will be minimal-to-zero since the result set contains a single row   It also has the advantage of being compact relative to a cursor-based solution  and not being vulnerable to concurrency issues like the two-step solution in the original post

User · Answer

I would not use an explicit cursor to do this   Steve F  no longer advises people to use explicit cursors when an implicit cursor could be used    The method with count    is unsafe   If another session deletes the row that met the condition after the line with the count     and before the line with the select     into  the code will throw an exception that will not get handled   The second version from the original post does not have this problem  and it is generally preferred   That said  there is a minor overhead using the exception  and if you are 100  sure the data will not change  you can use the count     but I recommend against it   I ran these benchmarks on Oracle 10 2 0 1 on 32 bit Windows   I am only looking at elapsed time   There are other test harnesses that can give more details  such as latch counts and memory used    SQL gt create table t  NEEDED FIELD number  COND number        Table created    SQL gt insert into t  NEEDED FIELD  cond  values  1  0        1 row created    declare   otherVar  number    cnt number  begin   for i in 1    50000 loop      select count    into cnt from t where cond   1        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   1       else        otherVar    0       end if     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 02 70   declare   otherVar  number  begin   for i in 1    50000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   1       exception        when no data found then          otherVar    0       end     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 03 06

User · Answer

An alternative to  Steve s code    DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   FOR foo rec IN foo cur LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     The loop is not executed if there is no data  Cursor FOR loops are the way to go - they help avoid a lot of housekeeping  An even more compact solution   DECLARE BEGIN   FOR foo rec IN  SELECT NEEDED FIELD WHERE condition  LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     Which works if you know the complete select statement at compile time

User · Answer

May be beating a dead horse here  but I bench-marked the cursor for loop  and that performed about as well as the no data found method   declare   otherVar  number  begin   for i in 1    5000 loop      begin        for foo rec in  select NEEDED FIELD from t where cond   0  loop          otherVar    foo rec NEEDED FIELD         end loop         otherVar    0       end     end loop  end    PL SQL procedure successfully completed   Elapsed  00 00 02 18

User · Answer

Yes  you re missing using cursors  DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   OPEN foo cur    FETCH foo cur INTO foo rec    IF foo cur FOUND THEN            END IF    CLOSE foo cur  EXCEPTION   WHEN OTHERS THEN     CLOSE foo cur      RAISE  END     admittedly this is more code  but it doesn t use EXCEPTIONs as flow-control which  having learnt most of my PL SQL from Steve Feuerstein s PL SQL Programming book  I believe to be a good thing   Whether this is faster or not I don t know  I do very little PL SQL nowadays

User · Answer

you dont have to use open when you are using for loops   declare cursor cur name is  select   from emp  begin for cur rec in cur name Loop     dbms output put line cur rec ename   end loop  End     or   declare cursor cur name is  select   from emp  cur rec emp rowtype  begin Open cur name  Loop Fetch cur name into  Cur rec     Exit when cur name notfound      dbms output put line cur rec ename   end loop  Close cur name  End

User · Answer

DCookie  I just want to point out that you can leave off the lines that say  EXCEPTION     WHEN OTHERS THEN         RAISE    You ll get the same effect if you leave off the exception block all together  and the line number reported for the exception will be the line where the exception is actually thrown  not the line in the exception block where it was re-raised

User · Answer

An alternative to  Steve s code    DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   FOR foo rec IN foo cur LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     The loop is not executed if there is no data  Cursor FOR loops are the way to go - they help avoid a lot of housekeeping  An even more compact solution   DECLARE BEGIN   FOR foo rec IN  SELECT NEEDED FIELD WHERE condition  LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     Which works if you know the complete select statement at compile time

User · Answer

you dont have to use open when you are using for loops   declare cursor cur name is  select   from emp  begin for cur rec in cur name Loop     dbms output put line cur rec ename   end loop  End     or   declare cursor cur name is  select   from emp  cur rec emp rowtype  begin Open cur name  Loop Fetch cur name into  Cur rec     Exit when cur name notfound      dbms output put line cur rec ename   end loop  Close cur name  End

User · Answer

May be beating a dead horse here  but I bench-marked the cursor for loop  and that performed about as well as the no data found method   declare   otherVar  number  begin   for i in 1    5000 loop      begin        for foo rec in  select NEEDED FIELD from t where cond   0  loop          otherVar    foo rec NEEDED FIELD         end loop         otherVar    0       end     end loop  end    PL SQL procedure successfully completed   Elapsed  00 00 02 18

User · Answer

Since SELECT INTO assumes that a single row will be returned  you can use a statement of the form   SELECT MAX column    INTO var   FROM table  WHERE conditions   IF var IS NOT NULL THEN       The SELECT will give you the value if one is available  and a value of NULL instead of a NO DATA FOUND exception   The overhead introduced by MAX   will be minimal-to-zero since the result set contains a single row   It also has the advantage of being compact relative to a cursor-based solution  and not being vulnerable to concurrency issues like the two-step solution in the original post

User · Answer

I would not use an explicit cursor to do this   Steve F  no longer advises people to use explicit cursors when an implicit cursor could be used    The method with count    is unsafe   If another session deletes the row that met the condition after the line with the count     and before the line with the select     into  the code will throw an exception that will not get handled   The second version from the original post does not have this problem  and it is generally preferred   That said  there is a minor overhead using the exception  and if you are 100  sure the data will not change  you can use the count     but I recommend against it   I ran these benchmarks on Oracle 10 2 0 1 on 32 bit Windows   I am only looking at elapsed time   There are other test harnesses that can give more details  such as latch counts and memory used    SQL gt create table t  NEEDED FIELD number  COND number        Table created    SQL gt insert into t  NEEDED FIELD  cond  values  1  0        1 row created    declare   otherVar  number    cnt number  begin   for i in 1    50000 loop      select count    into cnt from t where cond   1        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   1       else        otherVar    0       end if     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 02 70   declare   otherVar  number  begin   for i in 1    50000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   1       exception        when no data found then          otherVar    0       end     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 03 06

User · Answer

Yes  you re missing using cursors  DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   OPEN foo cur    FETCH foo cur INTO foo rec    IF foo cur FOUND THEN            END IF    CLOSE foo cur  EXCEPTION   WHEN OTHERS THEN     CLOSE foo cur      RAISE  END     admittedly this is more code  but it doesn t use EXCEPTIONs as flow-control which  having learnt most of my PL SQL from Steve Feuerstein s PL SQL Programming book  I believe to be a good thing   Whether this is faster or not I don t know  I do very little PL SQL nowadays

User · Answer

I would not use an explicit cursor to do this   Steve F  no longer advises people to use explicit cursors when an implicit cursor could be used    The method with count    is unsafe   If another session deletes the row that met the condition after the line with the count     and before the line with the select     into  the code will throw an exception that will not get handled   The second version from the original post does not have this problem  and it is generally preferred   That said  there is a minor overhead using the exception  and if you are 100  sure the data will not change  you can use the count     but I recommend against it   I ran these benchmarks on Oracle 10 2 0 1 on 32 bit Windows   I am only looking at elapsed time   There are other test harnesses that can give more details  such as latch counts and memory used    SQL gt create table t  NEEDED FIELD number  COND number        Table created    SQL gt insert into t  NEEDED FIELD  cond  values  1  0        1 row created    declare   otherVar  number    cnt number  begin   for i in 1    50000 loop      select count    into cnt from t where cond   1        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   1       else        otherVar    0       end if     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 02 70   declare   otherVar  number  begin   for i in 1    50000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   1       exception        when no data found then          otherVar    0       end     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 03 06

User · Answer

May be beating a dead horse here  but I bench-marked the cursor for loop  and that performed about as well as the no data found method   declare   otherVar  number  begin   for i in 1    5000 loop      begin        for foo rec in  select NEEDED FIELD from t where cond   0  loop          otherVar    foo rec NEEDED FIELD         end loop         otherVar    0       end     end loop  end    PL SQL procedure successfully completed   Elapsed  00 00 02 18

User · Answer

you dont have to use open when you are using for loops   declare cursor cur name is  select   from emp  begin for cur rec in cur name Loop     dbms output put line cur rec ename   end loop  End     or   declare cursor cur name is  select   from emp  cur rec emp rowtype  begin Open cur name  Loop Fetch cur name into  Cur rec     Exit when cur name notfound      dbms output put line cur rec ename   end loop  Close cur name  End

User · Answer

Stephen Darlington makes a very good point  and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following   begin    for i in 2    10000 loop     insert into t  NEEDED FIELD  cond  values  i  10     end loop  end    Then re-run the benchmarks   I had to reduce the loop counts to 5000 to get reasonable times    declare   otherVar  number    cnt number  begin   for i in 1    5000 loop      select count    into cnt from t where cond   0        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   0       else        otherVar    0       end if     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 04 34  declare   otherVar  number  begin   for i in 1    5000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   0       exception        when no data found then          otherVar    0       end     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 02 10   The method with the exception is now more than twice as fast   So  for almost all cases the method   SELECT NEEDED FIELD INTO var WHERE condition  EXCEPTION WHEN NO DATA FOUND       is the way to go   It will give correct results and is generally the fastest

User · Answer

Yes  you re missing using cursors  DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   OPEN foo cur    FETCH foo cur INTO foo rec    IF foo cur FOUND THEN            END IF    CLOSE foo cur  EXCEPTION   WHEN OTHERS THEN     CLOSE foo cur      RAISE  END     admittedly this is more code  but it doesn t use EXCEPTIONs as flow-control which  having learnt most of my PL SQL from Steve Feuerstein s PL SQL Programming book  I believe to be a good thing   Whether this is faster or not I don t know  I do very little PL SQL nowadays

User · Answer

Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables   BEGIN     FOR rec IN  SELECT a needed field b other field                   FROM table1 a                   LEFT OUTER JOIN table2 b                     ON a needed field   b condition field                  WHERE a column            LOOP        IF rec other field IS NOT NULL THEN          -- whatever processing needs to be done to other field        END IF      END LOOP  END

User · Answer

Stephen Darlington makes a very good point  and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following   begin    for i in 2    10000 loop     insert into t  NEEDED FIELD  cond  values  i  10     end loop  end    Then re-run the benchmarks   I had to reduce the loop counts to 5000 to get reasonable times    declare   otherVar  number    cnt number  begin   for i in 1    5000 loop      select count    into cnt from t where cond   0        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   0       else        otherVar    0       end if     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 04 34  declare   otherVar  number  begin   for i in 1    5000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   0       exception        when no data found then          otherVar    0       end     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 02 10   The method with the exception is now more than twice as fast   So  for almost all cases the method   SELECT NEEDED FIELD INTO var WHERE condition  EXCEPTION WHEN NO DATA FOUND       is the way to go   It will give correct results and is generally the fastest

User · Answer

Since SELECT INTO assumes that a single row will be returned  you can use a statement of the form   SELECT MAX column    INTO var   FROM table  WHERE conditions   IF var IS NOT NULL THEN       The SELECT will give you the value if one is available  and a value of NULL instead of a NO DATA FOUND exception   The overhead introduced by MAX   will be minimal-to-zero since the result set contains a single row   It also has the advantage of being compact relative to a cursor-based solution  and not being vulnerable to concurrency issues like the two-step solution in the original post

User · Answer

May be beating a dead horse here  but I bench-marked the cursor for loop  and that performed about as well as the no data found method   declare   otherVar  number  begin   for i in 1    5000 loop      begin        for foo rec in  select NEEDED FIELD from t where cond   0  loop          otherVar    foo rec NEEDED FIELD         end loop         otherVar    0       end     end loop  end    PL SQL procedure successfully completed   Elapsed  00 00 02 18

User · Answer

Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables   BEGIN     FOR rec IN  SELECT a needed field b other field                   FROM table1 a                   LEFT OUTER JOIN table2 b                     ON a needed field   b condition field                  WHERE a column            LOOP        IF rec other field IS NOT NULL THEN          -- whatever processing needs to be done to other field        END IF      END LOOP  END

User · Answer

An alternative to  Steve s code    DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   FOR foo rec IN foo cur LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     The loop is not executed if there is no data  Cursor FOR loops are the way to go - they help avoid a lot of housekeeping  An even more compact solution   DECLARE BEGIN   FOR foo rec IN  SELECT NEEDED FIELD WHERE condition  LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     Which works if you know the complete select statement at compile time

User · Answer

Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables   BEGIN     FOR rec IN  SELECT a needed field b other field                   FROM table1 a                   LEFT OUTER JOIN table2 b                     ON a needed field   b condition field                  WHERE a column            LOOP        IF rec other field IS NOT NULL THEN          -- whatever processing needs to be done to other field        END IF      END LOOP  END

User · Answer

An alternative to  Steve s code    DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   FOR foo rec IN foo cur LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     The loop is not executed if there is no data  Cursor FOR loops are the way to go - they help avoid a lot of housekeeping  An even more compact solution   DECLARE BEGIN   FOR foo rec IN  SELECT NEEDED FIELD WHERE condition  LOOP            END LOOP  EXCEPTION   WHEN OTHERS THEN     RAISE  END     Which works if you know the complete select statement at compile time

User · Answer

DCookie  I just want to point out that you can leave off the lines that say  EXCEPTION     WHEN OTHERS THEN         RAISE    You ll get the same effect if you leave off the exception block all together  and the line number reported for the exception will be the line where the exception is actually thrown  not the line in the exception block where it was re-raised

User · Answer

you dont have to use open when you are using for loops   declare cursor cur name is  select   from emp  begin for cur rec in cur name Loop     dbms output put line cur rec ename   end loop  End     or   declare cursor cur name is  select   from emp  cur rec emp rowtype  begin Open cur name  Loop Fetch cur name into  Cur rec     Exit when cur name notfound      dbms output put line cur rec ename   end loop  Close cur name  End

User · Answer

DCookie  I just want to point out that you can leave off the lines that say  EXCEPTION     WHEN OTHERS THEN         RAISE    You ll get the same effect if you leave off the exception block all together  and the line number reported for the exception will be the line where the exception is actually thrown  not the line in the exception block where it was re-raised

User · Answer

The count    will never raise exception because it always returns actual count or 0 - zero  no matter what  I d use count

User · Answer

Since SELECT INTO assumes that a single row will be returned  you can use a statement of the form   SELECT MAX column    INTO var   FROM table  WHERE conditions   IF var IS NOT NULL THEN       The SELECT will give you the value if one is available  and a value of NULL instead of a NO DATA FOUND exception   The overhead introduced by MAX   will be minimal-to-zero since the result set contains a single row   It also has the advantage of being compact relative to a cursor-based solution  and not being vulnerable to concurrency issues like the two-step solution in the original post

User · Answer

If it s important you really need to benchmark both options   Having said that  I have always used the exception method  the reasoning being it s better to only hit the database once

User · Answer

Yes  you re missing using cursors  DECLARE   CURSOR foo cur IS      SELECT NEEDED FIELD WHERE condition   BEGIN   OPEN foo cur    FETCH foo cur INTO foo rec    IF foo cur FOUND THEN            END IF    CLOSE foo cur  EXCEPTION   WHEN OTHERS THEN     CLOSE foo cur      RAISE  END     admittedly this is more code  but it doesn t use EXCEPTIONs as flow-control which  having learnt most of my PL SQL from Steve Feuerstein s PL SQL Programming book  I believe to be a good thing   Whether this is faster or not I don t know  I do very little PL SQL nowadays

User · Answer

Stephen Darlington makes a very good point  and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following   begin    for i in 2    10000 loop     insert into t  NEEDED FIELD  cond  values  i  10     end loop  end    Then re-run the benchmarks   I had to reduce the loop counts to 5000 to get reasonable times    declare   otherVar  number    cnt number  begin   for i in 1    5000 loop      select count    into cnt from t where cond   0        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   0       else        otherVar    0       end if     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 04 34  declare   otherVar  number  begin   for i in 1    5000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   0       exception        when no data found then          otherVar    0       end     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 02 10   The method with the exception is now more than twice as fast   So  for almost all cases the method   SELECT NEEDED FIELD INTO var WHERE condition  EXCEPTION WHEN NO DATA FOUND       is the way to go   It will give correct results and is generally the fastest

User · Answer

Stephen Darlington makes a very good point  and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following   begin    for i in 2    10000 loop     insert into t  NEEDED FIELD  cond  values  i  10     end loop  end    Then re-run the benchmarks   I had to reduce the loop counts to 5000 to get reasonable times    declare   otherVar  number    cnt number  begin   for i in 1    5000 loop      select count    into cnt from t where cond   0        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   0       else        otherVar    0       end if     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 04 34  declare   otherVar  number  begin   for i in 1    5000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   0       exception        when no data found then          otherVar    0       end     end loop  end     PL SQL procedure successfully completed   Elapsed  00 00 02 10   The method with the exception is now more than twice as fast   So  for almost all cases the method   SELECT NEEDED FIELD INTO var WHERE condition  EXCEPTION WHEN NO DATA FOUND       is the way to go   It will give correct results and is generally the fastest

User · Answer

I would not use an explicit cursor to do this   Steve F  no longer advises people to use explicit cursors when an implicit cursor could be used    The method with count    is unsafe   If another session deletes the row that met the condition after the line with the count     and before the line with the select     into  the code will throw an exception that will not get handled   The second version from the original post does not have this problem  and it is generally preferred   That said  there is a minor overhead using the exception  and if you are 100  sure the data will not change  you can use the count     but I recommend against it   I ran these benchmarks on Oracle 10 2 0 1 on 32 bit Windows   I am only looking at elapsed time   There are other test harnesses that can give more details  such as latch counts and memory used    SQL gt create table t  NEEDED FIELD number  COND number        Table created    SQL gt insert into t  NEEDED FIELD  cond  values  1  0        1 row created    declare   otherVar  number    cnt number  begin   for i in 1    50000 loop      select count    into cnt from t where cond   1        if  cnt   1  then        select NEEDED FIELD INTO otherVar from t where cond   1       else        otherVar    0       end if     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 02 70   declare   otherVar  number  begin   for i in 1    50000 loop      begin        select NEEDED FIELD INTO otherVar from t where cond   1       exception        when no data found then          otherVar    0       end     end loop  end         PL SQL procedure successfully completed       Elapsed  00 00 03 06

User · Answer

If it s important you really need to benchmark both options   Having said that  I have always used the exception method  the reasoning being it s better to only hit the database once

User · Answer

The first  excellent  answer stated -  The method with count   is unsafe  If another session deletes the row that met the condition after the line with the count     and before the line with the select     into  the code will throw an exception that will not get handled   Not so   Within a given logical Unit of Work Oracle is totally consistent   Even if someone commits the delete of the row between a count and a select Oracle will  for the active session  obtain the data from the logs   If it cannot  you will get a  snapshot too old  error

[sql] Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

Examples related to sql

Examples related to oracle

Examples related to exception

Examples related to plsql