Home » SQL & PL/SQL » SQL & PL/SQL » when no_data_found (Oracle 9.2.0.3)
when no_data_found [message #405713] Fri, 29 May 2009 03:48 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
declare
v_empid number;
v_dept_id number;
Begin
Select empid into v_empid from emp where dept_id = v_dept_id;
exception
when NO_DATA_FOUND then
Flag :=0
end;
if flag <> 0 then
< proceed ...do some action >
else
null;
end if;
end;


I want to know if this is the correct way of coding. Thanks
Re: when no_data_found [message #405714 is a reply to message #405713] Fri, 29 May 2009 03:58 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Did u checked for any Errors??
Re: when no_data_found [message #405717 is a reply to message #405713] Fri, 29 May 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it is not the correct way.
The correct way is to first indent the code, then have a code that compile and in the end:
declare
  v_empid number;
  v_dept_id number;
Begin
  Select empid into v_empid from emp where dept_id = v_dept_id;
  < proceed ...do some action >
exception
  when NO_DATA_FOUND then null;
end;

Of course you should also have rules about when to use UPPER case, when to use lower case, when to use Initial Capitals and when to use MiXed CaSe.

Regards
Michel

Re: when no_data_found [message #405718 is a reply to message #405713] Fri, 29 May 2009 04:04 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Did u mean like below by any chance, because the design that posted would result errors

DECLARE
   v_empid     NUMBER;
   v_dept_id   NUMBER;
BEGIN
   SELECT empno
     INTO v_empid
     FROM emp
    WHERE dept_id = v_dept_id;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      flag := 0;

      IF flag <> 0
      THEN
         < proceed ...do some action >
      ELSE
         < proceed ...do some action >
      END IF;
END;
Re: when no_data_found [message #405721 is a reply to message #405713] Fri, 29 May 2009 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a way of coding it.
You are making the assumption that you will never get two rows from EMP for the same Dept_id - you might want to consider trapping the Too_Many_Rows exception as well.

I would be more likely to code it like this, assuming that no rows or too many rows for the specified Id wasn't a condition that needed handling or reporting on:
DECLARE
   v_empid     NUMBER;
   v_dept_id   NUMBER;
BEGIN
   SELECT empno
   INTO   v_empid
   FROM   emp
   WHERE  dept_id = v_dept_id;

   <do some action>

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      null; 
   WHEN TOO_MANY_ROWS THEN
      null;
END;

Re: when no_data_found [message #405722 is a reply to message #405717] Fri, 29 May 2009 04:10 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
When no data found I want to set flag as 0 and pass it main block and based on the condition whther to process or not to process.Would it be wrong if I write the code this way?

DECLARE
  v_empid number;
  v_dept_id number;
  flag number;
BEGIN 
<......... >
    BEGIN
       SELECT empid INTO v_empid FROM emp 
              WHERE dept_id =   v_dept_id;
      
    EXCEPTION
       WHEN NO_DATA_FOUND THEN null;
       flag:=0;
    END;
    IF flag <> 0 THEN
       < proceed ...do some action >
    ELSE 
      NULL;
    END IF;
END;

[Updated on: Fri, 29 May 2009 04:19]

Report message to a moderator

Re: when no_data_found [message #405735 is a reply to message #405722] Fri, 29 May 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You ask us how you should write it.
We answer how you should write it.
If you want to write other way, you can if it works as required.

Regards
Michel
Re: when no_data_found [message #405739 is a reply to message #405722] Fri, 29 May 2009 04:46 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
DECLARE
  v_empid number;
  v_dept_id number;
  flag number;
BEGIN 
<......... >
    BEGIN
       SELECT empid INTO v_empid FROM emp 
              WHERE dept_id =   v_dept_id;
       < proceed ...do some action >
    EXCEPTION
       WHEN NO_DATA_FOUND THEN null;
     
    END;
   
END;


can this be alternative to
DECLARE
  v_empid number;
  v_dept_id number;
  flag number;
BEGIN 
<......... >
    BEGIN
       SELECT empid INTO v_empid FROM emp 
              WHERE dept_id =   v_dept_id;
      
    EXCEPTION
       WHEN NO_DATA_FOUND THEN null;
       flag:=0;
    END;
    IF flag <> 0 THEN
       < proceed ...do some action >
    ELSE 
      NULL;
    END IF;
END;

or is it a bad coding approach?
Re: when no_data_found [message #405749 is a reply to message #405739] Fri, 29 May 2009 05:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
prachij593 wrote on Fri, 29 May 2009 11:46
or is it a bad coding approach?

It is your choice to create longer and less transparent code.
Just one observation: as FLAG is not assigned anything in case of success, it will hold NULL value. So the condition (FLAG <> 0) will have NULL value and <some action> will not be performed. Seems like bug to me.
Re: when no_data_found [message #405764 is a reply to message #405739] Fri, 29 May 2009 06:35 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can this be alternative to

If you don't understand the first one do it as you want but abvoce all do it that is
1/ compile
2/ fit the requirements.

Regards
Michel
Previous Topic: How to extract the left and right sides of the decimal
Next Topic: selecting the data from Partitioned table
Goto Forum:
  


Current Time: Thu Dec 08 14:10:46 CST 2016

Total time taken to generate the page: 0.06840 seconds