| checking if cursor is returning empty list [message #476585] |
Fri, 24 September 2010 01:42  |
saaya123
Messages: 76
Registered: September 2010
|
Member |
|
|
I am select list of ids;
cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;
now I need to return err_code and err_message if this combined list is empty !
Can not use exit%notfound. help please !!
TIA
|
|
|
|
|
|
|
|
|
|
|
|
| Re: checking if cursor is returning empty list [message #476602 is a reply to message #476599] |
Fri, 24 September 2010 03:12  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
You might want to rethink this logic. Exception handling should only be there in case the normal logic does not suffise. IF you say "return", that would suggest that you are making a function. Now let me explain with a simple example:
col orafaq1 format a20
col orafaq2 format a20
Create or replace function orafaq1
Return Varchar2
Is
-- cursor that won't return a record
cursor c1
is
select 'A' return_value
From dual
Where 1=2;
lv_return Varchar2(20);
Begin
Open c1;
Fetch c1 Into lv_return;
If c1%NOTFOUND Then
Close c1;
Raise No_Data_Found;
Else
Close c1;
End If;
Return lv_Return;
Exception
When No_Data_Found Then
Return 'Nothing found!';
End;
/
Create or replace function orafaq2
Return Varchar2
Is
-- cursor that won't return a record
cursor c1
is
select 'A' return_value
From dual
Where 1=2;
lv_return Varchar2(20);
Begin
Open c1;
Fetch c1 Into lv_return;
If c1%NOTFOUND Then
lv_Return := 'Nothing found!';
End If;
Close c1;
Return lv_Return;
End;
/
Select orafaq1
, orafaq2
From dual
/
drop function orafaq1
/
drop function orafaq2
/
I know that it is a rather silly example but I just want to offer an alternative to using exception handling as part of the regular logic.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
