#!/usr/local/bin/oraperl
####################################################################
#   File:    import2.pl
#   Input:   Data from the File WCType6.DAT
#   Output:  The script writes all the data in the file to Oracle table 
#    	     tempWarrentyClaimsErrorsMF
#   Author:  Zhijing Zhou         
####################################################################

$ENV{'ORACLE_HOME'}= "/usr/oracle";
$ENV{'PATH'} = "$path /usr/oracle/bin /bin /usr/bin /usr/ucb/usr/local/bin . ";
$ENV{'WOW_UID'}= "$user/$user";
$ENV{'ORACLE_SID'}= "cmuser";
$user="ns_user";
$passwd="ns_user";                                                 

# ==========================================================
# ================= oraperl setup ==========================
# ------------------------------------------------------------
# First make sure that we are running under some form of perl.
# ------------------------------------------------------------
  eval "echo 'You must specify oraperl or coraperl.' ; exit" if 0;
# -----------------------------------------------
# make sure that we really are running (c)oraperl
# -----------------------------------------------
  die ("You should use oraperl, not perl\n") unless defined &ora_login;
# -----------------
# Set up debugging
# -----------------
# -----------------
  $ora_debug = shift if $ARGV[0] =~ /^-#/;
# ----------------
# get error codes
# ----------------
  require('/usr/oracle/oraperl/src/oraperl.ph');
# ---------------------
# login to the database
# ---------------------
  $lda = &ora_login('mindy', $user, $passwd)      || die $ora_errstr;
# ==============  end of oraperl setup =======================  
# ============================================================
&TestIt;

sub TestIt {
  $i = '1';
  $message=&Lookup_Err($i);
  &Insert_Table($i, $message);
 }  

 sub Lookup_Err{
   local($Error)= @_;

   # Select record from table ErrorMessageList
   $select_err1 = "SELECT ErrorMessageCode, ErrorMessageDescription FROM ErrorMessageList where ErrorMessageCode= :1 ";

   #Open a complicate query cursor
   $csr1= &ora_open($lda, $select_err1);
  
   # Assign values in SQL statement
   &ora_bind($csr1, $Error);
   
   $ErrorMessage = ""; 
   @data1 = &ora_fetch($csr1);
   foreach $attr1 (@data1){
	$ErrorMessage .= $attr1;
	$ErrorMessage .= " ";
   }
   &ora_close($csr1);
   print $ErrorMessage;
   return $ErrorMessage;
 } 

 sub Insert_Table{
   local($n_Claim_No, $n_Errormessage) = @_;
   
   #Insert data into table WarrentyClaimErrors
   $insert_rec1 = "INSERT INTO WarrentyClaimErrors (Claim_No, ErrorDescription) VALUES (:1, :2)";
   
   #Open a complicate fquery cursor
   $csr2= &ora_open($lda, $insert_rec1);
   
   # Assign values in SQL statement
   &ora_bind($csr2, $n_Claim_No, $n_Errormessage);
   
   # close
   &ora_close($csr2);
   &ora_commit($lda);
   return 1;
  }      
   
     
