Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

NO_DATA_FOUND

Hi,
This code is a pert of PL/SQL proc

str10i := 'select * from RDR_ODS.period_info  where seriesname='||''''||recpr.rseriesname||''''|| ' and  period='||''''||str9i||''''||' and deleted=''N''';

now what all i want if there is no record retuened thwn do
err_status=true
else err_status=false

The problem is i am using exception to do so.

......
.....
begin
execute  immediate str10i;
 exception
 when NO_DATA_FOUND then
                     
err_status := 'TRUE';
                   
end;
even though returned rows are 0 it doesnot go to exception.
It skips the exception why?
Please respond ASAP
0
avi_ny
Asked:
avi_ny
  • 3
  • 2
1 Solution
 
sathyagiriCommented:
Try this
declare a record to store your RDR_ODS output

declare
var1 RDR_ODS.period_info%rowtype;

Then
str10i := 'select col1 from RDR_ODS.period_info  where seriesname='||''''||recpr.rseriesname||''''|| ' and  period='||''''||str9i||''''||' and deleted=''N''';

begin
execute  immediate str10i into var1;
 exception
 when NO_DATA_FOUND then
               
err_status := 'TRUE';
                   
end;




0
 
sathyagiriCommented:
Or if you just want to to know if your query returns any thing change it as follows

declare
cnt number;

Then
str10i := 'select count(1) from RDR_ODS.period_info  where seriesname='||''''||recpr.rseriesname||''''|| ' and  period='||''''||str9i||''''||' and deleted=''N''';
execute immediate str10i into cnt;
if (cnt > 0) then // records found
//logic
else
//logic
end if
0
 
actonwangCommented:
see this:

http://www.experts-exchange.com/Databases/Oracle/Q_21904208.html

I would still recommend to use my pl/sql code as:

declare
_count number;
begin
select count(*) into _count from (select * from <your table> where rownum=1);  
if _count = 0 then
DBMS_OUTPUT.PUT_LINE('no record');
else
DBMS_OUTPUT.PUT_LINE('some record');
end if;
end;
/
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
actonwangCommented:
for your case, it would be:
//////////////////////////////////////////////////////////

declare
   _count number;

.....

str10i := 'select * from RDR_ODS.period_info  where seriesname='||''''||recpr.rseriesname||''''|| ' and  period='||''''||str9i||''''||' and deleted=''N''';

execute immediate ' select count(*) from ('||str10i || ' and rownum =1)' into _count;

if _count = 0 then
  err_status := 'TRUE';
else
   err_status := 'FALSE';
end if;

....
0
 
actonwangCommented:
it would be lot faster if your query return a lot of records.
Also notice I didn't change your original str10i and I am ONLY using a subquery so you don't need to modify your original query.

Acton
0
 
avi_nyAuthor Commented:
Hi Satayagri,
Thanks.
You saved the weekend.
it worked.
But still I wonder why doesn't my concept worked.
After all  count 0 means NO_DATA_FOUND exception. Well send me if you got something.
Thanks .
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now