# $Header: emdb/sysman/admin/scripts/db/dataUtilities/datapump_job.pl /st_emdbsa_11.2/3 2009/03/21 05:46:43 rasundar Exp $
#
# datapump_job.pl
# 
# Copyright (c) 2003, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      datapump_job.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      <short description of component this file declares/defines>
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    rasundar    03/18/09 - remove hardcoded password in test code
#    rimmidi     07/30/08 - Code slap from 10205 to 11.2DBControl
#    rpattabh    02/28/08 - bug 6843219: do not reset job_state returned by
#                           get_status
#    rgiroux     06/10/05 - 
#    rgiroux     06/03/05 - add error messages 
#    rgiroux     05/23/05 - fix for bug 4369796; fail jobs on null handle 
#    rgiroux     05/23/05 - update with MAINSA version
#    rgiroux     05/04/05 - fix for bug 4345325; don't allow nonprived users 
#                           to turn on tracing 
#    rgiroux     07/15/04 - fix for bug 3640595; fail job on data pump error 
#    ngade       04/01/04 - tts support and cleanup
#    rgiroux     02/13/04 - fix for bug 3422396; use new get_status procedure 
#    rgiroux     11/21/03 - change job_state_nr back to job_state 
#    rgiroux     11/21/03 - fix for bug 3271191; allow restart of job while 
#                           it's being monitored 
#    rgiroux     11/14/03 - enhance debugging 
#    rgiroux     11/13/03 - fixes for bugs 3077896 and 3247523; get more 
#                           information on job failure and enable tracing if 
#    rgiroux     10/23/03 - fix for bug 3176547; detect bad data pump jobs 
#    npamnani    10/02/03 - loop on condition 
#    ngade       02/21/03 - implement server changes
#    ngade       02/06/03 - flush stdout, cleanup
#    ngade       01/31/03 - handle errors, mode
#    ngade       01/17/03 - ngade_more_dp_changes
#    ngade       01/17/03 - Creation
# 

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

use strict;
use DBI qw(:sql_types);
BEGIN
{
  if ($^O eq "VMS") { eval 'use VMS::Filespec'; die if $@ }     # compile time
}

## global variables.
use vars qw($job_state $is_prived);

## Datapump Job constants
use constant  JOB_NOT_RUNNING   => 'NOT RUNNING';
use constant  JOB_IDLING        => 'IDLING';
use constant  JOB_DEFINING      => 'DEFINING';
use constant  JOB_EXECUTING     => 'EXECUTING';


## GET_STATUS SQL
my $sql_get_status = q{
declare  
  get_status_wip_error_message CONSTANT NUMBER := 13;
  get_status_timeout CONSTANT NUMBER := -1;
  ind           number;
  status        ku$_status;
  job_state     varchar2(30);
  le_wip        ku$_LogEntry;
  le_error      ku$_LogEntry;

  procedure display_status(msg in varchar2) is
      msg1 varchar2(255);
      len integer := length(msg);
      i integer := 1;
  begin
      dbms_output.enable(1000000);
      loop
        msg1 := substr(msg, i, 255);
        dbms_output.put_line(msg1);
        len := len - 255;
        i := i + 255;
      exit when len <= 0;
      end loop;
  end display_status;
    
  begin
    dbms_datapump.get_status(:handle, get_status_wip_error_message, get_status_timeout, job_state, status);
    :job_state := job_state;

    le_wip := status.wip;
    if le_wip is not null then
      ind := le_wip.FIRST;
      while ind is not null loop
        display_status(le_wip(ind).LogText);
        ind := le_wip.NEXT(ind);
      end loop;
    end if;

    le_error := status.error;
    if le_error is not null then
      ind := le_error.FIRST;
      :error_status := 'true';
      while ind is not null loop
        display_status(le_error(ind).LogText);
        ind := le_error.NEXT(ind);
      end loop;
    end if;
  end;
};

###################################
## MAIN METHOD TO START A DATAPUMP JOB
###################################
sub start_datapump_job
{
  ##
  # 1. connect db.
  # 2. attach job
  # 3. start job
  # 4. read job status
  # 5. disconnect db.
  ##
  my($user, $password, $role, $job_name) = @_;  
  my $dbh = connect_database($user, $password, $role);  
  my ($handle) = attach_job($dbh, $job_name, $user);
  if(defined $handle)
  {
    print_debug("Data Pump: handle: $handle \n");  
    start_job($dbh, $job_name, $handle);
    get_job_info($dbh, $job_name, $handle);
    disconnect_database($dbh);
  }
  else
  {
    print_error("Data Pump: attach_job failed\n");
    exit(1);
  }
}

#####################################
##   ATTACH JOB   
#####################################
sub attach_job()
{
  my ($dbh, $job_name, $user) = @_;
  
  ## ATTACH JOB SQL
  my $sql_attach_job = q{
    declare
      sqlcmd        varchar2(200);
      sqlcmd_validate        varchar2(200);
      sqlcmd_validate_state        varchar2(200);
      tmp_count     number;
      done          boolean;
      valid_job     boolean;
      h1            integer;
      trace         number;

      begin
       dbms_output.enable(1000000);
       tmp_count := 0;
       done := false;
       valid_job := true;
       trace := :set_trace;
           sqlcmd_validate := :sqlcmd_validate;
           execute immediate sqlcmd_validate into tmp_count;

           dbms_output.put_line('');
           if tmp_count <= 0 then
                dbms_output.put_line('ERROR: No data pump job named "' || :job_name || '" exists in the database.');
                valid_job := false;
                done := true;
           end if;

           tmp_count := 0;
           if valid_job then
               sqlcmd_validate_state := :sqlcmd_validate_state;
               execute immediate sqlcmd_validate_state into tmp_count;

               dbms_output.put_line('');
               if tmp_count <= 0 then
                    dbms_output.put_line('ERROR: Invalid data pump job state; one of: DEFINING or EXECUTING.');
                    dbms_output.put_line('       Wait a few moments and check the log file (if one was used) for more details.');
                    valid_job := false;
                    done := true;
               end if;
           end if;
       

           while NOT done
             loop
                begin
                  sqlcmd := :sqlcmd;
                  execute immediate sqlcmd into tmp_count;
                  if tmp_count > 0 then
                      done := true;
                  end if;
                end;
              end loop;

           if valid_job = true then
               begin
                 h1 := dbms_datapump.attach(:job_name);
                 if trace = 1 then
                   dbms_datapump.set_parameter(h1, 'TRACE', 16711936);
                 end if;
                :handle := h1;
               end;
           end if;
    end;
  };  

  
  my $dbcur = prepare_statement($dbh, $sql_attach_job, $job_name);

  ## Execute
  my $handle;

  $job_state = JOB_NOT_RUNNING;
  my $job_state_idling = JOB_IDLING;
  my $job_state_defining = JOB_DEFINING;
  my $job_state_executing = JOB_EXECUTING;
  
  my $sqlcmd = "select count(*) from dba_datapump_jobs where owner_name='$user' and job_name='$job_name' and (state='$job_state' or state='$job_state_idling')";
  my $sqlcmd_validate = "select count(*) from dba_datapump_jobs where owner_name='$user' and job_name='$job_name'";
  my $sqlcmd_validate_state = "select count(*) from dba_datapump_jobs where owner_name='$user' and job_name='$job_name' and state not in ('$job_state_defining', '$job_state_executing')";

  print_debug("Data Pump:sqlcmd $sqlcmd\n");
  print_debug("Data Pump:sqlcmd $sqlcmd_validate\n");

  $dbcur->bind_param(":job_name", $job_name);
  $dbcur->bind_param(":sqlcmd", $sqlcmd); 
  $dbcur->bind_param(":sqlcmd_validate", $sqlcmd_validate); 
  $dbcur->bind_param(":sqlcmd_validate_state", $sqlcmd_validate_state); 

  my $trace = 0;
  
  if(EMAGENT_isPerlDebugEnabled())
  {
    if(defined $is_prived)
    {
      print_debug("Setting Debug on\n");
      $trace = 1;
    }
  }
  $dbcur->bind_param(":set_trace", $trace, SQL_NUMERIC);

  $dbcur->bind_param_inout(":handle", \$handle, SQL_INTEGER);

  $dbcur->execute || get_wip_err_status_msg($dbh, $dbcur, $handle, $job_name);

  $dbcur->finish();
  my @output = $dbh->func( 'dbms_output_get' );

  my $output;
  foreach $output(@output)
  {
    print STDOUT "$output\n";
  }
  return $handle;
}  


#####################################
##   STARTING JOB
#####################################
sub start_job()
{
  print_debug("Data Pump: Starting the Job\n");
  my ($dbh, $job_name, $handle) = @_;
  
  ## START JOB SQL
  my $sql_start_job = q{
  begin
    dbms_datapump.start_job(:handle);
  end;
  };


  my $dbcur = prepare_statement($dbh, $sql_start_job, $job_name);    
  $dbcur->bind_param(":handle", $handle);
  $dbcur->execute || get_wip_err_status_msg($dbh, $dbcur, $handle, $job_name);

}

##############################################
##   JOB STARTED SUCCESSFULLY - GET JOB STATUS
##############################################
sub get_job_info()
{
  print_debug("Data Pump: Getting the Log Information\n");
  my ($dbh, $job_name, $handle, $error_status) = @_;

  my $count = 0;
  print_debug( " job state: $job_state \n");

  while ($job_state !~ /COMPLETED/i  && $job_state !~ /STOPPED/i)
  {
    my $dbcur = prepare_statement($dbh, $sql_get_status, $job_name);  
    $dbcur->bind_param(":handle", $handle);
    $dbcur->bind_param_inout(":job_state", \$job_state, 2);    
    $dbcur->bind_param_inout(":error_status", \$error_status, 10);    
    execute_statement($dbh, $dbcur, $job_name);  
    $count++;

    print_debug( " job state: $job_state \n");
    $dbcur->finish();
    my $output;
    my @output = $dbh->func( 'dbms_output_get' );
    foreach $output(@output)
    {
      print STDOUT "$output\n";
    }
  }

  print_debug("Data Pump: PL/SQL count: $count : job_state: $job_state\n");

  #######################   
  ## Fail job if the job was STOPPED.
  ## TODO: Indicate the EM Job that the datapump job was suspended/stopped.
  ## Note: Distinguish between stopped and suspended jobs:
  ##  - query the dba_datapump_job view and if it returns no rows then the job was stopped else the job was suspended.
  #######################   
  if($job_state =~ /STOPPED/i)
  {
    print_error("Data Pump: Job Stopped\n");
    exit(1);
  }

  if($error_status =~ /TRUE/i)
  {
    print STDOUT "Execution errors encountered.\n\n";
    print STDOUT "Job state: $job_state\n";
    exit(1);
  }

  print STDOUT "Job state: $job_state\n";
}

sub start_datapump_job_vms
{
  ##
  # 1. attach job
  # 2. start job
  # 3. read job status
  ##

  $| = 1; # Flush Buffers 

  my($user, $password, $role, $job_name, $oracle_home, $oracle_sid) = @_;
  
  my ($handle) = attach_job_vms($user, $password, $role, $job_name, $oracle_home, $oracle_sid);
  if(defined $handle)
  {
    print_debug("Data Pump: handle: $handle \n");  
    start_job_vms($user, $password, $role, $job_name, $oracle_home, $oracle_sid);
    get_job_info_vms($user, $password, $role, $job_name, $oracle_home, $oracle_sid);
  }
  else
  {
    print_error("Data Pump: attach_job failed\n");
    exit(1);
  }
}

sub attach_job_vms
{
  my($user, $password, $role, $job_name, $oracle_home, $oracle_sid) = @_;

  my $job_state = JOB_NOT_RUNNING;
  my $job_state_idling = JOB_IDLING;
  my $sqlcmd = "select count(*) from dba_datapump_jobs where owner_name=''$user'' and job_name=''$job_name'' and (state=''$job_state'' or state=''$job_state_idling'')";
  my $sqlcmd_validate = "select count(*) from dba_datapump_jobs where owner_name=''$user'' and job_name=''$job_name'' ";
  my $sqlcmd_validate_state = "select count(*) from dba_datapump_jobs where owner_name=''$user'' and job_name=''$job_name'' and state not in (''DEFINING'',''EXECUTING'')";
    
  my $set_trace = 0;
  if(EMAGENT_isPerlDebugEnabled())
  {
    print_debug("Setting Debug on \n");
    $set_trace = 1;
  }

  ## ATTACH JOB SQL
  my $sql_attach_job = "
    declare
      sqlcmd        varchar2(200);
      sqlcmd_validate        varchar2(200);
      sqlcmd_validate_state        varchar2(200);
      tmp_count     number;
      done          boolean;
      valid_job     boolean;
      h1            integer;
      trace         number;
  
    begin
       dbms_output.enable(1000000);
       tmp_count := 0;
       done := false;
       valid_job := true;
       trace := $set_trace;
       sqlcmd_validate := \'$sqlcmd_validate\';
       execute immediate sqlcmd_validate into tmp_count;
  
       dbms_output.put_line('');
       if tmp_count <= 0 then
            dbms_output.put_line('ERROR: No data pump job named  $job_name exists in the database');
            valid_job := false;
            done := true;
       end if;
  
       tmp_count := 0;
       if valid_job then
           sqlcmd_validate_state := \'$sqlcmd_validate_state\';
           execute immediate sqlcmd_validate_state into tmp_count;
  
           dbms_output.put_line('');
           if tmp_count <= 0 then
                dbms_output.put_line('ERROR: Invalid data pump job state; one of: DEFINING, or EXECUTING');
                valid_job := false;
                done := true;
           end if;
       end if;
       
  
       while NOT done
         loop
            begin
              sqlcmd := \'$sqlcmd\';
              execute immediate sqlcmd into tmp_count;
              if tmp_count > 0 then
                  done := true;
              end if;
            end;
          end loop;
  
       if valid_job = true then
           begin
             h1 := dbms_datapump.attach('$job_name');
             if trace = 1 then
               dbms_datapump.set_parameter(h1, 'TRACE', 16711936);
             end if;
            dbms_output.put_line('Handle = ' || h1);
           end;
       end if;
    end;
    /\n";

  my $mytime = time();
  my $vmshome = vmsify($oracle_home."/");
  my $tmp1dir = $ENV{ORA_AGENT_TMP};
  my $batchq = $ENV{ORA_EMD_BATCH_QUEUE};
  my $sqlfile1 = $tmp1dir."sqlscript1.S$mytime";
  my $comfile1 = $tmp1dir."comfile1.S$mytime";
  my $outfile1 = $tmp1dir."outfile1.S$mytime";
  my $jobst = "";

  open (SQL_WRITER, ">$sqlfile1") || die "Unable to create temp sql file\n";
  print SQL_WRITER "$sql_attach_job";
  close SQL_WRITER;

  open (COM_WRITER, ">$comfile1") || die "Unable to create temp sql file\n";
  print COM_WRITER "\$ \@"."$vmshome"."orauser.com "."$oracle_sid\n";
  print COM_WRITER "\$ define sys\$output $outfile1\n";
  print COM_WRITER "\$ sqlplus \"/nolog\" \n";

  $role = lc $role;
  if ($role eq "sysdba")
  {
      print COM_WRITER "connect $user/$password as $role\n";
  }
  else
  {
      print COM_WRITER "connect $user/$password\n";
  }

  print COM_WRITER "set serveroutput on \n";
  print COM_WRITER "\@$sqlfile1\n";
  print COM_WRITER "exit;\n";
  print COM_WRITER "\$ deassign sys\$output\n";
  close COM_WRITER;


  $mytime = time();
  my $batchlog= $tmp1dir."DP$mytime".".log";
  my $cmdfile2 = $tmp1dir."DP$mytime".".com";
  open (CMDFIL1, ">$cmdfile2") || die "Unable to open attach job tmp command file\n";
  print CMDFIL1  "\$ define sys\$output nl: \n";
  print CMDFIL1  "\$ submit/que=$batchq/name=DP$mytime/log=$batchlog $comfile1\n";
  print CMDFIL1  "\$ SYNCH/que=$batchq DP$mytime\n";
  close CMDFIL1;
  my $dummy = `\@$cmdfile2`;

  open (DB_OUTPUT, "$outfile1") || die "Unable to open attach job output file\n";
  my $output = "";
  my $handle = "";
  while (<DB_OUTPUT>)
  {
    if( $_ =~ /Handle = (.*)/i)
    { 
      $handle = $1;
    }
    $output = $output.$_;
  }
  $output = $output."\n";
  close DB_OUTPUT;

  my $pdbg = $ENV{EMD_PERL_DEBUG};
  if ($pdbg eq "")
  {
    unlink $sqlfile1;
    unlink $comfile1;
    unlink $outfile1;
    unlink $batchlog;
    unlink $cmdfile2;
  }
  
  return $handle;
}


sub start_job_vms
{
  my($user, $password, $role, $job_name, $oracle_home, $oracle_sid) = @_;

  my $mytime = time();
  my $vmshome = vmsify($oracle_home."/");
  my $tmp1dir = $ENV{ORA_AGENT_TMP};
  my $batchq = $ENV{ORA_EMD_BATCH_QUEUE};
  my $sqlfile1 = $tmp1dir."sqlscript1.S$mytime";
  my $comfile1 = $tmp1dir."comfile1.S$mytime";
  my $outfile1 = $tmp1dir."outfile1.S$mytime";
  my $jobst = "";

  my $set_trace = 0;
  if(EMAGENT_isPerlDebugEnabled())
  {
    print_debug("Setting Debug on \n");
    $set_trace = 1;
  }

  ## START JOB SQL
  my $sql_start_job = "
  declare
      h1            integer;
     trace          number;

  begin
      h1 := dbms_datapump.attach('$job_name');
      trace := $set_trace;
      if trace = 1 then
         dbms_datapump.set_parameter(h1, 'TRACE', 16711936);
      end if;
      dbms_datapump.start_job(h1);
      dbms_output.put_line('JOB STARTED');
  end;
  /\n";


  print_debug("Data Pump: Starting the Job\n");
  open (SQL_WRITER, ">$sqlfile1") || die "Unable to create temp sql file\n";
  print SQL_WRITER "$sql_start_job";
  close SQL_WRITER;

  open (COM_WRITER, ">$comfile1") || die "Unable to create temp sql file\n";
  print COM_WRITER "\$ \@"."$vmshome"."orauser.com "."$oracle_sid\n";
  print COM_WRITER "\$ define sys\$output $outfile1\n";
  print COM_WRITER "\$ sqlplus \"/nolog\" \n";

  $role = lc $role;
  if ($role eq "sysdba")
  {
      print COM_WRITER "connect $user/$password as $role\n";
  }
  else
  {
      print COM_WRITER "connect $user/$password\n";
  }

  print COM_WRITER "set serveroutput on \n";
  print COM_WRITER "\@$sqlfile1\n";
  print COM_WRITER "exit;\n";
  print COM_WRITER "\$ deassign sys\$output\n";
  close COM_WRITER;


  $mytime = time();
  my $batchlog= $tmp1dir."DP$mytime".".log";
  my $cmdfile2 = $tmp1dir."DP$mytime".".com";
  open (CMDFIL1, ">$cmdfile2") || die "Unable to open start job tmp command file\n";
  print CMDFIL1  "\$ define sys\$output nl: \n";
  print CMDFIL1  "\$ submit/que=$batchq/name=DP$mytime/log=$batchlog $comfile1\n";
  print CMDFIL1  "\$ SYNCH/que=$batchq DP$mytime\n";
  close CMDFIL1;
  my $dummy = `\@$cmdfile2`;

  open (DB_OUTPUT, "$outfile1") || die "Unable to open start job output file\n";
  my $output = "";
  while (<DB_OUTPUT>)
  {
    if( $_ =~ /JOB STARTED(.*)/i)
    { 
      $jobst = "DONE";
    }
    $output = $output.$_;
  }
  $output = $output."\n";
  close DB_OUTPUT;

  my $pdbg = $ENV{EMD_PERL_DEBUG};
  if ($pdbg eq "")
  {
    unlink $sqlfile1;
    unlink $comfile1;
    unlink $outfile1;
    unlink $batchlog;
    unlink $cmdfile2;
  }

# Error if $jobst is null, do same action as what is done by function 
# handle_db_error at the end of this file 
  if ($jobst eq "")
  {
    print STDOUT "Execute Failed: \n";
    exit(1);
  }
}


sub get_job_info_vms
{
  $job_state = JOB_NOT_RUNNING;

  my($user, $password, $role, $job_name, $oracle_home, $oracle_sid) = @_;

  my $mytime = time();
  my $vmshome = vmsify($oracle_home."/");
  my $tmp1dir = $ENV{ORA_AGENT_TMP};
  my $batchq = $ENV{ORA_EMD_BATCH_QUEUE};
  my $sqlfile1 = $tmp1dir."sqlscript1.S$mytime";
  my $comfile1 = $tmp1dir."comfile1.S$mytime";
  my $outfile1 = $tmp1dir."outfile1.S$mytime";

## GET_STATUS SQL
#NOTE: Unix has value of get_status_timeout as -1. For VMS, this causes a hang
#      in the dbms_datapump.get_status() call when the job state is IDLING
#      Changing the value to 1
  $sql_get_status = "
  declare  
    get_status_wip_error_message CONSTANT NUMBER := 13;
    get_status_timeout CONSTANT NUMBER := 1;
    ind           number;
    status        ku\$_status;
    job_state     varchar2(30);
    le_wip        ku\$_LogEntry;
    le_error      ku\$_LogEntry;
    h1            integer;

    procedure display_status(msg in varchar2) is
        msg1 varchar2(255);
        len integer := length(msg);
        i integer := 1;
    begin
        dbms_output.enable(1000000);
        loop
          msg1 := substr(msg, i, 255);
          dbms_output.put_line(msg1);
          len := len - 255;
          i := i + 255;
        exit when len <= 0;
        end loop;
    end display_status;
      
    begin
      h1 := dbms_datapump.attach('$job_name');
      status := dbms_datapump.get_status(h1, get_status_wip_error_message, get_status_timeout);
      dbms_output.put_line('JOB_STATUS = ' || status.job_status.state);

      le_wip := status.wip;
      if le_wip is not null then
        ind := le_wip.FIRST;
        while ind is not null loop
          display_status(le_wip(ind).LogText);
          ind := le_wip.NEXT(ind);
        end loop;
      end if;

      le_error := status.error;
      if le_error is not null then
        ind := le_error.FIRST;
        while ind is not null loop
          display_status(le_error(ind).LogText);
          ind := le_error.NEXT(ind);
        end loop;
      end if;
    end;
   /\n";


  print_debug("Data Pump: Getting the Log Information\n");

  while ($job_state !~ /COMPLETED/i  && $job_state !~ /STOPPED/i)
  {
    open (SQL_WRITER, ">$sqlfile1") || die "Unable to create temp sql file\n";
    print SQL_WRITER "$sql_get_status";
    close SQL_WRITER;

    open (COM_WRITER, ">$comfile1") || die "Unable to create temp sql file\n";
    print COM_WRITER "\$ \@"."$vmshome"."orauser.com "."$oracle_sid\n";
    print COM_WRITER "\$ define sys\$output $outfile1\n";
    print COM_WRITER "\$ sqlplus \"/nolog\" \n";

    $role = lc $role;
    if ($role eq "sysdba")
    {
        print COM_WRITER "connect $user/$password as $role\n";
    }
    else
    {
        print COM_WRITER "connect $user/$password\n";
    }

    print COM_WRITER "set serveroutput on \n";
    print COM_WRITER "\@$sqlfile1\n";
    print COM_WRITER "exit;\n";
    print COM_WRITER "\$ deassign sys\$output\n";
    close COM_WRITER;


    $mytime = time();
    my $batchlog= $tmp1dir."DP$mytime".".log";
    my $cmdfile2 = $tmp1dir."DP$mytime".".com";
    open (CMDFIL1, ">$cmdfile2") || die "Unable to open job status tmp command file\n";
    print CMDFIL1  "\$ define sys\$output nl: \n";
    print CMDFIL1  "\$ submit/que=$batchq/name=DP$mytime/log=$batchlog $comfile1\n";
    print CMDFIL1  "\$ SYNCH/que=$batchq DP$mytime\n";
    close CMDFIL1;
    my $dummy = `\@$cmdfile2`;

    open (DB_OUTPUT, "$outfile1") || die "Unable to open job status output file\n";
    my $output = "";
    while (<DB_OUTPUT>)
    {
      if( $_ =~ /JOB_STATUS = (.*)/i) 
      { 
        $job_state = $1;
      }
# On VMS, each loop is a different session, therefore, at some point, when 
# the job completes, we are no longer going to be able to attach to it.
# The following error message is displayed when the sql script for status
# as above is run:
#     ERROR at line 1:
#     ORA-31626: job does not exist
#     ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
#     ORA-06512: at "SYS.DBMS_DATAPUMP", line 820
#     ORA-06512: at "SYS.DBMS_DATAPUMP", line 2825
#     ORA-06512: at line 27
# This means the job did complete, because our previous attempt to attach
# to the job was successful when we STARTED the job.. We won't be here otherwise
      if( $_ =~ /ORA-31626: (.*)/i) 
      { 
        $job_state = "COMPLETED";
      }
      print STDOUT;
    }
    close DB_OUTPUT;

    my $pdbg = $ENV{EMD_PERL_DEBUG};
    if ($pdbg eq "")
    {
      unlink $sqlfile1;
      unlink $comfile1;
      unlink $outfile1;
      unlink $batchlog;
      unlink $cmdfile2;
    }
# Sleep for 60 secs before looking again for job status
    sleep 60;

  }

  #######################   
  ## Fail job if the job was STOPPED.
  ## TODO: Indicate the EM Job that the datapump job was suspended/stopped.
  ## Note: Distinguish between stopped and suspended jobs:
  ##  - query the dba_datapump_job view and if it returns no rows then the job was stopped else the job was suspended.
  #######################   
  if($job_state =~ /STOPPED/i)
  {
    print_error("Data Pump: Job Stopped\n");
    exit(1);
  }

  print STDOUT "\n If you see error ORA-31626 above, and Job state below is  COMPLETED, it is normal. \n";
  print STDOUT " There's no cause for alarm.\n\n";
  print STDOUT "\nJob state: $job_state\n";
}


###################################################
##   GET WIP/ERROR MESSAGES FOR THE JOB ON FAILURE
###################################################
sub get_wip_err_status_msg
{
  print_debug("Data Pump: Error Occurred: Getting WIP/ERROR/STATUS Messages\n");  
  my($dbh, $dbcur, $handle, $job_name, $error_status) = @_;

  my $dbcur = prepare_statement($dbh, $sql_get_status, $job_name);
  $dbcur->bind_param(":handle", $handle);
  $dbcur->bind_param_inout(":job_state", \$job_state, 2);    
  $dbcur->bind_param_inout(":error_status", \$error_status, 10);    
  execute_statement($dbh, $dbcur, $job_name);  
  $dbcur->finish();

  my $output;
  my @output = $dbh->func( 'dbms_output_get' );
  
  foreach $output(@output)
  {
    print STDOUT "$output\n";
  }
}

##############################################
## PERL TESTING
## Submit a export job (with abort-step = -1)
## then call test(). Modify parameters.
## At command line set EMDROOT and run command
## runperl datapump.pl
##############################################
## expdptest();
sub expdptest()
{
  my $db_user     = "SYSTEM";
  ### fill in your password here
  my $db_passwd   = "";
  my $db_role     = "normal";
  my $job_name    = "TEST1";
  my $db_10_or_higher = "true";
  &set_env_var($ENV{ORACLE_HOME}, $ENV{ORACLE_SID}, $db_10_or_higher);
  print_debug("Starting job...\n");
  &start_datapump_job($db_user, $db_passwd, $db_role, $job_name);
}
1;