Home » SQL & PL/SQL » SQL & PL/SQL » Exception Handling
Exception Handling [message #296203] Fri, 25 January 2008 01:47 Go to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
hi,
say i have 3 select statements in my procedure. and one of it doesn't return any values.
and i trap the exception data_not_found in my exception handling. but with the exception how will i come to know which select statement doesn't return values.

thanx & regards,
viji
Re: Exception Handling [message #296209 is a reply to message #296203] Fri, 25 January 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Include each statement in a begin/exception/end block.

Regards
Michel
Re: Exception Handling [message #296217 is a reply to message #296209] Fri, 25 January 2008 02:09 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
can i get any examples or websites with examples

regards,
viji
Re: Exception Handling [message #296221 is a reply to message #296217] Fri, 25 January 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, post what you tried.

Regards
Michel
Re: Exception Handling [message #296240 is a reply to message #296217] Fri, 25 January 2008 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
deviji
i have 3 select statements
In other words, you have something like this (forget about PL/SQL for now):
SELECT column FROM table;
SELECT another_column FROM another_table;
SELECT third_column FROM third_table;

Michel told you to
Include each statement in a begin/exception/end block.

So, how would you "include"
SELECT column FROM table;
into its own BEGIN-EXCEPTION-END?
Re: Exception Handling [message #296267 is a reply to message #296240] Fri, 25 January 2008 07:59 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
create or replace procedure check_dept
as
declare
var1 number(2) ;

begin
var1=20;
select * from emp where deptno=var1;
select * from dept where deptno=var1;
select * from loc,dept
where loc.locationo=dept.locationo;

exception
when data_not_found then
dbms_output.putline("data not fetched");

end;

in the above code where i have to include each statement to find out which statement failed to return data?
Re: Exception Handling [message #296268 is a reply to message #296267] Fri, 25 January 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead (or in addition) of having on global exception block, enclose each statement inside in a begin/exception/end block.

Also read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Fri, 25 January 2008 08:05]

Report message to a moderator

Re: Exception Handling [message #296273 is a reply to message #296268] Fri, 25 January 2008 08:20 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
sorry for not sending in formatted way, and thanx for your reply. I'll work out.

--viji
Re: Exception Handling [message #296308 is a reply to message #296267] Fri, 25 January 2008 12:23 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I hope the code is just pseudo-code or an outline because it surely will not compile in Oracle.
Re: Exception Handling [message #296442 is a reply to message #296308] Sun, 27 January 2008 07:56 Go to previous messageGo to next message
KrishnaBoppana
Messages: 12
Registered: March 2007
Location: Boston, MA
Junior Member
One way to get around the select no data found exceptions is to use cursors, where you have much more control over the fetch operation and ability to raise application errors.

Cursors are life savers if your PL/SQL blocks are select heavy.

-Krishna

[Updated on: Sun, 27 January 2008 09:11] by Moderator

Report message to a moderator

Re: Exception Handling [message #296446 is a reply to message #296442] Sun, 27 January 2008 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is completly meaningless.
What is this use of cursor to handle no data?

Quote:
Cursors are life savers if your PL/SQL blocks are select heavy.

Cursor are performances killers if you don't know how to use them.

You should avoid putting url of your company when you say silly thing. You MUST avoid it anyway, profile are there for this.

Regards
Michel

[Updated on: Sun, 27 January 2008 09:12]

Report message to a moderator

Re: Exception Handling [message #296494 is a reply to message #296203] Sun, 27 January 2008 23:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

KrishnaBoppana might have meant to say that Select in Cursors doesn't lead to exception . But anyway it is not a method to trap the exception and it doesn't answer OP's Query.

One other way around would be to include all the SELECT queries in a single Block , but with some Errormesage assigning before each select statement. Once the exceptiop is Arised log/display the output.


BEGIN

   errormsg := 'unable to select from Tabl;

  SELECT ..
  FROM   Tab1;

  errormsg := 'unable to select from Tab2;
 
  SELECT ..
  FROM   Tab2;


  errormsg := 'unable to select from Tab3;

  SELECT ..
  FROM   Tab3;

EXCEPTION
 DBMS_OUTPUT.PUT_LINE('Error :'||errormsg);
END;


Though my vote goes to Exception handling for each select statement.

Thumbs Up
Rajuvan

[Updated on: Sun, 27 January 2008 23:43]

Report message to a moderator

Re: Exception Handling [message #296555 is a reply to message #296273] Mon, 28 January 2008 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    v number;
  3  begin
  4    begin
  5      select id into v from t where rownum=1;
  6      dbms_output.put_line('First query, got '||v);
  7    exception 
  8      when no_data_found then dbms_output.put_line('First query, got nothing');
  9    end;
 10    delete t;
 11    begin
 12      select id into v from t where rownum=1;
 13      dbms_output.put_line('Second query, got '||v);
 14    exception 
 15      when no_data_found then dbms_output.put_line('Second query, got nothing');
 16    end;
 17    rollback;
 18    begin
 19      select id into v from t where rownum=1;
 20      dbms_output.put_line('Third query, got '||v);
 21    exception 
 22      when no_data_found then dbms_output.put_line('Third query, got nothing');
 23    end;
 24  end;
 25  /
First query, got 1
Second query, got nothing
Third query, got 1

PL/SQL procedure successfully completed.

Regards
Michel
Re: Exception Handling [message #296585 is a reply to message #296555] Mon, 28 January 2008 05:56 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
wow, that's an excellent example to trap exceptions from multiple select statement Mr. Michel,

i think you are fed up with the guys and gave the final answer itself.
anyway thank you so much.

regards,
viji
Re: Exception Handling [message #296594 is a reply to message #296585] Mon, 28 January 2008 06:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
deviji wrote on Mon, 28 January 2008 12:56
i think you are fed up with the guys and gave the final answer itself

That would be a very bad attitude in a public forum, so that would not be the reason Michel chose to show you how to do this.
Re: Exception Handling [message #296598 is a reply to message #296594] Mon, 28 January 2008 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Mon, 28 January 2008 13:25
deviji wrote on Mon, 28 January 2008 12:56
i think you are fed up with the guys and gave the final answer itself

That would be a very bad attitude in a public forum, so that would not be the reason Michel chose to show you how to do this.

You're right Frank.
The reason I posted it is that there is so many different things in this topic that it is now very difficult to know what's the correct way.
For instance, Rajuvan posted an (working) example and finally say "Though my vote goes to Exception handling for each select statement". So do we use it or not?

Regards
Michel
Re: Exception Handling [message #296603 is a reply to message #296203] Mon, 28 January 2008 06:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
That would be a very bad attitude in a public forum

Yes. I totally Agree with that .
Every member has right to express their own openions in a publice Forum like Orafaq.

After all nobody has Perfect knowledge . Each member learns from other's Post .

Thumbs Up
Rajuvan.
Re: Exception Handling [message #296610 is a reply to message #296203] Mon, 28 January 2008 06:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I susggested my openion for using 'Single Block statement that uses different Variable assignment for various select statement' because this is what we are following in Our application.

I stand still on my comment 'my vote goes to Exception handling for each select statement' because I like that kind of flow .

Anyway , BALL is now with the Actual OP Smile

Thumbs Up
Rajuvan.
Re: Exception Handling [message #296612 is a reply to message #296610] Mon, 28 January 2008 06:59 Go to previous message
deviji
Messages: 23
Registered: November 2007
Junior Member
I agree the Quote
Quote:
Every member has right to express their own openions in a publice Forum like Orafaq. After all nobody has Perfect knowledge .
so i expressed my opnion.

because my doubt could be a small doubt for the experienced, and who faced this kind of situation many times.

regards,
viji
Previous Topic: Oracle query
Next Topic: Call a package dynamically in a procedure
Goto Forum:
  


Current Time: Sun Dec 11 06:19:59 CST 2016

Total time taken to generate the page: 0.05898 seconds