Skip to Main Content
  • Questions

  • IMPLICIT CURSOR ATTRIBUTE SQL%NOTFOUND NOT WORKING

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, PRS.

Asked: February 22, 2003 - 11:42 am UTC

Last updated: July 04, 2018 - 2:49 pm UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Hi Tom,
I am using implicit cursor attributes SQL%NOTFOUND for the below
PL/SQL block. But it raises NO_DATA_FOUND exception. It works fine
for update and delete statement as they are not going to raise the
NO_DATA_FOUND exception.

Example.

declare
l_table_name varchar2(30);
begin
select table_name
into l_table_name
from dba_tables
where table_name = 'XXXX';
if sql%notfound
then
dbms_output.put_line('Table not found');
else
dbms_output.put_line('Table found');
end if;
end;
/

Error:
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


I am handling the exception SQL%NOTFOUND. But it raise NO_DATA_FOUND exception. I can write the begin, exception, end block
to handle the exception. But why this implicit cursor attributes does not work for SELECT statement? ORACLE manuals says it should work. ORACLE manual for PL/SQL developer's guide says that. Can you please shed some light on this?

Your help is appreciated.

Thanks,
PRS

and Tom said...

it is working exactly as designed and documented.


A select into does one of three things:

o returns a single row successfully.
o throws TOO_MANY_ROWS if you get more than one row back
o throws NO_DATA_FOUND if you get less than one row back.

See -- it "works" for select into:

ps$tkyte@ORA920> declare
2 data varchar2(5);
3 begin
4 select dummy into data from dual where 1=0;
5
6 if ( sql%found ) then
7 dbms_output.put_line( '1) Found!!' );
8 elsif ( sql%notfound ) then
9 dbms_output.put_line( '1) Not Found!!' );
10 end if;
11 exception
12 when no_data_found then
13 if ( sql%found ) then
14 dbms_output.put_line( '2) Found!!' );
15 elsif ( sql%notfound ) then
16 dbms_output.put_line( '2) Not Found!!' );
17 end if;
18 end;
19 /
2) Not Found!!

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 data varchar2(5);
3 begin
4 select dummy into data from dual where 1=1;
5
6 if ( sql%found ) then
7 dbms_output.put_line( '1) Found!!' );
8 elsif ( sql%notfound ) then
9 dbms_output.put_line( '1) Not Found!!' );
10 end if;
11 exception
12 when no_data_found then
13 if ( sql%found ) then
14 dbms_output.put_line( '2) Found!!' );
15 elsif ( sql%notfound ) then
16 dbms_output.put_line( '2) Not Found!!' );
17 end if;
18 end;
19 /
1) Found!!

PL/SQL procedure successfully completed.



you can "use it" but select into will ALWAYS through those exceptions -- as they are in fact ERRORS.

<quote where=just a page or so down from where you stopped reading>

If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check %NOTFOUND on the next line or not. Consider the following example:

BEGIN ... SELECT sal INTO my_sal FROM emp WHERE empno = my_empno; -- might raise NO_DATA_FOUND IF SQL%NOTFOUND THEN -- condition tested only when false ... -- this action is never taken END IF;

The check is useless because the IF condition is tested only when %NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution stops and control transfers to the exception-handling part of the block.
</quote>

Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 22, 2003 - 8:57 pm UTC


handle data not found without using exception

Sean, July 14, 2003 - 11:26 am UTC

Hi Tom,

We want to check the existence of the record in a table. If it exists, we do something and if it does not exist, we do something else.

If we use

select empno into v_empno from emp where ename = ‘JOHN’;

we have to use exception to handle the transaction if the record does not exist. Since it is a normal situation even the records does not exist, instead we use

select count(empno) into v_count from emp
where ename = 'JOHN;


It works fine except that sometimes it takes time to count if there are a lot of records. My question is how to check the existence of record and do some transaction if the record does not exist without using exception or count. Here is our sample code:

create or replace procedure p1
is

v_count number;

begin
-- we want to replace count
select count(empno) into v_count from emp
where ename = 'JOHN';

-- Real transsaction is more complicated than this sample code.
if v_count > 0
then dbms_output.put_line('JOHN exists');
else dbms_output.put_line('JOHN does not exist') ;
end if;

end;
/


Thanks so much for your help.

Sean


Tom Kyte
July 15, 2003 - 12:45 am UTC



select count(*) into l_cnt
from dual
where EXISTS ( select null from emp where ename = 'JOHN' );

will return 0 if no rows exist, 1 if at least one row does.



A reader, October 07, 2003 - 3:47 pm UTC


We use ROWNUM to do this

sonali, February 26, 2004 - 10:40 am UTC

select count(*) into into l_cnt
from emp
where ename = 'JOHN' and rownum=1;

will return 0 if no rows exist, 1 if at least one row does.

Instead of this code -

select count(*) into l_cnt
from dual
where EXISTS ( select null from emp where ename = 'JOHN' );

will return 0 if no rows exist, 1 if at least one row does.

Which one is better performance wise.. with ROWNUM ?, yours with EXISTS or the exception NO_DATA_FOUND.. I am always confused as to which one to use. Most of our situations are same as in this thread...Like if count(*) >0 then do something else do something else.
Thanks




Tom Kyte
February 26, 2004 - 1:38 pm UTC

rownum is the least resource intensive of all.

But with rownum I will do that query twice right ?

sonali, February 27, 2004 - 9:33 am UTC

Sorry, but I am still not clear on this one..
In this example I do rownum=1; to check if the row exists and then if it exists I select it again so I will not get no_data_found error.. but if the row exists I am doing the same select twice where as if I use NO_DATA_FOUND exception the select will be done only once.. so wouldn't NO_DATA_FOUND exception use be more efficient than the rownum query check ?
Also if the table has large data wouldn't doing the where clause twice be more expensive with rownum query.

/*with rownum use*/
select count(1) into inCounter
from ETHolidayCal
where Holiday_Set=holidaySet
and tlDate between Holiday_Beg_Date and Holiday_End_Date
and rownum=1;


if fromTCAdd=10 and inCounter>0 then
select Holiday_Pay_Code
into dPayCode
from ETHolidayCal
where Holiday_Set=holidaySet
and tlDate between Holiday_Beg_Date and Holiday_End_Date and rownum=1;

else
goto EndIt; -- or some other code here
end if;

/*with no_data_found use*/
if fromTCAdd=10 and inCounter>0 then
select Holiday_Pay_Code
into dPayCode
from ETHolidayCal
where Holiday_Set=holidaySet
and tlDate between Holiday_Beg_Date and Holiday_End_Date and rownum=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
goto EndIt; -- or some other code here

end if;

What do you think ?
Thanks

Tom Kyte
February 27, 2004 - 10:05 am UTC

that is an entirely different question than you asked above.

avoiding the count all together is by far the most efficient and correct way to do it.

I do not "count" to see if I should do something.

I just try to do it and if there is no data to process, so be it.


just code


begin
select * into ... from t where ...
process record.
exception
when no_data_found then
do not process record
end;


and be done with it.

Regarding SQL%ISOPEN

Giridhar, July 17, 2005 - 2:15 am UTC

Hi Tom,
Good Morning. I am going through steven feurestein's book on pl/sql programming. In that book its mentioned that "SQL%ISOPEN always returns FALSE for implicit cursors, because oracle opens and closes implicit cursors automatically". If that is the case, why do we really need SQL%ISOPEN implicit cursor attribute? Or is this attribute useful?
Please clarity.
Thanks,
Giridhar Kodakalla

Tom Kyte
July 17, 2005 - 9:20 am UTC

I would consider it being there as a completeness thing.

All cursors have attributes, isopen and so on. It is there because in general all cursors "have it"

Since the implicit cursor is just a cursor after all, it has that attribute.

It's usefulness in real life? Probably none that I can think of.

Alex, February 17, 2006 - 5:21 pm UTC

Hello,

I'm having an issue with nested blocks that can possible return no rows from my singleton selects. It's being caught in the wrong place. For example:

begin
select *
into a_row
from a
where id = p_id;

begin
select *
into b_row
from b
where name = 'Bob';
exception when no_data_found then
b_row := null; <---Exception is being caught here from query 1
end;

...bunch of processing here if query 1 returns data but regardless if query does or not.......

exception when no_data_found then....
continue

It could be because it's late on a Friday but I'm tricked for the moment. Thank you.

Tom Kyte
February 18, 2006 - 8:19 am UTC 

No, it is not.  

something else is happening in your code - what you say cannot be happening.


ops$tkyte@ORA9IR2> declare
  2          a_row dual%rowtype;
  3          b_row dual%rowtype;
  4  begin
  5     select *
  6     into a_row
  7     from dual
  8     where 1=0;
  9
 10     begin
 11      select *
 12      into b_row
 13      from dual
 14      where 1=0;
 15     exception when no_data_found then
 16        dbms_output.put_line( 'caught B here' );
 17     end;
 18  end;
 19  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5





ops$tkyte@ORA9IR2> declare
  2          a_row dual%rowtype;
  3          b_row dual%rowtype;
  4  begin
  5     select *
  6     into a_row
  7     from dual
  8     where 1=0;
  9
 10     begin
 11      select *
 12      into b_row
 13      from dual
 14      where 1=0;
 15     exception when no_data_found then
 16        dbms_output.put_line( 'caught B here' );
 17     end;
 18
 19          -- other code here.
 20
 21  exception when no_data_found then
 22        dbms_output.put_line( 'caught A here' );
 23  end;
 24  /
caught A here

PL/SQL procedure successfully completed.


that first select into is not being caught where you say it is OR your code does not look like your example

Alex, February 18, 2006 - 3:18 pm UTC

That's interesting. I may have found an enormous bug in toad's procedure editor then. When I step through the code, it's dropping into the first exception block.

I will try using dbms_output via sqlplus like you did and see what happens.

Alex, February 21, 2006 - 9:50 am UTC

Ok I confirmed it. TOAD version 8.5.3.2 is screwed up. I ran my procedure from sqlplus and it was being caught in the correct spots. Thanks for setting me straight.

Implicit Cursor

Kandy Train, October 11, 2006 - 10:59 am UTC

Hi Tom,

Why is the following procedure fails??

set serveroutput on
begin
if (select count(*) from dual) = 0 then
dbms_output.put_line('Kandy');
else
dbms_output.put_line('Train');
end if;
end;
/

I get a
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: error.

I thought I can use an implicit cursor like this.

Any ideas??

Tom Kyte
October 11, 2006 - 4:00 pm UTC 

because it is not valid plsql, might have flown in t-sql, but you are programing in a better language now :)


  1  begin
  2     for x in
  3    (select case when count(*) = 0 then 'Kandy' else 'Train' end txt from dual)
  4    loop
  5        dbms_output.put_line( x.txt );
  6    end loop;
  7* end;
ops$tkyte%ORA10GR2> /
Train

PL/SQL procedure successfully completed.

I understood your answers here but....

GEE, December 13, 2007 - 6:00 pm UTC

If I am trapping all other no data found possibilities in my code.

IF <collection>.FIRST and <collection>.LAST are defined how could I get a no data found?

My code goes through 2,139,226 records then raises a no data found at the line noted below.

CREATE OR REPLACE PROCEDURE INCADM.ISP_STYLE_FLOOD_DOWNLOAD AS
  
  l_path        VARCHAR2(30) := '/u3/nex_retek/data/retek_utl';
  l_file        VARCHAR2(50);
  l_item        item_master.item%TYPE;
  l_upc  item_master.item%TYPE;
  l_loc  item_loc_soh.loc%TYPE;
  l_dept item_master.dept%TYPE;
  l_desc        item_master.short_desc%TYPE;
  l_retail item_loc.unit_retail%TYPE;
  l_primary     item_loc.loc%type;
  l_zone        price_zone_group_store.zone_id%type;
  output_file   utl_file.file_type;
  l_Counter     NUMBER := 0;
  l_retCode     INTEGER;

CURSOR GetZoneCur
    IS
SELECT DISTINCT zgrp.zone_id,
       fld.store
  FROM nex_isp_flood_store     fld, 
       price_zone_group_store zgrp 
 WHERE fld.style_flood = 'Y'
   AND fld.style_flood_processed IS NULL
   AND TRUNC( fld.flood_date ) = TRUNC( SYSDATE )
   AND zgrp.store = fld.store
   AND zgrp.primary_ind = 'Y'
   AND zgrp.zone_group_id = 2;

CURSOR GetZoneItems ( p_zid NUMBER )
    IS
SELECT DISTINCT il.item
  FROM item_master im,
          item_loc il
 WHERE im.item = il.item
   AND im.item_level = im.tran_level
   AND im.item_number_type = 'ITEM'
   AND im.status = 'A'
   AND il.loc IN ( SELECT store FROM price_zone_group_store WHERE zone_id = p_zid AND zone_group_id = 2 );
   
TYPE GetZoneItemsRec  IS RECORD ( item item_loc.loc%type );
TYPE t_zone_items_tbl IS TABLE OF GetZoneItemsRec INDEX BY BINARY_INTEGER;

l_zone_items_tbl    t_zone_items_tbl;
 

CURSOR GetStyleFloodCur
    IS
SELECT 
    <snip>
  FROM item_master            im,
       item_loc               il
 WHERE il.loc    = l_primary
   AND il.item   = l_item
   AND im.item_level = im.tran_level
   AND im.status = 'A'
   AND im.item_number_type = 'ITEM'
   AND im.item   = il.item;
   
l_StyleFloodCur_rec    GetStyleFloodCur%ROWTYPE;

FUNCTION GetRetailFromNonPrimary
RETURN INTEGER
IS
    l_SecondaryFound INTEGER;
CURSOR NonPrimarySearch
    IS
SELECT <snip>;
    
    NonPrimarySearchCur NonPrimarySearch%ROWTYPE;
       
BEGIN

      OPEN NonPrimarySearch;
     FETCH NonPrimarySearch INTO NonPrimarySearchCur;
     
     IF NonPrimarySearch%NOTFOUND THEN
        <snip>

     ELSE
        <snip>
        l_SecondaryFound := 1;
        l_Counter := l_Counter + 1; 
     END IF;             
     
    CLOSE NonPrimarySearch;   
     
    RETURN l_secondaryFound;

END GetRetailFromNonPrimary;

BEGIN

        OPEN GetZoneCur;
      LOOP
       FETCH GetZoneCur INTO l_zone, l_primary;
       IF GetZoneCur%NOTFOUND THEN
                <snip>
               CLOSE GetZoneCur;
               EXIT;
       ELSE
       
        l_file := 'style_dat.'||l_primary||'.'||TO_CHAR(SYSDATE,'YYYYMMDDHHMI')||'.fld';
        output_file := utl_file.fopen( l_path, l_file, 'W',32000);
           OPEN GetZoneItems( l_zone );
   FETCH GetZoneItems BULK COLLECT INTO l_zone_items_tbl;
          CLOSE GetZoneItems;
          
              <snip>
FOR idx IN l_zone_items_tbl.FIRST .. l_zone_items_tbl.LAST LOOP
         
 <b>--error raises on line below</b>
l_item := l_zone_items_tbl( idx ).item; 
                
                 OPEN GetStyleFloodCur;
                FETCH GetStyleFloodCur INTO l_StyleFloodCur_rec;
                 
                -- If item is not found at primary check other stores
                IF GetStyleFloodCur%NOTFOUND THEN
                     -- Return code of 1 is success 0 is failure
              l_retCode := GetRetailFromNonPrimary; 
                     GOTO DontWriteToFile;
                END IF;
                <snip wrote to file>
                
                l_Counter := l_Counter + 1;
                <<DontWriteToFile>>
                IF l_Counter > 0 AND MOD( l_Counter , 1000 ) = 0 THEN
                     <snip logged the count>

                  END IF;
                  
                  CLOSE GetStyleFloodCur;
          END LOOP;
          
              utl_file.fclose( output_file );
              
     END IF;
  END LOOP;
EXCEPTION
         WHEN OTHERS THEN
           errors.write_errors( sysdate,
                         'isp_style_flood_download',
                         'Exception',
                         SQLCODE||' '||SQLERRM||' for zone '||l_zone,
                         'Current item is '||l_item||' the item count is '||l_Counter,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL );
         utl_file.fclose( output_file ); 
END ISP_STYLE_FLOOD_DOWNLOAD;

Tom Kyte
December 14, 2007 - 12:55 pm UTC

I refuse, utterly and complete refuse to look at code that has

when others
NO RAISE statement.



that is a bug, period.


if you comment that out (the ENTIRE EXCEPTION BLOCK) you'll get an error message back that tells you precisely what line the no data found is being raised on - that will be EXTREMELY useful.


and never code a when others that is NOT followed by RAISE; or RAISE_APPLICATION_ERROR.

http://www.google.com/search?q=site%3Atkyte.blogspot.com+when+others+then+null

I respect that

cg, December 17, 2007 - 12:28 pm UTC

Sorry I know how you feel about when others now.
I did take out the when others BEFORE I posted and I know where the line is. That was not my question.

My question was why was that line raising the error?

I found out about collections and subscripts and the fact that you have to check that the subscript exists prior to referencing it.

Thanks for sticking to your beliefs! But not my design, my application uses that all over the place. They just log the error.
Tom Kyte
December 17, 2007 - 3:24 pm UTC

why was WHAT line raise the error.


if they just log the error, they have a major priority one bug in all of their developed code, all of it.

Their code is less then useless, it is harmful.

When others...

A reader, December 17, 2007 - 10:36 pm UTC

This was in my original post
.....
FOR idx IN l_zone_items_tbl.FIRST .. l_zone_items_tbl.LAST LOOP

--error raises on line below
l_item := l_zone_items_tbl( idx ).item;
......

About the when others when we do log the errors they email them to people of concern. But there is ORACLE code that has

WHEN OTHERS
NULL;

Right now in a multi million dollar Oracle product.
In PRO c code for the batches and everything.
So Oracle Retail has the harmful code. Go figure.
Tom Kyte
December 18, 2007 - 1:27 pm UTC

I don't know why you think I'd read your entire large set of code looking for a comment that says "error raises on line below"

if they just log the error, they have a major priority one bug in all of their developed code, all of it.

Do you think I should pull punches and not make the statement that your code has a bug?

If there are when others then null in code you can read - that would be (in my humble opinion) a bug - one that should be filed. Obviously - it matters NOT if the code were executed or not. So why bother executing it at all. Please file one (I don't have the Oracle Retail code - not sure what product you are talking about, don't use it myself..)



Your code raises a no data found because you assume that every IDX value in between first and last has a value.

and obviously - it does not.

consider:

ops$tkyte%ORA10GR2> declare
  2      type array is table of number index by binary_integer;
  3      l_data array;
  4  begin
  5      l_data(1) := 1;
  6      l_data(3) := 3;
  7
  8      for idx in l_data.first .. l_data.last
  9      loop
 10          dbms_output.put_line( 'l_data('||idx||') = ' || l_data(idx) );
 11      end loop;
 12  end;
 13  /
l_data(1) = 1
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 10


So, what you have is a sparse array (you can verify by last-first+1 = count)

ops$tkyte%ORA10GR2> declare
  2      type array is table of number index by binary_integer;
  3      l_data array;
  4      l_idx  number;
  5  begin
  6      l_data(1) := 1;
  7      l_data(3) := 3;
  8
  9          dbms_output.put_line( 'Contigous count would be ' || (l_data.last-l_data.first+1)  );
 10          dbms_output.put_line( 'Actual    count is       ' || (l_data.count) );
 11
 12      l_idx := l_data.first;
 13      while (l_idx is not null)
 14      loop
 15          dbms_output.put_line( 'l_data('||l_idx||') = ' || l_data(l_idx) );
 16          l_idx := l_data.next(l_idx);
 17      end loop;
 18  end;
 19  /
Contigous count would be 3
Actual    count is       2
l_data(1) = 1
l_data(3) = 3

PL/SQL procedure successfully completed.

It's first an ignorance from Oracle

SM, December 18, 2007 - 2:33 am UTC

It all started from the documentation. Just see what a junior programmer would do. Just like thousands of programmers even working at Oracle offices all around the World, they would never know this "when others then null;" issue before they are warned against it.
It's very easy for somebody with an exposure to shout at them just because they are new to development, for which the only source of knowledge is the documentation that is provided by Oracle itself.

My question is why not modify the Oracle documentation and share this knowledge accross with these best practices - I am not asking much I guess, unless some salesperson predicts break-down of the volume of consulting services provided by Oracle!
Tom Kyte
December 18, 2007 - 1:51 pm UTC

you are showing some signs of "paranoia" here - break down of the volume of consulting services. hmmm..


Anyway - come on, this is programming 101 stuff here people. This is NOT an Oracle thing. Exceptions happen in most current languages today.


This is a programming thing, something that should be taught to programmers in school, in classes, in the programming books they read, by their mentors.

This is not an Oracle thing
This is a programming thing, period.

And the exception handling I see in Java, C++, PL/SQL - it quite simply

scares
me
to
death


I don't care WHOSE code it is in - I'll flame on regardless. I've had these discussions with programmers all over the place. They are doing it wrong.

IF (you have a when others Not followed by RAISE or RAISE_APPLICATION_ERROR (if you do not re-raise the error)
THEN
    you have a bug
END IF;


I don't care who you are, how long you've been writing code.

I've written about this until I'm blue in the face.

Flame on every time I see it now, Just scares me to death.

Yes, when I find it in developed Oracle code I raise a fuss, I've made more than one person "annoyed" at me - but the only way I seem to be able to get the point across is "hey - you have a bug, yes you do, nothing you say will change that fact, face it, you have a bug - a bug - a big old bug"


even if you have code like this:

begin
   execute immediate 'drop table t;';
exception
   when others the null; -- we can ignore this because if the table
                         -- isn't there we don't care
end;


a lot of people would say "that is safe"

I would say "you are lazy, and it is not safe, I can make your drop table fail for hundreds of reasons - none of them to do with "table or view does not exist"


That code should

a) verify table T is a table (query dictionary)
b) drop it
c) upon hitting an error - feel free to log is BUT YOU BETTER RE-RAISE IT

why a) - because we are expecting to drop a table, if T is a view - it is NOT what we were expecting, un-expected stuff means "STOP". If T is anything other than a table - STOP.

why c) because you might want to log the error, for posterity - but you cannot deal with it (exception blocks that do not re-raise an exception mean YOU DEALT WITH THE ERROR, THE ERROR NEVER HAPPENED). You have to re-raise it.

And everyone up the call tree must do the same - if the catch it, it is unlikely they can "fix it", so they best either a) ignore it (do not catch it), b) catch it, do whatever, RE-RAISE IT (or raise SOMETHING)



http://www.google.com/search?q=site%3Atkyte.blogspot.com+when+others+then+null


One of my favorite 11g new features:

ops$tkyte%ORA11GR1> create table t( x varchar2(4000) );

Table created.

ops$tkyte%ORA11GR1> create or replace
  2  procedure maintain_t
  3  ( p_str in varchar2 )
  4  as
  5  begin
  6    insert into t
  7    ( x ) values
  8    ( p_str );
  9  exception
 10    when others
 11    then
 12      -- call some log_error() routine
 13      null;
 14  end;
 15  /

Procedure created.

ops$tkyte%ORA11GR1> exec maintain_t( rpad( 'x', 4001, 'x' ) );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

no rows selected

ops$tkyte%ORA11GR1> alter procedure maintain_t compile
  2  PLSQL_Warnings = 'enable:all'
  3  reuse settings
  4  /

SP2-0805: Procedure altered with compilation warnings

ops$tkyte%ORA11GR1> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8      PLW-06009: procedure "MAINTAIN_T" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR


I can now use the compiler to more quickly correct code. Now when a developer says to me "sometimes Oracle doesn't save my changes" - I will recompile their code with warnings set high and find all of these and tell them to add raise to it.

it will become immediately obvious where their bug is.

NO_DATA_FOUND vs SQL%NOTFOUND

just reader, April 25, 2009 - 5:27 pm UTC

Tom,

I had request to create stored proc that with following conditions:
select x, y, z
into :x, :y, :z
from t
where a = :a
and b = :b

IF NO_DATA_FOUND THEN

select x, y, z
into :x, :y, :z
from t
where a = :a
and c = 'Y'

IF NO_DATA_FOUND THEN (catch all)
select x, y, z
into :x, :y, :z
from t
where a = 'A'
and b = 'B'

So, I created this proc you can see below. Could you please advise if there is a better way to archive same result. What if there were more IN parameters I don't think it is correct keep creating NO_DATA_FOUND exceptions and "IF(SQL%NOTFOUND)" doesn't really addresses this issue either.

Grateful

CREATE OR REPLACE PROCEDURE p (
p_a_in IN t.a%type,
p_b_in IN t.b%type,
p_x_out OUT t.x%type,
p_y_out OUT t.y%type,
p_z_out OUT t.z%type )
IS

BEGIN

SELECT x, y, z
INTO p_x_out, p_y_out, p_z_out
FROM t
WHERE a = p_a_in
AND b = p_b_in;

EXCEPTION
WHEN NO_DATA_FOUND THEN

BEGIN
SELECT x, y, z
INTO p_x_out, p_y_out, p_z_out
FROM t
WHERE a = p_a_in
AND c = 'Y';

EXCEPTION
WHEN NO_DATA_FOUND THEN

SELECT x, y, z
INTO p_x_out, p_y_out, p_z_out
FROM t
WHERE a = 'A'
AND b = 'B';
END;

END p;


Tom Kyte
April 27, 2009 - 1:50 pm UTC

... don't think it is correct keep creating NO_DATA_FOUND exceptions and ...

why do you not think that?

We can either code 1 query that gets (up to) all three possible rows - sorts them in order of preference and returns just the first one....

Or, we can use nested exceptions.


If you usually expect to find the row (the exception happens infrequently), go with what you have. If you infrequently expect to find the row in the first or second query, code it as a single query.

ops$tkyte%ORA10GR2> select *
  2    from (select 1 oc, x, y, z from t where a = :a and b = :b
  3          union all
  4          select 2 oc, x, y, z from t where a = :a and c = 'Y'
  5                  union all
  6          select 3 oc, x, y, z from t where a = 'A' and b = 'B'
  7                  order by 1 )
  8   where rownum = 1;

no rows selected

Raju, July 04, 2018 - 6:11 am UTC

In all data bases when ever we are performing DML operations in Impliciti cursor then only impliciti cursor attribitues working
other wise showing above problem because in impliciti cursors we con not perform ddl,dql,dcl,tcl command
Chris Saxon
July 04, 2018 - 2:49 pm UTC

What exactly are you doing that's causing sql%notfound to "not work"?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library