Home » Other » Training & Certification » Doubt in Exception handling. (10g, Windows xp)
Doubt in Exception handling. [message #341788] Wed, 20 August 2008 05:51 Go to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi all,
I have a table named classes with description ,
SQL> desc classes
Name Null? Type
----------------------------------------------------- -------- ------------------
DEPARTMENT NOT NULL CHAR(3)
COURSE NOT NULL NUMBER(3)
DESCRIPTION VARCHAR2(2000)
MAX_STUDENTS NUMBER(3)
CURRENT_STUDENTS NUMBER(3)
NUM_CREDITS NUMBER(1)
ROOM_ID NUMBER(5)


I created a procedure named 'limit' as follows which displays an error that the max student limit has been exceeded as follows,

create or replace procedure limit (p_no IN number,p_dep IN varchar2) is
v_cur classes.current_students%type;
v_max classes.max_students%type;
e_toomany exception;
begin
select current_students,max_students into v_cur,v_max from classes where department='p_dep' and course=p_no;
if v_cur>v_max then raise e_toomany;
end if;
exception
when e_toomany then
dbms_output.put_line('There are currently ' || v_cur ||' no of students which is higher than the limit of ' ||v_max);
when others then
dbms_output.put_line('students count is fine at present!!!!');
end;
/

Procedure created.



Now my doubt is when i issue the following ,
sql>exec limit(101,'HIS');

the message getting displayed is 'students count is fine at present!!!!'

But, I was expecting the error message 'There are currently 31 no of students which is higher than the limit of 30'

As, the classes table had the max students as 30 and current_students as 31 for the department and course values of 'HIS' and 101 respectively (please note the current_students far exceeds the max_students count)...

Pls help me out.

Thanks,
Arun.
Re: Doubt in Exception handling. [message #341800 is a reply to message #341788] Wed, 20 August 2008 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read 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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

when others then
dbms_output.put_line('students count is fine at present!!!!');

This is a bug, remove it.

Regards
Michel
Re: Doubt in Exception handling. [message #341802 is a reply to message #341800] Wed, 20 August 2008 06:28 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi Michel,
I went thro the instructions,will follow it in the future. I wanted to ask why its necessary to remove the line that u specified ie.,

when others then
dbms_output.put_line('students count is fine at present!!!!');


Thanks.
Re: Doubt in Exception handling. [message #341806 is a reply to message #341802] Wed, 20 August 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it and reexecute and you will know the answer.

Regards
Michel
Re: Doubt in Exception handling. [message #341811 is a reply to message #341806] Wed, 20 August 2008 06:58 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi Michel,
I removed the line and executed as you said,


CREATE OR REPLACE PROCEDURE Limit
     (p_No   IN NUMBER,
      p_dep  IN VARCHAR2)
IS
  v_Cur      Classes.Current_Students%TYPE;
  v_Max      Classes.Max_Students%TYPE;
  e_TooMany  EXCEPTION;
BEGIN
  SELECT Current_Students,
         Max_Students
  INTO   v_Cur,
         v_Max
  FROM   Classes
  WHERE  Department = 'p_dep'
         AND Course = p_No;
  
  IF v_Cur > v_Max THEN
    RAISE e_TooMany;
  END IF;
EXCEPTION
  WHEN e_TooMany THEN
    dbms_Output.Put_Line('There are currently '
                         ||v_Cur
                         ||' no of students which is higher than the limit of '
                         ||v_Max);
END;
/




But while executing the procedure I get the following error,


exec limit(101,'HIS');

BEGIN limit(101,'HIS'); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.LIMIT", line 6
ORA-06512: at line 1



But the values 101 and HIS are very well present in the classes table..


Thanks,
Arun.
Re: Doubt in Exception handling. [message #341826 is a reply to message #341811] Wed, 20 August 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now you know the real error.
Now you know why "when others" is a bug.
I hope you will NEVER use it now.

Quote:
select current_students,max_students into v_cur,v_max from classes where department='p_dep' and course=p_no;

You force the department to a value, you don't give the parameter (see p_no).

Regards
Michel


Re: Doubt in Exception handling. [message #341975 is a reply to message #341826] Wed, 20 August 2008 23:43 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi Michel,

Thank you very much , i am clear now it worked well..!! Cool
Re: Doubt in Exception handling. [message #341976 is a reply to message #341826] Wed, 20 August 2008 23:44 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
SQL> set serveroutput on
SQL> exec limit(101,'HIS');
There are currently 99 no of students which is higher than the limit of 30

PL/SQL procedure successfully completed.
Re: Doubt in Exception handling. [message #342012 is a reply to message #341976] Thu, 21 August 2008 01:17 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Now to improve your code (get a better grade Wink)
Why would you test if cur > max AFTER you fetched a result?
You could do it in SQL, which would mean that you only retrieve a row if you have an offending record.

In that case, you would have to catch another exception (that is: the one that gets raised if no record is found). In case that exception is raised (meaning no offending record), you have to do nothing.
If the query DOES return a row, it means you found an offending record, so you'd have to display your message.
I'm sure your teacher will appreciate if you'd hand in both versions, plus you'll be learning along the way!
Re: Doubt in Exception handling. [message #342088 is a reply to message #342012] Thu, 21 August 2008 05:26 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi Frank,

Cool,i didnt think in your angle, thanks..i tried out your strategy , hope you give me an 'A' grade for this??!! Smile Smile


CREATE OR REPLACE PROCEDURE Limit_Violation
     (p_No   NUMBER,
      p_dep  VARCHAR2)
IS
  v_Cur  Classes.Current_Students%TYPE;
  v_Max  Classes.Max_Students%TYPE;
BEGIN
  SELECT Current_Students,
         Max_Students
  INTO   v_Cur,
         v_Max
  FROM   Classes
  WHERE  Department = p_dep
         AND Course = p_No
         AND Current_Students > Max_Students;
  
  dbms_Output.Put_Line('For department '
                       ||p_dep
                       ||' current students count is '
                       ||v_Cur
                       ||' which is a violation of max student limit of '
                       ||v_Max);
EXCEPTION
  WHEN No_Data_Found THEN
    dbms_Output.Put_Line('The total student count is normal!!');
END;
/




SQL> exec limit_violation(101,'HIS');
For department HIS current students count is 99 which is a violation of max
student limit of 30

PL/SQL procedure successfully completed.



Thanks,
Arun.
Re: Doubt in Exception handling. [message #342174 is a reply to message #342088] Thu, 21 August 2008 11:11 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Nice job!
Previous Topic: Can I go for 10g / Oracle 9i
Next Topic: Suggestion OCA certification
Goto Forum:
  


Current Time: Fri Mar 29 08:10:58 CDT 2024