#---------------------------------------------------------------------
#
# Tcl Procedure
#        dp_collect_sql
# 
# Purpose:
#        Collects and returns a datapoint via executing a sql stmt  
#
# Arguments:
#        args(0) == username/password for the target database
#        args(1) == the sql statement
# 
# Return value:
#        - the data point collected 
#
#---------------------------------------------------------------------
proc dp_collect_sql {c_str sql_stmt } {
   global oramsg
   set connect [format "%s@%s" $c_str $oramsg(oraobject)]
   # open connection to the target database
   if {[catch {oralogon $connect} lda]} {
      append msg [msgtxt [RDBMS] ora $oramsg(rc)] ""
      error $msg
   }    
   # open a cursor
   if {[catch {oraopen $lda} cur]} {
      append msg [msgtxt [RDBMS] ora $oramsg(rc)] ""
      catch {oralogoff $lda} err
      error $msg
   }   
   # execute the sql
   if {[catch {orasql $cur $sql_stmt}]} {
      append msg [convertout $oramsg(db_characterset) $oramsg(errortxt)] " : " $sql_stmt
      catch {oraclose $cur} err
      catch {oralogoff $lda} err
      error $msg
   }
   # fetch the data set
   if {[catch {orafetch $cur} first_row]} {
      append msg [convertout $oramsg(db_characterset) $oramsg(errortxt)] " : " $sql_stmt
      catch {oraclose $cur} err
      catch {oralogoff $lda} err
      error $msg
   }
   catch {orafetch $cur} second_row
   # return the datapoint (first column of the first row)
   if { ($oramsg(rows)!=1) || ([llength $first_row] != 1) } {
      append msg [msgtxt [NETWORK] nms 1021] " : " $sql_stmt
      catch {oraclose $cur} err
      catch {oralogoff $lda} err
      error $msg
   } else {
      catch {oraclose $cur} err
      catch {oralogoff $lda} err
      return [lindex $first_row 0]
   }
}




#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  generic.tcl : Event tcl file that checks an arbitrary condition 
#                against an arbitrary data point
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == operator
#           argv(2) == no of occurrance
#           argv(3) == threshold value for alert
#           argv(4) == threshold value for warning
#           argv(5) == the sql statement that collects the data
#           argv(6) == the flag for possible empty threshold
#                      -1: both alert and warning thresholds are to be used;
#                       0: only alert threshold is to be used;
#                       1: only warning threshold is to be used;
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           The current value of the data point
#


# Event definition
oradefine event /oracle/rdbms/generic/sql
oraarguments connect_str operator no_occurrence alert_threshold warning_threshold sql_stmt threshold_flag
oravardesc connect_str oracle_signon
oravardesc operator string 
oravardesc no_occurrence unsigned 
oravardesc alert_threshold string 
oravardesc warning_threshold string 
oravardesc sql_stmt string 
oravardesc threshold_flag string 
oraresults curr_value
oravardesc curr_value string
oradefine end

# Initialize global variables
set output ""
set noc 1

# The main event checking functions
proc EvalEvent {} {


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

    # consume the input parameters
    set connect_str [lindex $argv 0]
    set operator [lindex $argv 1]
    set no_occurrence [lindex $argv 2]
    set alert_threshold [lindex $argv 3]
    set warning_threshold [lindex $argv 4]
    set sql_stmt [lindex $argv 5]
    set threshold_flag [lindex $argv 6]

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""

    # evaluate the current value of the datapoint
    if {[catch {dp_collect_sql $connect_str $sql_stmt} ret]} {
        lappend output $ret
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    } else {
        set curr_value $ret 
    }

    ORATCL_DEBUG "sql : $oramsg(oraobject) : [oratime] : current value is $curr_value"
   
    # evaluate the current severity 
    set condition1 [concat \"$curr_value\" $operator \"$alert_threshold\"]
    set condition2 [concat \"$curr_value\" $operator \"$warning_threshold\"]
    set noc_condition [concat "if \{ " $noc "<" $no_occurrence " \} \{ incr noc \} else "]

    if { [string compare $threshold_flag "-1"] == 0 } {
        set command [format \
        "if { %s } { %s { set ret_code $ALERT_EVENT } } elseif { %s } { %s { set ret_code $WARNING_EVENT } } else { set noc 1 }" \
            $condition1 $noc_condition $condition2 $noc_condition]  
    } elseif { [string compare $threshold_flag "0"] == 0 } {
        ORATCL_DEBUG "sql : $oramsg(oraobject) : [oratime] : Warning threshold is to be ignored."
        set command [format \
        "if { %s } { %s { set ret_code $ALERT_EVENT } } else { set noc 1 }" $condition1 $noc_condition ]  
    } elseif { [string compare $threshold_flag "1"] == 0 } {
        ORATCL_DEBUG "sql : $oramsg(oraobject) : [oratime] : Alert threshold is to be ignored."
        set command [format \
        "if { %s } { %s { set ret_code $WARNING_EVENT } } else { set noc 1 }" $condition2 $noc_condition ]  
    } else {
        ORATCL_DEBUG "sql : $oramsg(oraobject) : [oratime] : unknown (threshold) flag parameter: \"$threshold_flag\"."
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    if {[catch {eval "$command"} ret]} {
        lappend output $ret 
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }
    set output $curr_value

    # return
    if {$last_report == $ret_code} {
        return $NO_EVENT
    } else {
        ORATCL_DEBUG "sql : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        return $ret_code 
    }

}
