LISTING 5: This script, upAnaObj.prc, is installed by condiAnalyze.install and called by condiAnal.prc (Listing 8). It updates the sys_analyzed_objects table.
/*   
Filename : updAnaObj.prc
Author   : Julius J.Y. Lin 		12/31/96
Purpose  : create procedure for update sys_analyzed_objects table, called
	      from condiAnalyze()
See Also : condiAnal.prc, condiAnalyze.install
*/

CREATE OR REPLACE PROCEDURE update_analyzed_obj ( 
	p_owner		in	varchar2,	-- p_ means parameter for the procedure
	p_obj_name  	in	varchar2,
	p_obj_type    	in    	varchar2,
	p_row_cnt     	in    	integer,
	p_start_tm    	in    	date,
	p_end_tm      	in 	date,
	p_status  	in    	varchar2	
) AS
   v_upc_owner	varchar2(10) := upper( p_owner ) ; -- upper case owner
   v_row_id	rowid ;
   v_t_status     	SYS_ANALYZED_OBJECTS.status%TYPE ;
 
BEGIN
   SELECT 	rowid, status		-- provide a fast data access  
   INTO   	v_row_id, v_t_status
   FROM		SYS_ANALYZED_OBJECTS
   WHERE  	owner 	 = v_upc_owner
   AND    	object_name = p_obj_name
   AND    	object_type   = p_obj_type
   ;
   -- if a table needs to be analyzed:
   -- higher ANALYZED_COUNT means table is very dynamic, reverse is false
   IF p_obj_type = 'TABLE' AND p_status = 'OK' THEN
      UPDATE 	sys_analyzed_objects
      SET    	analyzed_count = nvl(analyzed_count, 0) + 1
      WHERE  	ROWID     = v_row_id
      ;
   END IF ;
				   -- keep 3 previous analyzed dates.
   IF v_t_status = 'OK' AND p_status = 'OK' AND p_end_tm is not NULL THEN
	UPDATE 	sys_analyzed_objects
	SET    		last_3_analyzed  = last_2_analyzed
	WHERE  	ROWID     = v_row_id
   	;
	UPDATE 	sys_analyzed_objects
	SET    		last_2_analyzed  = last_1_analyzed 
	WHERE  	ROWID     = v_row_id
   	;
	UPDATE 	sys_analyzed_objects
	SET    		last_1_analyzed  = end_date
	WHERE  	ROWID     = v_row_id
   	;
   END IF ;

   UPDATE 	sys_analyzed_objects
   SET    		row_count        = p_row_cnt,  
		started_date  = p_start_tm,
   		end_date	    = p_end_tm,
		status	    = p_status
   WHERE  	ROWID = v_row_id 
   ;
   commit ; 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO sys_analyzed_objects 
      VALUES( v_upc_owner, p_obj_name, p_obj_type, p_row_cnt, p_start_tm, p_end_tm, 
	      p_status, 1, null, null, null )
      ; 
      COMMIT ; 
   WHEN OTHERS THEN
      dbms_output.put_line( 'Upd Anal Obj: '||substr( SQLERRM, 1, 150)) ;
END ;
/