RE: HELP with PL/SQL

Lee,

I'm not laughing, I promise.

OK, let's address some points of style.

1. It would be better to use a cursor-for loop (the one you have here is infinite, and also you never close the cursor)
2. It would be best to join up all the information you want in one select rather than running the same selects and calculations over and over within the loop
3. You have your where clause switched around in the update -- not a big deal, but odd to look at. Usually one puts "where <table_column_name> = <bind>", not the other way around. Maybe that is a big deal after all?

So, what we come up with is this (not tested, not sure it will solve your problem):

PROCEDURE ESTTBLGROWTH is

	cursor c_tname is 
		select table_name,
nvl(((avg_row_len*num_rows)/(1024*1024)),0) var_new, tbl_size var_old
		from all_tables, sizetbl
		where owner = 'PG_UK'
		and table_name = anal_tname;
	var_pct number;
   begin
	for crec in c_tname loop
		if crec.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 = crec.var_old, 
				tbl_size = crec.var_new,
				pct_growth = var_pct
			where anal_tname = crec.table_name;
		end if;
	end loop;
 

   end ESTTBLGROWTH;

-----Original Message-----
From: lerobe - Lee Robertson [mailto:LEROBE_at_acxiom.co.uk] Sent: Friday, December 08, 2000 11:23 AM To: Multiple recipients of list ORACLE-L Subject: 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 original message or any copy of it from your computer system.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: lerobe - Lee Robertson
  INET: LEROBE_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L