Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure - not able to display no data found message
Stored Procedure - not able to display no data found message [message #680402] Wed, 13 May 2020 01:59 Go to next message
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 #680403 is a reply to message #680402] Wed, 13 May 2020 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

See how nicer is your post now I have formatted it.

The reason you have not the message is because you use a cursor loop which manages the NO_DATA_FOUND exception to end the loop and never raises this exception (otherwise you will also get it when there are rows).

[Updated on: Wed, 13 May 2020 02:39]

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 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: Displaying select results in double columns
Next Topic: Creation of new partitions by increasing the sequence number
Goto Forum:
  


Current Time: Thu Mar 28 07:38:41 CDT 2024