Stored Procedure - not able to display no data found message [message #680402] |
Wed, 13 May 2020 01:59 |
|
subash_141
Messages: 1 Registered: May 2020
|
Junior Member |
|
|
Hi All,
I have created a procedure, which will take the Mgr number as input for execution.
When ever data is there for input passed Mgr number we are getting data. But when we don't have data it is not displaying "no data found" message.
create or replace procedure sp1 (mg number)
as
cursor c1 is select * from emp1 where mgr = mg;
i emp1%rowtype;
begin
for i in c1
loop
dbms_output.put_line(i.ename||' '||i.sal);
end loop;
exception
when no_data_found then
dbms_output.put_line('no data found');
end;
/
Can anyone please tell me how to display "no data found" message when there is no data for input Mgr number.
Thanks,
Subash
[Edit MC: format the post]
[Updated on: Wed, 13 May 2020 02:38] by Moderator Report message to a moderator
|
|
|
|
Re: Stored Procedure - not able to display no data found message [message #680406 is a reply to message #680402] |
Wed, 13 May 2020 07:13 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
subash_141 wrote on Wed, 13 May 2020 02:59
Can anyone please tell me how to display "no data found" message when there is no data for input Mgr number.
Start from reading documentation:
13.17 Cursor FOR LOOP Statement
...
With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor.
As you can see, cursor for loop can't raise NO_DATA_FOUND by definition. You can do something like:
DECLARE
V_CNT NUMBER := 0;
...
BEGIN
FOR V_REC IN V_CUR LOOP
V_CNT := V_CNT + 1;
...
END LOOP;
IF V_CNT = 0
THEN
RAISE NO_DATA_FOUND;
END IF;
...
END;
/
SY.
|
|
|