CODEWITHSUNDEEP
sqloracle-databaseplsql
G.Kol
G.Kol

Reputation: 59

Oracle - PLSQL to check the difference in number of records after a truncate/load procedure

Our IT team loads couple of tables every month. The new load should have more records than the previous load, with at least 2% more records. It's a truncate and load process, I'm collecting the num of records from each table before the truncate, and I'm checking the difference in excel every month to make sure the data load is correct. Is there anyway to automate this in Oracle.

eg:

Table_name     Before_cnt       After_cnt
XX_TEST1    4,606,619,326   4,983,759,822 
XX_TEST2      121,973,005     123,161,581

Upvotes: 0

Views: 167

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can apply the steps just like below :

SQL> create table XX_TEST1( id int primary key );

SQL> insert into XX_TEST1 select level from dual connect by level <= 100;

SQL> begin -- if table exists, then drop it!
 for c in (select table_name from cat where table_name = 'XX_TEST1_OLD' )
 loop
  execute immediate 'drop table '||c.table_name;
 end loop; 
end;  
/    

SQL> create table XX_TEST1_old as select count(*) as cnt from  XX_TEST1;

SQL> begin
    execute immediate 'truncate table XX_TEST1';
end;  
/  
SQL> insert into XX_TEST1 select level from dual connect by level <= 103;

SQL> with xt1_new(cnt_new) as
(
 select count(id) from XX_TEST1
)
select case when sign( (100 * ( cnt_new - cnt) / cnt)-2 ) = 1 then 1
               else 0 end as "Rate Satisfaction"
      from XX_TEST1_old
      cross join xt1_new;

If this SELECT statement retuns 1, then we're successful to reach the target, else returns 0 and means we're unsuccessful.

Demo

Upvotes: 1

Related Questions