oracle.pl

#!/usr/local/bin/perl 
# Perl-Oracle-Web Interaction Routines - by Scott Florcsk
# This file contains customized useful perl routines for APHIS intranet apps

$ENV{'ORACLE_SID'} = "prod";
$ENV{'ORACLE_HOME'} = "/usr/oracle/app/oracle/product/7.3.4";

sub sql {
local ($sql_login, @sql_code) = @_;
$sqlplus = "/usr/oracle/app/oracle/product/7.3.4/bin/sqlplus -S";
$sql_code_begin = "set echo off pages 200 verify off head off feedback off lines 500 ARRAYSIZE 1 escape ^\n";
$sql_code_end = "\nset head on lines 80 pages 23 feedback on\nquit;\n";
@sql_array[0..2] = ($sql_code_begin, @sql_code, $sql_code_end);
@sql_code = @sql_array;
$random = time();
$rand = substr($random,length($random)-8,8);
while ( -e "/var/tmp/$rand.sql" ) { $rand = $rand - 600; }
$sql_file = "/var/tmp/".$rand.".sql";
open (SQLCODE, ">$sql_file");
print SQLCODE @sql_code;
close (SQLCODE);
system("$sqlplus $sql_login \@$sql_file");
unlink ("$sql_file");
}

sub upper {
  local($input) = @_;
  $input =~ tr/a-z/A-Z/;
  return $input;
}

# Fix (by SF): This function makes oracle and perl variables like one-another
# Specifically, passed variables are: capitalized, wildcard'ed correctly (* -> %),
# double-ticked (apostrophy), and assigned an ORACLE empty/wildcard value (%) if passed blank
sub fix {
  local($input) = @_;
  $input =~ tr/a-z/A-Z/;
  $input =~ s/\*/\%/g;
  $input =~ s/\'/\%/g;
  if (length($input) < 1)
  {
    $input = "%";
  }
  return $input;
}


sub fix_string {
  local($input) = @_;
  $input =~ s/\s{2,}/ /g;
  $input =~ s/'/''/g;
  $input =~ s/&/ and /g;
  return $input;
}


sub fix_number {
  local($input, $min, $max) = @_;
  if (($min<=$input)&&($input<=$max)) {
  return $input;
} else {
print <
  You entered a "$input" in one of the fields that exceeded the bounds that was being 
  requested. In other words, the field was calling for a value between 1 and 3, and you 
  entered something like the number 6 or the letter Q. Duh!!! Go back a page, find your 
  booboo, correct it, and re-submit it. Thanks for your support.
  
ENDOFHTML
  exit;
}
return $input;
}

sub GetButtonName {
  local (%in) = @_;
  @list = (sort keys(%in));
  $ButtonPressed = $list[0];
  return $ButtonPressed;
}

sub select_date {
  local($input) = @_;
  ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
  @months = ("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");
  if ($year > 35) {
    $year = $year + 1900;
  } else {
    $year = $year + 2000;
  }
  $mon_name = $input . "_month";
  $day_name = $input . "_day";
  $year_name = $input . "_year";
  print ("\n");
  for ($mon_index=0; $mon_index<12; $mon_index++) {
    if ($mon_index eq $mon) {
      print("$months[$mon_index]\n");
    } else {
      print("$months[$mon_index]\n");
    }
  }
  print ("\n");
  for ($day_index=1; $day_index<32; $day_index++) {
    if ($day_index eq $mday) {
      print("$day_index\n");
    } else {
      print("$day_index\n");
    }
  }
  print ("\n");
  for ($year_index=$year-2; $year_index<$year+9; $year_index++) {
    if ($year_index eq $year) {
      print("$year_index\n");
    } else {
      print("$year_index\n");
    }
  }
  print ("");
}

sub valid_date {
  local($month, $day, $year) = @_;
  if (($month eq "APR") || ($month eq "JUN") || ($month eq "SEP") || ($month eq "NOV")) {
    $day_limit = 30;
  } elsif ($month eq "FEB") {
    if($year%4 == 0) {
      $day_limit = 29
    } else {
      $day_limit = 28
    }
  } else { $day_limit = 31; }
  if ($day > $day_limit) {
print <
  $month $day, $year is not a valid date, yet you tried to input it anyway. Did you really think you 
  could get that one by me? Now go back and make sure you select a day that is contained within the 
  month of $month.
  
ENDOFHTML
  exit;
}
}

1;