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!

Stored Procedure without input parameter

849860Sep 20 2011 — edited Sep 20 2011
I am importing data from csv files and storing as imp_nats on daily basis, I have current table nats which has data...

What i am trying to do is to compare count from both the table and if difference is less than equal to 1% then replacing current nats table by imp_nats...
 create or replace
PROCEDURE SP_IMPORTNATS
as
V_NATS number(20):=0,
V_IMP_NATS number(20):=0,
V_CNT_NATS number(20):=0,

BEGIN
   
   begin
      SELECT COUNT(*) INTO v_NATS  FROM NATS ;
 
       select count(*) INTO v_IMP_NATS  FROM IMP_NATS ;
          
      v_CNT_NATS := round(((v_NATS - v_IMP_NATS) * 100) / v_NATS) ;
     
 IF (V_CNT_NATS =1)  or (V_CNT_NATS = 0) or (V_CNT_NATS = -1) then
begin
 execute immediate 'drop table NATS_BK';
execute immediate 'alter table NATS rename to NATS_BK';
EXECUTE IMMEDIATE 'alter table IMP_NATS rename to NATS';
end;
end if;

   end;
end; 
When i am trying compile its giving me error please help...
This post has been answered by pollywog on Sep 20 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 18 2011
Added on Sep 20 2011
3 comments
303 views