Home » SQL & PL/SQL » SQL & PL/SQL » raising an exception (9i)
raising an exception [message #339862] Sat, 09 August 2008 01:29 Go to next message
karunakar_adepu84
Messages: 43
Registered: January 2008
Member
Hi to all,
Hi I am getting a problem with followin code can any one solve the problem for me?
this is my code:
DECLARE
       CURSOR C1 is 
       SELECT  empno,sal 
       FROM emp12       WHERE mgr=&mgr
       FOR UPDATE  of sal nowait;
e_update EXCEPTION;
BEGIN
       FOR i IN c1 loop
       UPDATE emp12 SET sal=&sal WHERE  current of c1;
       IF c1%notfound THEN
       RAISE e_update;
       END IF;
       END LOOP;
EXCEPTION
WHEN e_update THEN
dbms_output.put_line('there is no such mgr');
END;

Here the problem is when I am using the exact mgr number which is emp table the code is going write , But when I am using the mgr number which is not in table The code has to disply the exception message. how to do this? if there is any other way please provide the answer as soon as possible/

Thanking You Advance.
Karunakar

[Edit MC: add code tags]

[Updated on: Sat, 09 August 2008 01:58] by Moderator

Report message to a moderator

Re: raising an exception [message #339866 is a reply to message #339862] Sat, 09 August 2008 01:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

Firstly, formatting your post according to instructions in OraFAQ Forum Guide would be helpful.

Secondly, why are you using cursor to update the rows?
Of course, when the SELECT statement does not return any row, no UPDATE is performed at all.
You could declare a new variable and set it when any row is updated.
But using single UPDATE statement and checking SQL%ROWCOUNT would be much simpler.
Re: raising an exception [message #339867 is a reply to message #339866] Sat, 09 August 2008 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry flyboy, was modifying OP's post at the same time you said him to format it.

To complete flyboy's answer, if &mgr does not match a current mgr then you never enter inside the loop so exception can't be raised. Exception that can never raise as %notfound leads to exit the loop by the FOR clause so you can't test this case inside the loop.

Regards
Michel
Re: raising an exception [message #339882 is a reply to message #339867] Sat, 09 August 2008 07:12 Go to previous messageGo to next message
karunakar_adepu84
Messages: 43
Registered: January 2008
Member
Hi thankq for reply,
how to update the employee salary based on mgr number, if mgr number is not exist then I have to disply one message. how to do this.
Re: raising an exception [message #339905 is a reply to message #339882] Sat, 09 August 2008 10:30 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Test sql%rowcount after update.

Regards
Michel
Previous Topic: Hi - need help with if statement
Next Topic: Pivoting table with 3 dimentions
Goto Forum:
  


Current Time: Wed Dec 07 08:43:04 CST 2016

Total time taken to generate the page: 0.07987 seconds