Originally posted by puneet
I am trying to run a PL/SQL to get the table_name , num_rows from user_tables and count(*) for each table and if the difference between num _rows and count(*) for that table is greater than 10000 then I want to display it .

declare
tab varchar2(100);
a number;
b = 2 number ;
c number
begin
loop
b:= b +1;
SELECT table_name into tab FROM user_tables where rownum < a;
SELECT NUM_ROWS INTO b FROM USER_TABLES WHERE TABLE_NAME = 'tab';
select count(*) into c from 'tab';
if b - c > 10000 then
dbms_output.put_line ('tab' , b , c );
exit when b = 30;
end loop;
end;
/

Please let me know what is wrong with this .

Thanks
Let's start with...everything?! Not to be destructive, but I can not correlate what you say you're trying to do with what that code is doing whatsoever.

First, you do a loop and increment the variable 'b', which you then stomp on later on with the line "select num_rows into b from user_tables..." -- and I'm not even sure what you're attempting to accomplish with that line; it asks where table_name = 'tab', and obviously you can only have zero or one tables with the exact name 'tab', maybe you mean where table_name LIKE 'tab%' or maybe you think that 'tab' will use the variable tab, which it won't.

Now you exit when b = 30, which may or may not ever happen given that you stomp its value.

I think you might need to more clearly state your goal, because from your code I can not even derive it; but it looks like you want to do more than what you say? What are you doing a difference for? What is the actual purpose of 'b'? Also, why are you selecting table names where rownum < a?