LISTING 6: This script, execAnalNlog.prc, is installed by condiAnalyze.install and called by condiAnal.prc and recondiAnal.prc (Listings 8 and 9). It updates the sys_analyzed_objects table. /* Filename : execAnalNlog.prc execute Analyze and log it Author : Julius J.Y. Lin 01/09/97 Purpose : sub-program called by condiAnal.prc and reCondiAnal.prc This procedure runs the sql statement and updates the result info back to table sys_analyzed_objects. Note !! if use package, many of these parameters can be used as global variables except for obj_type called from condiAnalyze() which will be my next version. */ -- =========== sub-procedure execNlog for conditional analyze ============== set document off CREATE OR REPLACE PROCEDURE execAnalNlog( p_schema in varchar2, p_cur_tbl in varchar2, p_start_time in date, p_cs_id in integer, p_sql_stmt in varchar2, p_obj_type in varchar2, p_actual_row_cnt in number ) IS v_obj_name varchar2(34) ; v_status sys_analyzed_objects.status%TYPE ; v_row_cnt number ; v_end_time date ; BEGIN IF p_obj_type = 'index' THEN v_obj_name := p_cur_tbl || '*NDX' ; v_row_cnt := NULL ; ELSE v_obj_name := p_cur_tbl ; v_row_cnt := p_actual_row_cnt ; END IF ; /* by putting the analyze statement in a local block, if an error is raised, it can still continue to the next statement */ BEGIN dbms_sql.parse( p_cs_id, p_sql_stmt, dbms_sql.v7 ); v_status := 'OK' ; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( p_obj_type||' : '||substr( SQLERRM, 1, 150)); v_status := 'FAILED' ; END ; v_end_time := sysdate ; update_analyzed_obj( p_schema, v_obj_name, p_obj_type, v_row_cnt, p_start_time, v_end_time, v_status ); END execAnalNlog ; / -- ===================== end sub-procedure execNlog =====================