HELP with PL/SQL

All,  

Simple stuff probably but I am VERY new to PL/SQL. What I want to do is this.  

I have a table whic contains a list of all tables in a schema. The procedure (package body) needs to query dba_tables (I eventually want to look at all schemas) for the num_rows*avg_row_len for each table in the list. It will then place this in the table and work out %age growth over a week. The table holds previous weeks calculation.  

The problem is it puts in the columns for every table, the figures retrieved for the last table and it is driving me up the wall. Can someone please look at the code (Without laughing) and point me in the right direction, or suggest improvements to what I am doing.  

Regards  

Lee  

PROCEDURE ESTTBLGROWTH is

     var_old number;
     var_new number;
     var_pct number;
     anal_tname varchar2(30);
 
cursor c_tname is select table_name from all_tables
                                       where owner = 'PG_UK';
 

   begin  

        /* Retrieve tables
        */
        open c_tname;
        loop
 
        fetch c_tname into anal_tname;
           
           select tbl_size into var_old from sizetbl 
                       where anal_tname = table_name;
 
           select nvl(((avg_row_len*num_rows)/(1024*1024)),0) into var_new
                                         from all_tables
                                         where anal_tname = table_name
                                         and owner = 'PG_UK';
 
           if var_old > 0 then
                var_pct := round((var_new/var_old)*(100/1)-100);
 
           /* update analysis table
           */
                  update sizetbl  
           set    date_sized = sysdate,
                         tbl_old_sz = var_old, 
                         tbl_size = var_new,
                         pct_growth = var_pct
               where  anal_tname = table_name;
 
           end if;
 
        end loop;   /* for all records in user_tables */
 
 
 

   end ESTTBLGROWTH;  

Lee Robertson
Acxiom
Tel: 0191 525 7344
Fax: 0191 525 7007
Email: lerobe_at_acxiom.co.uk  

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the Received on Fri Dec 08 2000 - 10:21:39 CST