Home » Other » Training & Certification » question based on Oracle PL/SQL Programming book code
question based on Oracle PL/SQL Programming book code [message #390062] Wed, 04 March 2009 15:46 Go to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
I have encountered the following code (see below). it worked fine. However, after analyzing it i could not understand why the exception HAS to be within the INNER block. My assumption was that since the exception was raised within the loop, it will then get passed on to the exception block which will handle it and the loop will continue and everyone is going to live happily ever after. Why does it have to be inside the inner block?
I commented out BEGIN and END; of the inner block and received a bunch of ugly errors as if i have done something unconstitutional:
ORA-06550 line 24
PLS-00103
then another
ORA-06550 line 31
PLS-00103: encountered symbol end_of_file when expected one os the following ;

There is no end-of-file, all i have is END LOOP;

please help because i am definitely missing something and then book does not explain why this exception HAS to be inside the inner block. I mean it was declared globally ?

thank you Smile

set serveroutput on

declare
	cursor instruct_cur IS
		select instructor_id, count(*) total_sec
		from section
		group by instructor_id;
		
	v_name varchar2(30);
	e_too_many_sections EXCEPTION;
	
begin
	for instruct_rec IN instruct_cur LOOP
	--inner block
	--BEGIN
		if instruct_rec.total_sec >= 10 THEN
			RAISE e_too_many_sections;
		else
			select rtrim(first_name)||' '||rtrim(last_name)
				into v_name
				from instructor
				where instructor_id = instruct_rec.instructor_id;
				
			DBMS_OUTPUT.PUT_LINE('Instructor, '||v_name||', teaches '|| instruct_rec.total_sec||' sections');
		end if;
	exception
		when e_too_many_sections THEN
	DBMS_output.put_line('this instructor teaches too much');
	--END; 
	-- end inner block
	end loop;
END;

/

[Updated on: Wed, 04 March 2009 23:05] by Moderator

Report message to a moderator

Re: question based on Oracle PL/SQL Programming book code [message #390074 is a reply to message #390062] Wed, 04 March 2009 18:38 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
The exception section of a block of code has to be the last part of a block of code.
Re: question based on Oracle PL/SQL Programming book code [message #390079 is a reply to message #390062] Wed, 04 March 2009 20:26 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
> Why does it have to be inside the inner block?
Because Oracle syntax allows catching exceptions only in BEGIN END blocks, not in LOOP statement. It (=the syntax) is described in PL/SQL User's Guide and Reference book, which is part of Oracle documentation. It is available e.g. online on http://tahiti.oracle.com/.
Please, consult it before any other questions.

> please help because i am definitely missing something and then book does not explain why this exception HAS to be inside the inner block. I mean it was declared globally ?
What is the relationship between those two statements? The exception may be declared locally in the inner block (using DECLARE part). There are many ways to achieve the same behaviour - you may get rid of the exception raising and handling at all.
Re: question based on Oracle PL/SQL Programming book code [message #409307 is a reply to message #390062] Mon, 22 June 2009 01:50 Go to previous message
svvenkat
Messages: 1
Registered: March 2009
Location: Mumbai
Junior Member
Hi happyjoshua777,

Your quote :
[ for instruct_rec IN instruct_cur LOOP
--inner block
--BEGIN
if instruct_rec.total_sec >= 10 THEN
RAISE e_too_many_sections;
else
select rtrim(first_name)||' '||rtrim(last_name)
into v_name
from instructor
where instructor_id = instruct_rec.instructor_id;

DBMS_OUTPUT.PUT_LINE('Instructor, '||v_name||', teaches '|| instruct_rec.total_sec||' sections');
end if;
exception
when e_too_many_sections THEN
DBMS_output.put_line('this instructor teaches too much');
--END;
-- end inner block
end loop;
]
If you need to comment the inner block, then you may need to move the end loop, further up, before the exception block.

Raja
Previous Topic: OAF , ADF,BPEL , SOA training (merged 6)
Next Topic: Need advice on Certification options.. OCP9i and/or OCP11g
Goto Forum:
  


Current Time: Thu Oct 30 09:14:34 CDT 2014

Total time taken to generate the page: 0.12785 seconds