Message-Id: <10704.124117@fatcity.com> From: Diana Duncan Date: Fri, 8 Dec 2000 16:54:16 -0500 Subject: 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 = ", 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@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@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@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@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L