Solved

NO_DATA_FOUND

Posted on 2006-06-30
6
366 Views
Last Modified: 2012-06-27
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
Comment
Question by:avi_ny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17021674
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
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17021678
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17022147
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 19

Expert Comment

by:actonwang
ID: 17022154
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17022157
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
 

Author Comment

by:avi_ny
ID: 17023481
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question