# $Header: convert_common.pl 07-apr-2005.11:38:25 sxzhu Exp $
#
# convert_common.pl
# 
# Copyright (c) 2004, 2005, Oracle. All rights reserved.  
#
#    NAME
#      convert_common.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#       Provide common perl rountines for Convert and other components
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    sxzhu       04/07/05 - Handle large plsql output 
#    sxzhu       10/18/04 - Echo sql commands 
#    sxzhu       08/26/04 - Handle ouput during repository DB downtime 
#    sxzhu       07/30/04 - sxzhu_clone_0721
#    sxzhu       07/27/04 - Creation
# 

use strict;
use warnings;

require "emd_common.pl";
require "$ENV{EMDROOT}/sysman/admin/scripts/db/db_common.pl";

use File::Spec;
use vars qw/ $userID $OS $NT $S $TEMP $DELIMITER $DEFER_OUTPUT $OUTPUT_LOC $INDEX/;

#******************************************
#   Shared Subroutines
#******************************************

# Remove multiple files
# removeFiles(fileNameArray)
sub removeFiles
{
  my ($fileNameArray) = @_;

  my @fileNames = split /$DELIMITER/, $fileNameArray;
  my $fileName;

  foreach $fileName (@fileNames)
  {
    &removeFile($fileName);
  }
}

# Get valid control file names (separated by delimiters)
# Call &set_env($oracleHome, $oracleSid) before calling this method.
# getControlFiles()
sub getControlFiles
{
  EMD_PERL_DEBUG("convert_common.getControlFiles(): *** START ***");

  my $sql_string = "";
  $sql_string .= "set echo off\n";
  $sql_string .= "CONNECT $userID AS SYSDBA;\n";
  $sql_string .= "set echo on\n";
  $sql_string .= "set serveroutput on;\n";
  $sql_string .= "set linesize 513;\n";
  $sql_string .= "variable status varchar2(7);\n";
  $sql_string .= "variable filename varchar2(256);\n";
  $sql_string .= "declare\n";
  $sql_string .= "  i    binary_integer := 1;\n";
  $sql_string .= "BEGIN\n";
  $sql_string .= "  dbms_output.enable(2000000);\n";
  $sql_string .= "  FOR cfrec IN (SELECT status, name FROM v\$controlfile WHERE status is NULL)\n";
  $sql_string .= "  LOOP\n";
  $sql_string .= "    dbms_output.put_line('Checked file ' || i || ':');\n";
  $sql_string .= "    dbms_output.put_line('filename# '||cfrec.name);\n";
  $sql_string .= "    i := i + 1;\n";
  $sql_string .= "  END LOOP;\n";
  $sql_string .= "END;\n";
  $sql_string .= "/\n";
  $sql_string .= "EXIT;\n";

  (my $fh, my $filename) = &runSql($sql_string);
  my $controlFile = "";
  my $controlFiles = "";
  open (CONTROL_FILE, "$filename") || die "Unable to open tempfile for CONTROL_FILE\n";
  while (<CONTROL_FILE>)
  {
    if ($_=~/\bfilename#/)
    {
      chomp($_); 
      $controlFile = substr $_, 10; #controlFile name starts from position 10
      $controlFiles .= ${controlFile}.${DELIMITER};
      EMD_PERL_DEBUG("convert_common.getControlFiles(): Filename: $controlFile");
    }
  }

  close CONTROL_FILE;
  close $fh;
  if($NT)
  {
    &removeFile($filename);
  }
  
  EMD_PERL_DEBUG("convert_common.getControlFiles(): *** END ***");
  
  return $controlFiles;
}

# Add control files to init parameter file
# addControlFileToInitFile(controlFileNames, pfileFullName)
sub addControlFileToInitFile
{
  EMD_PERL_DEBUG("convert_common.addControlFileToInitFile(): *** START ***");
  
  my ($controlFileNames, $pfileFullName) = @_;

  EMD_PERL_DEBUG("convert_common.addControlFileToInitFile(): control file names: $controlFileNames");
  EMD_PERL_DEBUG("convert_common.addControlFileToInitFile(): pfile name: $pfileFullName");
  
  my @controlFileNames = split /$DELIMITER/, $controlFileNames;

  open(INIT_ORA, ">>$pfileFullName") || die "Cannot open $pfileFullName";

  if($#controlFileNames == 0)
  {
    print INIT_ORA "control_files='$controlFileNames[0]'\n";
  }
  elsif($#controlFileNames == 1)
  {
    print INIT_ORA "control_files='$controlFileNames[0]',";
    print INIT_ORA "'$controlFileNames[1]'\n";
  }
  else
  {
    print INIT_ORA "control_files='$controlFileNames[0]',";
    foreach (1 .. ($#controlFileNames - 1))
    {
      print INIT_ORA "'$controlFileNames[$_]',";
    }
    print INIT_ORA "'$controlFileNames[$#controlFileNames]'\n";
  }

  close INIT_ORA || die "Cannot close $pfileFullName";

  #For debug
  if($ENV{EMAGENT_PERL_TRACE_LEVEL} >= 0)
  {
    open (INIT_ORA_FILE, "$pfileFullName") || die "Unable to open tempfile for INIT_ORA_FILE\n";
    my @file_content = <INIT_ORA_FILE>;
    my $file_string = "@file_content";
    close INIT_ORA_FILE;
    EMD_PERL_DEBUG("convert_common.addControlFileToInitFile(): INIT_ORA_FILE:\n $file_string");
  }

  EMD_PERL_DEBUG("convert_common.addControlFileToInitFile(): *** END ***");
}

# Run given sql script on source DB
# The caller is responsible to close the returned fileHandle
# runSql(sqlScript, hideOutput) will hide standard output for any
# defined parameter "hideOutput"
# runSql(sqlScript) will print standard output
sub runSql
{
  EMD_PERL_DEBUG("convert_common.runSql(): *** START ***");
  
  my ($sql_string) = $_[0];
  my $sql_string_debug = &filterDBCredentialConvertCommon($sql_string);
  EMD_PERL_DEBUG("convert_common.runSql(): SQL:\n$sql_string_debug");

  (my $fh, my $filename) = &create_temp_file();

  if($NT)
  {
    $filename = "$TEMP\\"."convert_common.$$";
    #$filename = "$TEMP\\".getBasename($filename);
    EMD_PERL_DEBUG("convert_common.runSql(): temp file: $filename");
    open(SQL_SCRIPT, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog >$filename")
      || die "Cannot open pipe for SQL_SCRIPT";
    #print SQL_SCRIPT "spool $filename; \n";
    print SQL_SCRIPT $sql_string;
    close SQL_SCRIPT || die "Bad SQL_SCRIPT";
  }
  else
  {
    open(SQL_SCRIPT, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog >$filename")
      || die "Cannot open pipe for SQL_SCRIPT";
    print SQL_SCRIPT $sql_string;
    close SQL_SCRIPT || die "Bad SQL_SCRIPT";
  }

  #Open the temp file to print output to standard output and debug trace file
  open (OUT_PUT, "$filename") || die "Unable to open tempfile for OUT_PUT\n";
  my @output_content = <OUT_PUT>;
  my $output_string = "@output_content";
  close OUT_PUT;
  
  if(!defined($_[1]))
  {
    if(defined($DEFER_OUTPUT) && ($DEFER_OUTPUT eq 'Y'))
    {
      &deferOutput($output_string);
    }
    else
    {
      print STDOUT $output_string;
    }
  }
  
  EMD_PERL_DEBUG("convert_common.runSql(): OUT_PUT:\n$output_string");
  &parseOutputConvertCommon($output_string);
  
  EMD_PERL_DEBUG("convert_common.runSql(): *** END ***");

  return ($fh, $filename);
}

#******************************************
#   Private Subroutines
#   The names end with ConvertCommon
#******************************************

# Filter out DB credential from sql script, which is written to trace file
# filterDBCredentialConvertCommon(sqlScript)
sub filterDBCredentialConvertCommon
{
  my ($sqlScript) = @_;

  my $position1 = index($sqlScript, "CONNECT");
  my $position2 = index($sqlScript, "AS");
  my $replacedLength = $position2 - $position1 - 9;
  substr($sqlScript, $position1 + 8, $replacedLength) = "username/password(Hiden intentionally)";

  return $sqlScript;
}

# Parse the output string to detect ORA- errors
# parseOutputConvertCommon(output)
sub parseOutputConvertCommon
{
  EMD_PERL_DEBUG("convert_common.parseOutputConvertCommon(): *** START ***");

  my ($output) = @_;
  
  if ($output eq "")
  {
    EMD_PERL_ERROR("convert_common.parseOutputConvertCommon(): Output file is empty!");
  }

  if($output !~ /ORA-[0-9]/)
  {
    EMD_PERL_DEBUG("convert_common.parseOutputConvertCommon(): No Error found.");
  }
  elsif (($output =~ /ORA-00278/) || ($output =~ /ORA-00279/) ||
        ($output =~ /ORA-00280/) || ($output =~ /ORA-00289/) || 
        ($output =~ /ORA-32006/) || ($output =~ /ORA-32004/))
  {
    EMD_PERL_ERROR("convert_common.parseOutputConvertCommon(): ORA- messages!");
  }
  else
  {
    EMD_PERL_ERROR("convert_common.parseOutputConvertCommon(): ORA- ERROR! Exit! See output log.");
    exit(1);
  }
  
  if($output !~ /RMAN-[0-9]/)
  {
    EMD_PERL_DEBUG("convert_common.parseOutputConvertCommon(): No Error found.");
  }
  else
  {
    EMD_PERL_ERROR("convert_common.parseOutputConvertCommon(): RMAN- ERROR! Exit! See output log.");
    exit(1);
  }
  
  EMD_PERL_DEBUG("convert_common.parseOutputConvertCommon(): *** END ***");
}

# Write the output to a file stored in a specified location
# OUTPUT_LOC and INDEX are global variables
# deferOutput(output)
sub deferOutput
{
  EMD_PERL_DEBUG("convert_common.deferOutput(): *** START ***");
  
  my ($output) = @_;
  
  my $outputFile = File::Spec->catfile($OUTPUT_LOC, "output_file_".${INDEX});
  open(OUT_PUT_FILE, ">$outputFile") || die "Cannot open $outputFile";
  print OUT_PUT_FILE "$output";
  close OUT_PUT_FILE || die "Cannot close $outputFile"; 
  $INDEX ++;
  
  EMD_PERL_DEBUG("convert_common.deferOutput(): *** END ***");
}

1;

# End of the Program