# $Header: unschdjob.tcl 06-jun-00.10:56:15 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  unschdjb.tcl : Event tcl file that checks if any job is not rescheduled for execution.
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == threshold value for alert
#           argv(2) == approximate time, in minutes, for a job to complete.
#           
#
# RETURN:
#           $SCRIPT_FAIL
#           $CLEAR_EVENT
#           $NO_EVENT
#           $WARNING_EVENT
#           $ALERT_EVENT
#
# OUTPUT:
#           A list of jobs that not rescheduled for execution
#
# $Log:  $
# Revision 1.5  12/12/97  18:28:59  rposham
# initial
#

# Event definition
#
#
#
oradefine event /oracle/rdbms/fault/unschdjob description=VOC-01906 \
report=VOC-01907
oraarguments connect_str alert_threshold job_completion_time
oravardesc connect_str oracle_signon
oravardesc alert_threshold unsigned default=0 message=VOC-01908
oravardesc job_completion_time unsigned default=10 message=VOC-01909
oraresults jobs
oravardesc jobs string
oradefine end
#


# Initialize global variables
#comment out to fix bug#606739
#set last_report $CLEAR_EVENT
set output ""
oraeventpersist last_jobs {} 


# The main event checking functions
proc EvalEvent {} {


    # Declare globals we will use
    global argv last_report output
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg 
    global last_jobs

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""
    set jobs {}

    # connect to the database and open a cursor
    set connect [format "%s@%s" [lindex $argv 0] $oramsg(oraobject)]
    if {[catch {oralogon $connect} lda]} {
        lappend output [msgtxt [RDBMS] ora $oramsg(rc)] ""
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    if {[catch {set cur [oraopen $lda]} err]} {
	lappend output $err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    # read the approximate job completion time in minutes
    set job_completion_time [lindex $argv 2] 

    set sql "select job from sys.dba_jobs where sysdate > next_date + "
    set sql [concat $sql $job_completion_time]
    set sql [concat $sql "/(60*24)"]

    # If we want to be more accurate, we should do the following too
    set sql [concat $sql " and job not in ( select job from sys.dba_jobs_running ) "

    if { [catch { orasql $cur $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        lappend output $err
        if {$last_report == $SCRIPT_FAIL} {
           return $NO_EVENT
        } else {
           set last_report $SCRIPT_FAIL
           return $SCRIPT_FAIL
        }
    }

    if {[catch {set row [orafetch $cur]} err]} {
	lappend output $err
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    while {$oramsg(rc) == 0} {
        lappend jobs [lindex $row 0]
	if {[catch {set row [orafetch $cur]} err]} {
	    lappend output $err
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
        }
   }



    # log off
    catch {oraclose $cur} err
    catch {oralogoff $lda]} err

    # print out event trace info 
    ORATCL_DEBUG "unschdjob : $oramsg(oraobject) : [oratime] : [llength $jobs]"


    # check number of broken jobs against alert threshold 
    if {[llength $jobs] > [lindex $argv 1]} {
        set ret_code $ALERT_EVENT
        lappend output $jobs
    } 
    
    
    # return
    if { $ret_code == $last_report && [string compare $last_jobs $jobs] == 0 } {
        return $NO_EVENT
    } else {
        ORATCL_DEBUG "unschdjob : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_jobs $jobs
        return $ret_code 
    }
}


