Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Exiting from sql script when no data found

612530Jan 30 2008 — edited Jan 30 2008
Hi
i'm having a sql script in that there are three queries.

i want to exit from the sql script displaying as "No duplicates" if i have no records for the first query, can anyone suggest me how to do it in a sql script, without anonymous block, below i have given my script

========================================

whenever sqlerror exit sql.sqlcode
set serveroutput on
column fn new_value filename
select 'remove_duplicates_'||to_char(sysdate, 'yyyymmddhh24miss')||'.log' as fn from dual;
spool E:/&filename

---------------------------------------
prompt 'Checking for duplicates'
---------------------------------------
select account_id, count(1)
from accounts
group by account_id
having count(1) > 1;

---------------------------------------
prompt 'Records to be deleted'
---------------------------------------

select *
from accounts
where decode ( substr(account_id,1,3),'COG',1,0)=1
group by account_id
having count(1) > 1;

---------------------------------------
prompt ' Deleting duplicates'
---------------------------------------

delete
from accounts
where account_id in (
select account_id
from accounts
where decode ( substr(account_id,1,3),'COG',1,0)=1
group by account_id
having count(1) > 1
);

commit;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 27 2008
Added on Jan 30 2008
4 comments
897 views