Home » SQL & PL/SQL » SQL & PL/SQL » exception
exception [message #226064] Thu, 22 March 2007 07:51 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
hi...
I would like to know whether we can come back to main section of the program after an exception has been raised?

If so, how? please

rzkhan
Re: exception [message #226066 is a reply to message #226064] Thu, 22 March 2007 07:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Too generic of a question. I'd have to answer yes, and say that your code will continue after it encounters and exception. You need to ask a more specific question with examples.

[added]

And after reading Frank's response, I wanted to just mention that I was not meant to deceive here. In my mind, I pictured that the block of code would have a separate BEGIN / EXCEPTION / END block within the code, and that flow would just continue, as opposed to exiting out of the entire block. I saw the question as more of a "will my program bomb when it encounters an exception?"

[Updated on: Thu, 22 March 2007 08:06]

Report message to a moderator

Re: exception [message #226069 is a reply to message #226066] Thu, 22 March 2007 07:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If I understand your name correctly, the answer is NO.
After an exception is raised and handled, you can NOT return to the place where the exception occurred.
Adjust your logic with nested blocks to accomplish that
Re: exception [message #226073 is a reply to message #226069] Thu, 22 March 2007 08:04 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have the following code which throws no data found.
------------------------------------------------------
------This procedure assigns gas bill to each member
------ and inserts the amount in bill table
------------------------------------------------------

 create or replace PROCEDURE ASSIGN_GAS (mp IN char,yp IN number ) as
   cursor c1 is select * from members
   where status=1;
   x c1%rowtype;
   cursor c2 is select * from gas_rt  where g_m=mp and g_y=yp;
  x2 c2%rowtype;
 a number(5);
 b number(10,2);
 c number(10,2);
 d number(3);
   begin
   select count(*) into a from members  where status=1;
   select amount into b  from gas_rt where g_m=mp and g_y=yp;
   select item_id into d from gas_rt;
   delete from bill where  m=mp and y=yp and item_id=d;
   c :=b/a;
   for x in c1 loop
   for x2 in c2 loop
 -- dbms_output.put_line(x.member_id||'  '||x2.item_id||' '||c||' '||x2.g_m||' '||x2.g_y);
  insert into bill values(x.member_id,x2.item_id,c,x2.g_m,x2.g_y);
  end loop;
   end loop;
exception
when no_data_found then
null;
dbms_output.put_line('No data found');
   end;




at sql prompt it gives the error. But I need to find out the exact record where the error was. I am afraid I can not understand this .. please help me out..

thanks

rzkhan
Re: exception [message #226076 is a reply to message #226073] Thu, 22 March 2007 08:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Get rid of your exception handler. It has no function other than obscuring bugs.
I can't believe these two can go along without at lease one of them raising an error:
select amount into b  from gas_rt where g_m=mp and g_y=yp;
select item_id into d from gas_rt;

Either you can lose the where clause from the first, because there is only 1 row anyway, or you will get a too_many_rows exception on the second one.

On second look: start all over. this procedure does not make much sense..

[Updated on: Thu, 22 March 2007 08:10]

Report message to a moderator

Re: exception [message #226078 is a reply to message #226073] Thu, 22 March 2007 08:08 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
One of these two lines causes the exception:
   select amount into b  from gas_rt where g_m=mp and g_y=yp;
   select item_id into d from gas_rt;

You can put a separate BEGIN / EXCEPTION / END block around each SELECT , or around both of them together and then handle it whatever way you wish.
Re: exception [message #226081 is a reply to message #226078] Thu, 22 March 2007 08:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Thu, 22 March 2007 14:08
One of these two lines causes the exception:
   select amount into b  from gas_rt where g_m=mp and g_y=yp;
   select item_id into d from gas_rt;

You can put a separate BEGIN / EXCEPTION / END block around each SELECT , or around both of them together and then handle it whatever way you wish.

I'm quite sure the second line can NEVER raise a no_data_found...
Re: exception [message #226082 is a reply to message #226081] Thu, 22 March 2007 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Heh, you know, I was lazy, I didn't even look at the statement. I just saw a SELECT without and aggregate, and that immediately triggered a knee-jerk reaction of it needing an exception. My mistake.

Of course though, if there were no rows in the table at all...Wink

[Updated on: Thu, 22 March 2007 08:16]

Report message to a moderator

Re: exception [message #226086 is a reply to message #226082] Thu, 22 March 2007 08:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hehe..
Same here. I reacted immediately on those two lines. Then reread the code, and decided to add my little advise on the rewrite Smile
Re: exception [message #226088 is a reply to message #226082] Thu, 22 March 2007 08:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Thu, 22 March 2007 14:15

Of course though, if there were no rows in the table at all...Wink


Oops, too fast again: it would never reach that line!
Re: exception [message #226089 is a reply to message #226088] Thu, 22 March 2007 08:18 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
OK, I give up. This must be a precursor to something bad going to happen today. I can feel it.
Re: exception [message #226266 is a reply to message #226089] Fri, 23 March 2007 02:16 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi.. Is the following logic OK now..? or it further needs modification.. Plz help



 create or replace PROCEDURE ASSIGN_GAS (mp IN char,yp IN number ) as
   cursor c1 is select * from members
   where status=1;
   x c1%rowtype;
   cursor c2 is select * from gas_rt  where g_m=mp and g_y=yp;
  x2 c2%rowtype;
 a number(5);
 b number(10,2);
 c number(10,2);
 d number(3);
   begin
begin
---select count(*) into a from members  where status=1;
select count(*) into a from attendance 
where flag=1 
and to_char(a_date,'MON')=mp 
and to_char(a_date,'YYYY')=yp;
begin
select amount into b  from gas_rt where g_m=mp and g_y=yp;
exception
when no_data_found then
dbms_output.put_line('No data in gas_rt for '||mp||' '||yp);
when others then
dbms_output.put_line('Error in gas_rt for '||mp||' '||yp);
null;
end;
   select item_id into d from gas_rt;
 delete from bill where  m=mp and y=yp and item_id=d;
c :=b/a;
   for x in c1 loop
   for x2 in c2 loop
 dbms_output.put_line(x.member_id||'  '||x2.item_id||' '||c||' '||x2.g_m||' '||x2.g_y);
---  insert into bill values(x.member_id,x2.item_id,c,x2.g_m,x2.g_y);
  end loop;
   end loop;
exception
when zero_divide then
dbms_output.put_line('No data in attendance for '||mp||' '||yp);
when others then
dbms_output.put_line('Error in assign_gas procedure');
null;
end;
end;
/





rzkhan

Re: exception [message #226280 is a reply to message #226266] Fri, 23 March 2007 02:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but your code is quite unreadable.
* Use logical variable names, not a, b, c or d, but for example total_amount_of_cars_in_the_world, total_number_of_countries and cars_per_country.
* Use indentation
* If you use a cursor for loop, do NOT define the record-type.
* You only use member_id from cursor c1, so don't select *
* and to_char(a_date,'YYYY')=yp; yp is a number. Don't compare numbers to strings.
* dbms_output.put_line is useless in server-side code. If you run this unattended, your errors will NOT be visible.
* null; does not do anything. Remove it.
* when others then
dbms_output.put_line('Error in gas_rt for '||mp||' '||yp);
null;
You have NO IDEA what can go wrong here. Yet you proceed. That is a bug.
* My previous remark still stands. You select into b from gas_rt using a where-clause, and then select into d without one. This will most likely result in a too_many_rows.
* Join the select into d and the delete.
* join cursors c1 and c2
* even better: make it a insert (select <joined cursors c1 and c2> )
Re: exception [message #226331 is a reply to message #226064] Fri, 23 March 2007 04:57 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
create or replace PROCEDURE ASSIGN_GAS (mp IN char,yp IN number ) as
	cursor c1 is select member_id from members
	where status=1;
	cursor c2 is select * from gas_rt  where g_m=mp and g_y=yp;
total_attendances number(5);
total_gas_amount number(10,2);
gas_amt_per_att  number(10,2);
d number(3);
begin
   begin
	select count(*) into total_attendances from attendance 
	where flag=1 
	and to_char(a_date,'MON')=mp 
	and to_char(a_date,'YYYY')=yp;

		--------------get gas amount for the month----------------
		begin
			select amount into total_gas_amount  from gas_rt where g_m=mp and g_y=yp;
			exception
			when no_data_found then
			dbms_output.put_line('No data in gas_rt for '||mp||' '||yp);
		end;
		--------------end get gas amount for the month-------------
	--------------delete from bill any existing gas records-----------------
	select distinct item_id into d from gas_rt;
	delete from bill where  m=mp and y=yp and item_id=d;
	--------------end delete 
	--------------calcualte per member gas charges
	gas_amt_per_att  :=total_gas_amount/total_attendances;
		for x in c1 loop
		 for x2 in c2 loop
		 dbms_output.put_line(x.member_id||'  '||'gas_amt_per_att  '||'gas_amt_per_att '||x2.g_m||' '||x2.g_y);
		 ---  insert into bill values(x.member_id,x2.item_id,c,x2.g_m,x2.g_y);
		 end loop;
		end loop;
		exception
		when zero_divide then
		dbms_output.put_line('No data in attendance for '||mp||' '||yp);

   end;
end;
/




I am using this procedure under when-button-pressed trigger. The error message is visible at SQL prompt. (as u have mentioned). My question is how can I show this message in Forms6i. . I mean Can I pass the error message to Forms6i.
Please guide..

rzkhan

Re: exception [message #226377 is a reply to message #226331] Fri, 23 March 2007 07:55 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I still see many problems Frank pointed out. Why are you STILL comparing a character string to a number? Please, please I would love to know the reason for this.
SELECT DISTINCT is going to trigger a TOO_MANY_ROWS exception.
MESSAGE is the Forms function to display output.
And for Frank, luckily I was wrong. Nothing bad happened yesterday, or at least to me.

[Updated on: Fri, 23 March 2007 08:01]

Report message to a moderator

Re: exception [message #226386 is a reply to message #226377] Fri, 23 March 2007 08:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for the feedback, joy! You got me worried Wink
Re: exception [message #226738 is a reply to message #226064] Mon, 26 March 2007 06:38 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member

cheers

Quote:

and to_char(a_date,'YYYY')=yp;


to_number(to_char(a_date,'YYYY'))=yp;
Quote:

SELECT DISTINCT is going to trigger a TOO_MANY_ROWS exception.




This will never throw an exception. As the table contains only one item_code. No other item_code is allowed to be entered in.


and I am still stuck in how to display a message in forms. by passing it from stored procedure.. Any idea??


rzkhan
Re: exception [message #226756 is a reply to message #226738] Mon, 26 March 2007 08:01 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rzkhan wrote on Mon, 26 March 2007 07:38


Quote:

SELECT DISTINCT is going to trigger a TOO_MANY_ROWS exception.




This will never throw an exception. As the table contains only one item_code. No other item_code is allowed to be entered in.



Don't blame me if I'm just a bit skeptical about this. If there is only one row in there, why are you bothering to use a WHERE clause earlier on the same table? If there's only one row, the WHERE clause is not adding anything, AND there is no point in having cursor C2 if there is only one row.
Previous Topic: What is the difference
Next Topic: Doubt on a query
Goto Forum:
  


Current Time: Sun Dec 04 18:23:35 CST 2016

Total time taken to generate the page: 0.16774 seconds