# easyAGENT for Oracle
# $Id: Oracle.pm,v 1.2 2006/08/28 22:24:46 abob Exp $
#
#  should have user:pass:sid in /usr/local/soap/etc/oracle
#
package Oracle;

use strict;
use bytes;
use lib '/usr/local/soap/lib';
use INCPATH;
BEGIN {
  @INC = @INCPATH::MYINC;
}

use DBI;

my @ORATABS=("/var/opt/oracle/oratab","/etc/oratab");

my $oratab = sub {
    my $otab;

    while ( $otab = shift @ORATABS) {
        if ( ! -f $otab ) {
	    next;
        }
        open(OTAB,"<".$otab) || die("Can't open oratab ($otab) - aborting");

        while (<OTAB>) {
            chomp;
            if ($_ !~ m/^#/) {
		if ($_ =~ m/:/) {
		    my $line = $_;
		    my($s, $h, $a) = split(':', $line);
                    my $lcs1 = lc($s);
                    my $lcs2 = lc($_[0]);
                    if ( $lcs1 eq $lcs2 ) {
		        #print STDERR "using ORACLE_HOME=$h for sid $s\n";
		        $ENV{'ORACLE_HOME'}=$h;
		        return $h;
                    }
                }
            }
        }

    }

    print STDERR "cannot find ORACLE_HOME for sid $_[0]\n";
    return "";
};

my $setup_connection = sub {
  my($ora_sid) = @_;
  my $ora_con = 'dbi:Oracle:'.$ora_sid;

  my($ora_user,$ora_pass);

  if (not defined($ENV{'ORACLE_HOME'})) {
    $oratab->($ora_sid);
  } 

  open(CONF,"/usr/local/soap/etc/oracle") or return(undef,"UNKNOWN no config file");
  while(<CONF>){
    chomp;
    my ($user,$pass,$sid) = split(':');
    if ($sid eq $ora_sid) {
      ($ora_user,$ora_pass) = ($user,$pass);
    }
  }

  if (not defined($ora_user)){
    return(undef,"UNKNOWN No user defined for sid $ora_sid");
  }

  my $dbh = DBI->connect($ora_con, $ora_user, $ora_pass) or return(undef,"CRITICAL connect failed: $DBI::errstr");
  return($dbh,undef);
};

sub ts {
    my($ts_name, $ora_sid) = split(':', $_[1]);
    my ($ts_size, $ts_free, $ts_type, $ts_auto);

    my($dbh,$error) = $setup_connection->($ora_sid);
    if($error) {
      return $error;
    }

    eval {
	    my $proc = $dbh->prepare(q{
		    begin
			    dp_monitor.query_tablespace(
				    ts_name => :ts_name,
				    ts_type => :ts_type,
				    ts_size => :ts_size,
				    ts_free => :ts_free,
				    ts_auto => :ts_auto
			    );
		    end;
	    });
	    $proc->bind_param(":ts_name", $ts_name);
	    $proc->bind_param_inout(":ts_type", \$ts_type, 100);
	    $proc->bind_param_inout(":ts_size", \$ts_size, 100);
	    $proc->bind_param_inout(":ts_free", \$ts_free, 100);
	    $proc->bind_param_inout(":ts_auto", \$ts_auto, 100);
	    $proc->execute;
    };
    $dbh->disconnect;

    my $ts_auto_dis = "";
    if ($ts_auto > 0) {
	$ts_auto_dis = " AUTO";
    }
    my $ts_used = $ts_size - $ts_free;
    my $ts_used_meg = $ts_used / 1024 / 1024;
    my $ts_size_meg = $ts_size / 1024 / 1024;
    if ($ts_size_meg < 1000) {
	return sprintf("%.2fM/%.2fM%s",$ts_used_meg,$ts_size_meg,$ts_auto_dis);
    } else {
	$ts_size_meg /= 1024;
	$ts_used_meg /= 1024;
	return sprintf("%.2fG/%.2fG%s",$ts_used_meg,$ts_size_meg,$ts_auto_dis);
    }
}

sub quota {
    my($ts_user, $ts_name, $ora_sid) = split(':', $_[1]);
    my ($ts_curv, $ts_maxv);

    my($dbh,$error) = $setup_connection->($ora_sid);
    if($error) {
      return $error;
    }

    eval {
	    my $proc = $dbh->prepare(q{
		    begin
			    dp_monitor.query_quota(
				    ts_name => :ts_name,
				    ts_user => :ts_user,
				    ts_curv => :ts_curv,
				    ts_maxv => :ts_maxv
			    );
		    end;
	    });
	    $proc->bind_param(":ts_name", $ts_name);
	    $proc->bind_param(":ts_user", $ts_user);
	    $proc->bind_param_inout(":ts_curv", \$ts_curv, 100);
	    $proc->bind_param_inout(":ts_maxv", \$ts_maxv, 100);
	    $proc->execute;
    };
    $dbh->disconnect;

    my $ts_curv_meg = $ts_curv / 1024 / 1024;
    my $ts_maxv_meg = $ts_maxv / 1024 / 1024;
    return $ts_curv_meg."M/".$ts_maxv_meg."M";
}

sub pcount {
        my($ora_sid) = $_[1];
        my ($ps_count, $ps_total);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

        eval {
                my $proc = $dbh->prepare(q{
                        begin
                                dp_monitor.query_pcount(
                                        ps_count => :ps_count,
                                        ps_total => :ps_total
                                );      
                        end;    
                });
                $proc->bind_param_inout(":ps_count", \$ps_count, 100);
                $proc->bind_param_inout(":ps_total", \$ps_total, 100);
                $proc->execute;
        };
	$dbh->disconnect;

        return $ps_count."/".$ps_total;
}

sub bcmr {
        my($ora_sid) = $_[1];
        my ($var_bchr, $var_bcmr);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_bchr(
					arg_bchr => :arg_bchr
				);      
			end;    
		});
		$proc->bind_param_inout(":arg_bchr", \$var_bchr, 100);
		$proc->execute;
	};
	$dbh->disconnect;

	$var_bcmr=(1-$var_bchr);
	return sprintf("%.2f/%.2f",$var_bcmr,100);
}

sub lcmr {
        my($ora_sid) = $_[1];
        my ($var_lchr, $var_lcmr);
	my ($ora_user,$ora_pass);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_lchr(
					arg_lchr => :arg_lchr
				);      
			end;    
		});
		$proc->bind_param_inout(":arg_lchr", \$var_lchr, 100);
		$proc->execute;
	};
	$dbh->disconnect;

	$var_lcmr=(1-$var_lchr);
	return sprintf("%.2f/%.2f",$var_lcmr,100);
}

sub dmr {
        my($ora_sid) = $_[1];
        my ($var_dhr, $var_dmr);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_dhr(
					arg_dhr => :arg_dhr
				);      
			end;    
		});
		$proc->bind_param_inout(":arg_dhr", \$var_dhr, 100);
		$proc->execute;
	};
	$dbh->disconnect;

	$var_dmr=(1-$var_dhr);
	return sprintf("%.2f/%.2f",$var_dmr,100);
}

sub cf {
	my($cf_type, $ora_sid) = split(':', $_[1]);
	my ($cf_curv, $cf_maxv);
	
        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

	eval {
		my $proc = $dbh->prepare(q{
			begin
				dp_monitor.query_controlfile(
					cf_type => :cf_type,
					cf_curv => :cf_curv,
					cf_maxv => :cf_maxv
				);
			end;
		});
		$proc->bind_param(":cf_type", $cf_type);
		$proc->bind_param_inout(":cf_curv", \$cf_curv, 100);
		$proc->bind_param_inout(":cf_maxv", \$cf_maxv, 100);
		$proc->execute;
	};
	$dbh->disconnect;

        return $cf_curv."/".$cf_maxv;
}

sub asm {
    my($asm_name, $ora_sid) = split(':', $_[1]);
    my ($asm_size, $asm_free, $asm_type);

    my($dbh,$error) = $setup_connection->($ora_sid);
    if($error) {
      return $error;
    }

    eval {
	    my $proc = $dbh->prepare(q{
		    begin
			    dp_monitor.query_asm_diskgroup(
				    asm_name => :asm_name,
				    asm_type => :asm_type,
				    asm_size => :asm_size,
				    asm_free => :asm_free
			    );
		    end;
	    });
	    $proc->bind_param(":asm_name", $asm_name);
	    $proc->bind_param_inout(":asm_type", \$asm_type, 100);
	    $proc->bind_param_inout(":asm_size", \$asm_size, 100);
	    $proc->bind_param_inout(":asm_free", \$asm_free, 100);
	    $proc->execute;
    };
    $dbh->disconnect;

    my $asm_used = $asm_size - $asm_free;
    my $asm_used_meg = $asm_used;
    my $asm_size_meg = $asm_size;
    if ($asm_size_meg < 1000) {
	return sprintf("%.2fM/%.2fM %s",$asm_used_meg,$asm_size_meg,$asm_type);
    } else {
	$asm_size_meg /= 1024;
	$asm_used_meg /= 1024;
	return sprintf("%.2fG/%.2fG %s",$asm_used_meg,$asm_size_meg,$asm_type);
    }
}

sub locks {
        my($ora_sid) = $_[1];
        my ($lk_count);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

        eval {
                my $proc = $dbh->prepare(q{
                        begin
                                dp_monitor.query_deadlock(
                                        lk_count => :lk_count
                                );      
                        end;    
                });
                $proc->bind_param_inout(":lk_count", \$lk_count, 100);
                $proc->execute;
        };
	$dbh->disconnect;

        return $lk_count;
}

sub rowcount {
        my($rc_query, $ora_sid) = split(':', $_[1]);
        my ($rc_count);

        my($dbh,$error) = $setup_connection->($ora_sid);
        if($error) {
          return $error;
        }

        eval {
                my $proc = $dbh->prepare(q{
                        begin
                                dp_monitor.query_row_count (
                                        rc_query => :rc_query,
                                        rc_count => :rc_count
                                );
                        end;
                });
                $proc->bind_param(":rc_query", $rc_query);
                $proc->bind_param_inout(":rc_count", \$rc_count, 10);
                $proc->execute;
        };
        $dbh->disconnect;

        return $rc_count;
}

1;