Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Problem
Cursor Problem [message #192686] Wed, 13 September 2006 04:45 Go to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi,
  declare
 cursor c1 is select * from emp
 where deptno=20
for update of sal nowait;
 begin
 for emp_rec in c1
 loop
 EXIT when c1%notfound;
 raise no_data_found;
 if emp_rec.empno=7369 then
 update emp
 set sal=sal+200
where current of c1;
 end if;
 end loop;
exception
  when no_data_found then
   dbms_output.put_line('NO EMp......');
end;

Now there is no employee with empno 7369 in 20 department.Now if empno is not there my cursor should give NO_DATA_FOUND Error. It is not giving .How can i make this happen.

Regards
Srini..

Re: Cursor Problem [message #192689 is a reply to message #192686] Wed, 13 September 2006 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Where do I start?

1) If you are using the FOR rec IN CURSOR LOOP syntax, you don't need the 'IF %NOTFOUND' check - the loop will finish when you get to the end of the set of rows.

2) You don't have any data in the EMP table with a DEPTNO of 20 - if you did it would automatically hit the 'RAISE no_data_found;' that you have erroneously put in after the EXIT statement.

3) Your entire piece of code can be replaced with the SQL statement
UPDATE emp
SET    sal = sal+200
WHERE  deptno = 20
AND    empno = 7369;


4) If you want to check how many rows an update statement processed, simply check SQL%ROWCOUNT immediately after the statement.
Re: Cursor Problem [message #192693 is a reply to message #192686] Wed, 13 September 2006 05:05 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi,

I will put it like this...in a cursor if no data found occurs how should we handle it. In my case if I give deptno=99 then I should get soem error saying that no department like 99 exists should come.Help me.

 declare
 cursor c1 is select * from emp
 where deptno=99
for update of sal nowait;
 begin
 for emp_rec in c1
 loop
 EXIT when c1%notfound;
 raise no_data_found;
 if emp_rec.empno=7369 then
 update emp
 set sal=sal+200
where current of c1;
 end if;
 end loop;
exception
  when no_data_found then
   dbms_output.put_line('NO EMp......');
end;


Regards
Srini
Re: Cursor Problem [message #192703 is a reply to message #192693] Wed, 13 September 2006 05:41 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
if you are really insist on cursor and raise exception when you encounter no rows for that department in cursor, then use as given below

declare
	cursor c1 is select * from emp
	where deptno=99
	for update of sal nowait;
	cnt number := 0;
begin
	for emp_rec in c1
	loop
	cnt := cnt + 1;
	if emp_rec.empno=7369 then
		update emp
		set sal=sal+200
		where current of c1;
	end if;
	end loop;

	if cnt > 0 then
		raise no_data_found;
	end if;

	exception
	when no_data_found then
	dbms_output.put_line('NO EMp......');
end;


Thanks,
Thangam
Re: Cursor Problem [message #192707 is a reply to message #192686] Wed, 13 September 2006 05:49 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
HI Thangam

It is not taking me to the exception block at all. I tried it.This is the most important topic and question.

Some one please help me.

Regards
Srini..
Re: Cursor Problem [message #192711 is a reply to message #192707] Wed, 13 September 2006 05:56 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
OOPS. the "IF" conditions should be
[code]
if cnt = 0 then
raise no_data_found;
end if;
[code]

not ">0"
Thanks,
Thangam
Re: Cursor Problem [message #192720 is a reply to message #192686] Wed, 13 September 2006 06:09 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi Thangam,

What u said is exactly working if the department by number 99 is not there in emp table,But now the scenario is my emp table has 20 department and that 20 department has no employee with empno:7369. This empno 7369 is there only in department 10.So when the code given below is excuted then i should get an error like 'NO employee like 7369 in this department' . How to raise this exception.Please let me know.
declare
 cursor c1 is select * from emp
 where deptno=20
 for update of sal nowait;
 cnt number := 0;
begin
 for emp_rec in c1
 loop
 cnt := cnt + 1;
 if emp_rec.empno=7369 then
  update emp
  set sal=sal+200
  where current of c1;
 end if;
 end loop;
 if cnt = 0 then
  raise no_data_found;
 end if;
 exception
 when no_data_found then
 dbms_output.put_line('NO EMp......');
end;

Now it is not giving any error.
It says
Quote:

PL/SQL procedure successfully completed.

Please answer this.
Regards
Srini..
Re: Cursor Problem [message #192726 is a reply to message #192720] Wed, 13 September 2006 06:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You asked for code to give you an error if the specified Dept did not exist, which is exactly what @gold_orcl gave you.

If you want to display an error if there are no employees in the department chose, then your current code is fine.
Otherwise, what are you after?

You say that Empno 7369 is in Dept 10, and that it should raise an error saying this.
Should it also warn you about employee 7370 who's in Dept 30, or his brother 7371, who works over in Dept 80?

If you are actually planning to use this piece of code to update the salary for a single employee and want it to warn you if he doesn't exist in a specified department, then you are going about it in a completely arse-backwards manner.

Please describe what you are trying to do here.
Re: Cursor Problem [message #192727 is a reply to message #192726] Wed, 13 September 2006 06:43 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
HI JROWBOTTOM,

I am talking about two different scenarios.
1)when the department 99 doesn't exist in the employee table at all.
2)when a department exists like dept 20 and no employee like 7369 exists in that department.

What gold_orcl said is correct for the 1st scenario, but i want to know how you will raise for the second scenario.My question is very clear. Any doubts let me know.

Regards
Srini..
Re: Cursor Problem [message #192736 is a reply to message #192727] Wed, 13 September 2006 07:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So let's get this straight.
You've written an procedure to inefficiently update the salary of all the employees in a department, and you them plan to use this procedure to update a single employee, making it even less efficient.

Somehow, I doubt that this is your intention, which is why I asked you to explain what it was that you were trying to do. As you chose not to provide any useful information, I@ll just have to work with what you have provided.

I wouldn't solve this problem at all - instead I'd junk the code you've got and write something like:

BEGIN
  UPDATE emp
  SET    sal = sal+200
  WHERE  deptno = 20
  AND    empno  = 7369;

  IF sql%rowcount = 0 THEN
    raise_application_error(-20001,'Employee/Department combination not found.');
  END IF;
END;
Re: Cursor Problem [message #193104 is a reply to message #192686] Thu, 14 September 2006 22:21 Go to previous message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Thank u JROW
Previous Topic: Adding dummy value to list of selected values
Next Topic: select query with date = null??
Goto Forum:
  


Current Time: Fri Dec 09 02:09:36 CST 2016

Total time taken to generate the page: 0.21099 seconds