Home » SQL & PL/SQL » SQL & PL/SQL » Cursor- no data found exception handaling
Cursor- no data found exception handaling [message #417131] Thu, 06 August 2009 04:39 Go to next message
mahantesh
Messages: 28
Registered: March 2008
Location: Mumbai ,India
Junior Member
In my procedure there are three cursor
cur_customer_details
cur_revenue_details
cur_cost_details.

I have problem with cursor cur_cost_details.
Here select statement fetches some rows and i insert these record in to database table.
But when select statement returns no row then i want to insert some other data in to database table.
To get this result i try by using insert command in no data found exception but it is of no use. so i have try with some user defind exception , but it is also not of use.

here i have added code

MOD-EDIT: Removed Unnecessary Code that was breaking the board.

So can anyone help me regarding this.
Thanks
Mahantesh

[Updated on: Thu, 06 August 2009 04:43] by Moderator

Report message to a moderator

Re: Cursor- no data found exception handaling [message #417133 is a reply to message #417131] Thu, 06 August 2009 04:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I removed the code because it served no real purpose to explain the problem, and it broke the board in a way that the reply-buttons where about six screens to the left.

A cursor that returns no rows will NOT raise an exception, so you can't implement your requirement with exception handling.

You need to use a variable or something in the code that processes the returned rows to check if rows are returned or not.

[Updated on: Thu, 06 August 2009 04:57]

Report message to a moderator

Re: Cursor- no data found exception handaling [message #417134 is a reply to message #417131] Thu, 06 August 2009 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the code how one can help you?

Regards
Michel
Re: Cursor- no data found exception handaling [message #417135 is a reply to message #417134] Thu, 06 August 2009 04:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Without the code how one can help you?


Trust me, you wouldn't have wanted to go through thousands of lines of code just to confirm that it's indeed the empty-cursor-won't-raise-exception problem. Razz
Re: Cursor- no data found exception handaling [message #417139 is a reply to message #417134] Thu, 06 August 2009 04:55 Go to previous messageGo to next message
mahantesh
Messages: 28
Registered: March 2008
Location: Mumbai ,India
Junior Member
sorry,
i have past code over there but they have removed it , so here i am attaching package body.
Regards
Mahantesh
Re: Cursor- no data found exception handaling [message #417145 is a reply to message #417139] Thu, 06 August 2009 05:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

A cursor that returns no rows will NOT raise an exception, so you can't implement your requirement with exception handling.



You have to check after the loop if any rows were returned. This would be a possible way to do it.

DECLARE 

 v_rows_returned number;

BEGIN

 v_rows_returned := 0;
 FOR l IN (SELECT 1 FROM dual WHERE 1 = 1) LOOP
    v_rows_returned := v_rows_returned + 1;
 END LOOP;
 Dbms_Output.put_line('Rows Returned : ' || v_rows_returned);

 v_rows_returned := 0;
 FOR l IN (SELECT 1 FROM dual WHERE 1 = 2) LOOP
    v_rows_returned := v_rows_returned + 1;
 END LOOP;
 Dbms_Output.put_line('Rows Returned : ' || v_rows_returned);
 
END;
/


Re: Cursor- no data found exception handaling [message #417155 is a reply to message #417145] Thu, 06 August 2009 05:31 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

U can use c1%ROWCOUNT
Instead of exception cursor will never throw no_data_found exception

DECLARE 
  CURSOR c1 IS 
    SELECT 1 
    FROM   dual 
    WHERE  2 = 1; 
   a  NUMBER; 
BEGIN 
  FOR x IN c1 LOOP 
    a := c1%ROWCOUNT; 
    dbms_output.Put_line(a); 
  END LOOP; 
   
  IF a IS NULL THEN 
    dbms_output.Put_line('cursor with no values'); 
  END IF; 
END; 


Regards
Prajakta
Re: Cursor- no data found exception handaling [message #417157 is a reply to message #417155] Thu, 06 August 2009 05:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What does your example add to ThomasG's?
Re: Cursor- no data found exception handaling [message #417160 is a reply to message #417157] Thu, 06 August 2009 05:55 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Yeh Frank ..its quite similar.....
anyway .. i dont see
c1%ROWCOUNT there .........
Re: Cursor- no data found exception handaling [message #417165 is a reply to message #417160] Thu, 06 August 2009 06:04 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Prajakta001 wrote on Thu, 06 August 2009 11:55
Yeh Frank ..its quite similar.....
anyway .. i dont see
c1%ROWCOUNT there .........



That's becuase you don't need it for this.
Re: Cursor- no data found exception handaling [message #417170 is a reply to message #417165] Thu, 06 August 2009 06:17 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hmmm...may be
Re: Cursor- no data found exception handaling [message #417186 is a reply to message #417170] Thu, 06 August 2009 07:19 Go to previous messageGo to next message
mahantesh
Messages: 28
Registered: March 2008
Location: Mumbai ,India
Junior Member
Thanks all
Ragards
Mahantesh
Re: Cursor- no data found exception handaling [message #417537 is a reply to message #417131] Mon, 10 August 2009 02:20 Go to previous message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
try to use implicit cursor if returned row is not more than one.
Doing this way you can trap no_data_found exception.There is no use in using explicit cursor when the select statement returns just one row.
Previous Topic: regarding all_tab_columns
Next Topic: Pl/SQL Procedure Performance Issue
Goto Forum:
  


Current Time: Sun Dec 04 18:33:11 CST 2016

Total time taken to generate the page: 0.04215 seconds