?
Solved

No Data found exception or catching through cursor%notfound.

Posted on 2012-08-13
9
Medium Priority
?
1,115 Views
Last Modified: 2012-08-21
I have the below code.

In this some action is done on no data found exception

I believe the same can be done using a named cursor.
and we can do the action when on Cursor%notfound

My Question
1) Which one is better, what is the advantage / disadvantage of each method.
2)Is there any other method which is efficient.


 BEGIN
          SELECT ENTRY_GATE_ID, YARD_ID
            INTO :MOVES_IN.ENTRY_GATE_ID, :MOVES_IN.YARD_ID
            FROM ENTRY_GATES
           WHERE TERMINAL_ID = :MOVES_IN.TERMINAL_ID
             AND YARD_ID = 'PRIMARY'
             AND ROWNUM = 1;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
          <Some action>
0
Comment
Question by:sakthikumar
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 1336 total points
ID: 38287351
A "SELECT INTO" only returns, at most, 1 row.
A cursor can return many rows.

If you only need select the 1 row of values once during the script - a SELECT INTO is as good as any method (possibly better and easier to code).

If you need multiple rows from the table - use a cursor.  A SELECT INTO would incur the overhead of parse and execute the SQL many times, the cursor does not.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38287357
The select creates an inplicit cursor.  There shouldn't really be much of a difference creating an explicit one.

With things like this, just set up a simple test case and try both.
0
 

Author Comment

by:sakthikumar
ID: 38290608
My concern is when we use an explicit cursor. We can avoid an exception.

Declare

Cursor c1 is
SELECT ENTRY_GATE_ID, YARD_ID
                        FROM ENTRY_GATES
           WHERE TERMINAL_ID = :MOVES_IN.TERMINAL_ID
             AND YARD_ID = 'PRIMARY'
             AND ROWNUM = 1;

yard number;
entry number;
Begin
open c1;
fetch c1 into entry,number;
            if c1%notfound then
               <Action>
           End if;
End;
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Expert Comment

by:lwadwell
ID: 38290630
Sure ... try it yourself.
set serverout ON
DECLARE
    CURSOR c1 IS
        SELECT 1 AS x
          FROM DUAL
         WHERE ROWNUM < 1;

    v_x number;
BEGIN
    OPEN c1;
    FETCH c1 INTO v_x;
    IF c1%NOTFOUND THEN 
        DBMS_OUTPUT.PUT_LINE('No data found');
    END IF;
END;

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38291666
>>My concern is when we use an explicit cursor. We can avoid an exception.

You can catch the exception either way.

Using the example above with the explicit cursor, here is the same code with an implicit cursor

declare
	v_x number;
begin
--do some stuff

	--nested pl/sql block
	begin
        SELECT 1 into v_x 
          FROM DUAL
         WHERE ROWNUM < 1;

	exception when no_data_found then
		DBMS_OUTPUT.PUT_LINE('No data found');
	end;
--do more stuff
end;
/

Open in new window

0
 

Author Comment

by:sakthikumar
ID: 38299151
Please dont mistake me.

My doubt is ,
when using an explicit cursor, we are avoiding an exception, and catch it before any exception.

which one is good programming?
0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 1336 total points
ID: 38299164
Avoiding exceptions is good programming.  
Using exceptions to help you is even better programming.

Why are you worried about exceptions? ... they are there to help you - they are not bad things (except for "when others" which, in many cases, indicates something happened that was allowed for).
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38299194
Here is the example code of mine AND @slightwv's ... please run it yourself.
set serverout ON
DECLARE
    CURSOR c1 IS
        SELECT 1 AS x
          FROM DUAL
         WHERE ROWNUM < 1;

    v_x number;
BEGIN
    OPEN c1;
    FETCH c1 INTO v_x;
    IF c1%NOTFOUND THEN 
        DBMS_OUTPUT.PUT_LINE('Explicit Cursor: No data found ... carry on');
    END IF;
    
    BEGIN
        SELECT 1 INTO v_x 
          FROM DUAL
         WHERE ROWNUM < 1;
    EXCEPTION WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('Implicit Cursor: No data found ... carry on');
    END;

    FETCH c1 INTO v_x;
    IF c1%NOTFOUND THEN 
        DBMS_OUTPUT.PUT_LINE('Explicit Cursor: No data is still not found ... carry on');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('End of Example');

EXCEPTION WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('The MAIN no_data_found exception was triggered');
END;

Open in new window


The explicit cursor do not trigger any exception.  The c1%NOTFOUND test catches the no data found condition and handles it - this is good programming.
The implicit cursor uses a deliberately coded localised exception to catch the no found condition and handles it - this is good programming.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 38300175
>>we are avoiding an exception, and catch it before any exception.

You aren't avoiding anything.  A cursor is just a 'potential' result set.  With explicit cursors, opening them just prepares the result set for fetching.  Nothing more.  The result set might have rows, it might not.  You don't know until you preform the first fecth.

After you fectch, then you can determine if you got a row or not.  The implicit cursor does all the same things.  It just does them for you.

Like posted above:  Both are perfectly valid.  Which method you use is pretty much up to you.

Personally, I use implicit cursors pretty much everywhere.  It's just less code and, IMHO, easier to read/follow the logic.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question