# DCips.ctl:202:Collects Oracle Incident Packaging Service Information
# $Id: DCips.ctl,v 1.10 2015/10/20 13:17:29 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DCips.ctl,v 1.10 2015/10/20 13:17:29 RDA Exp $
#
# Change History
# 20151016  JJU  Extend 12c support.

=head1 NAME

DB:DCips - Collects Oracle Incident Packaging Service Information

=head1 DESCRIPTION

This module collects the Collects Oracle Incident Packaging Service-related
information.

The following reports can be generated and are regrouped under
C<Incident Packaging Service>:

=cut

if !${B_LOCAL/P}
 return

echo tput('bold'),'Processing DB.IPS module ...',tput('off')

# Initialization
var $DATABASE     = ${I_DATABASE/P}
var $ORACLE_HOME  = $DATABASE->get_prime('D_ORACLE_HOME')
var $ORACLE_SID   = nvl(getSid($DATABASE->get_first('T_ORACLE_SID'),\
                               $DATABASE->get_first('T_DB_NAME')),'')
var $ALERT_TAIL   = ${N_ALERT_TAIL:30000}
var $LOG_AGE      = ${R_TRACE_AGE/T:15}
var $LOG_TRESHOLD = ${N_TRACE_TRESHOLD:20480}
var $LOG_RATIO    = max(1,${R_TRACE_RATIO/T:10})
var $BDUMP_MAX    = ${N_TRACE_BDUMP:2}
var $BDUMP_VOLUME = ${R_TRACE_BVOLUME/T:100}
var $UDUMP_AGE    = ${N_TRACE_UDUMP:2}
var $UDUMP_VOLUME = ${R_TRACE_UVOLUME/T:10}

var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'
pretoc '^1:RDBMS'
pretoc '1+:Incident Packaging Service'

# Load the common macros
run DB:DBinfo()

# Get the dump destinations
var $BDUMP_DIR = get_bdump()
var $UDUMP_DIR = get_udump()
var $ADR_HOME  = get_adr_home()
var $TRACE_DIR = cond($ADR_HOME,catDir($ADR_HOME,'trace'),$BDUMP_DIR)

# Define a macro to estimate the size after compression
macro get_size
{var ($fil) = @arg
 import $LOG_RATIO,$LOG_TRESHOLD
 keep $LOG_RATIO,$LOG_TRESHOLD
 var $siz = getSize($fil)
 if expr('>',$siz,$LOG_TRESHOLD)
  var $siz = expr('/',$siz,$LOG_RATIO)
 return $siz
}

# Macros to format a time stamp
macro fmt_time
 return replace(getLocalTime($arg[0]),' ','&nbsp;',true)

# Macro for reading the alert.log
macro search_alert
{var (\@tim,$fil,$xml) = @arg
 var %sta = ('fil',$fil)
 var @err = ()

 debug ' Inside IPS module, analyzing the alert.log data (can take time)'
 if createBuffer('ALS','R',$fil)
 {# Define parsing macros
  code parse_err = -1
  {if !$sta{'err'}
   {var $sta{'err'} = true
    call push(@err,[$tim = eval(&log_tsp($sta{'cur'}))])
    call push(@tim,$tim)
   }
   call push(@err,line)
  }

  code parse_str = -1
  {var $tim = [last]
   incr $sta{'str'}
   # Add the event in the error summary
   if !$sta{'err'}
   {var $sta{'err'} = true
    call push(@err,$tim)
   }
   call push(@err,'Instance was restarted')
  }

  # Define the code blocks to parse and to decode a time stamp
  if $xml
  {code parse_tsp = -1
   {var $sta{'cur'} = $sta{'end'} = $tim = \
      first(match(line,"<msg time='([^']+)'"))
    var $sta{'beg'} = nvl($sta{'beg'},$tim)
    var $sta{'err'} = false
   }
   code log_tsp
   {var ($yer,$mon,$day,$hur,$min,$sec) = \
      match(last,'^(\d{4})-(\d+)-(\d+)T(\d+):(\d+):(\d+)')
    decr $mon
    call mktime($sec,$min,$hur,$day,$mon,$yer)
   }
  }
  elsif ${RDA.B_VMS}
  {code parse_tsp = -1
   {var $sta{'cur'} = $sta{'end'} = $tim = replace(parseHit(0),'^\s','0')
    var $sta{'beg'} = nvl($sta{'beg'},$tim)
    var $sta{'err'} = false
   }
   code log_tsp
   {var ($day,$mon,$yer,$hur,$min,$sec) = split('-|\s+|:',last)
    call mktime($sec,$min,$hur,$day,$mon,$yer)
   }
  }
  else
  {code parse_tsp = -1
   {var $sta{'cur'} = $sta{'end'} = $tim = line
    var $sta{'beg'} = nvl($sta{'beg'},$tim)
    var $sta{'err'} = false
   }
   code log_tsp
   {var (undef,$mon,$day,$hur,$min,$sec,$yer,$oth) = split('\s+|:',last)
    if !match($yer,'\d+')
     var $yer = $oth
    call mktime($sec,$min,$hur,$day,$mon,$yer)
   }
  }

  # Parse the alert.log
  call parseReset()
  if $xml
  {call parsePattern('TOP','<msg time=',&parse_tsp())
   call parseBegin('TOP','^\s*<txt>','TXT')
   call parseInfo('TXT','beg',parseReplace(replace(line,'^\s*<txt>')))
   call parseInfo('TXT','flp',true)
   call parsePattern('TXT',\
     '^ORA-0+600.*?\[17059\]',&parse_err(),\
     'Starting ORACLE instance',&parse_str(&log_tsp($sta{'cur'})))
   call parseEnd('TXT','^\s*</txt>')
  }
  elsif ${RDA.B_VMS}
   call parsePattern('TOP',\
     '^(\s?\d{1,2}-[A-Za-z]{3}-\d{4}\s+\d{2}:\d{2}:\d{2})\.\d+',&parse_tsp(),\
     '^ORA-0+600.*?\[17059\]',&parse_err())
  else
   call parsePattern('TOP',\
     '^(Mon|Tue|Wed|Thu|Fri|Sat|Sun)\b',&parse_tsp(),\
     '^ORA-0+600.*?\[17059\]',&parse_err(),\
     'Starting ORACLE instance',&parse_str(&log_tsp($sta{'cur'})))
  call parse('ALS')
  var $sta{'lin'} = inputLine('ALS')

  # Report the analysis
  call write_alert_results(\%sta,\@err,\
    eval(&log_tsp($sta{'beg'})),eval(&log_tsp($sta{'end'})))
 }
}

# Macro for writing the alert.log summary
macro write_alert_results
{var (\%sta,\@err,$beg,$end) = @arg
 import $TOP,$TOC

 # Define a macro to report events, while merging duplicates
 macro write_nodup
 {# Define a macro to report an event
  macro write_event
  {var ($beg,$end,$cnt,$evt) = @arg
   if $cnt
   {incr $cnt
    write '|',fmt_time($beg),' |',\
              fmt_time($end),' | ',$cnt,'|',$evt,' |'
   }
   else
    write '|',fmt_time($beg),' |||',$evt,' |'
  }

  # Merge duplicates
  var ($cnt,$old,$new) = (0)
  loop $lin (@arg,[])
  {if ref($lin) # Compare event list
   {if compare('eq',$new,$old)
    {incr $cnt
     var ($end,$new) = ($lst)
    }
    else
    {if $old
      call write_event($beg,$end,$cnt,$old)
     var ($old,$beg,$cnt,$new) = ($new,$lst,0)
    }
    var $lst = $lin->[0]
   }
   else # Add to the new list until a time stamp is found
    var $new = join('%BR%',$new,$lin)
  }

  # Treat the remaining occurrences
  if $old
   call write_event($beg,$end,$cnt,$old)
 }

 debug ' Inside IPS module, reporting alert.log analysis results'
 write '---+!! Alert.log Analysis'
 write '---## For ',$sta{'fil'}
 write $TOC

 write '---+ Processing Totals'
 write '|*Total alert.log records(lines) read*| ',nvl($sta{'lin'},0),'|'
 write '|*Start time*|',fmt_time($beg),' |'
 write '|*End time*|',fmt_time($end),' |'
 write '|*Total instance startups encountered*| ',nvl($sta{'str'},0),'|'
 write $TOP

 prefix
 {write '---+ Error Summary'
  write '|*First Occurrence*|*Last Occurrence*|*Count*|*Event*|'
 }
 call write_nodup(@err)
 if hasOutput(true)
  write $TOP
}

=head2 versions - Product Versions

Gets product versions and determines which version of the database is
installed.

=cut

debug ' Inside IPS module, gathering product versions'
report versions
write '---+!! Product Versions from V$Version'
set $sql
{SELECT '|' || v.banner || '|'
" FROM v$version v;
}
if loadSql($sql)
{prefix
  write '|*Banner*|'
 call writeLastSql()
 unprefix
}
else
 write 'Cannot connect to the database (',getSqlMessage(),')'

# Determine which version the database says we are dealing
var ($ver) = grepLastSql('Oracle','f')
if match($ver,'^\|Oracle7\s')
 var $ORACLE_VERSION = '7'
elsif match($ver,'^\|Oracle8\s')
 var $ORACLE_VERSION = '80'
elsif match($ver,'^\|Oracle8i\s')
 var $ORACLE_VERSION = '81'
elsif match($ver,'^\|Oracle9i\s')
{if match($ver,'\s9\.2\.')
  var $ORACLE_VERSION = '92'
 else
  var $ORACLE_VERSION = '90'
}
elsif match($ver,'^\|Oracle\s')
 var $ORACLE_VERSION = check($ver,'\s12\.2\.','122',\
                                  '\s12\.1\.','121',\
                                  '\s11\.2\.','112',\
                                  '\s11\.1\.','111',\
                                  '\s10\.2\.','102',\
                                  '\s10\.1\.','101',\
                                  '10')
debug '  ORACLE_VERSION is ',$ORACLE_VERSION
toc '2:[[',getFile(),'][rda_report][Product Versions]]'

=head2 alert_log - Alert.log

Displays the content of the alert log. By default, it is limited to the last
30000 lines, but the number of lines can be increased to include the last
start.

=head2 alert_sum - Alert.log Analysis

RDA analyzes the lines extracted from the alert log to detect ORA-600 [17059]
error and produce a summary.

=cut

var @tim = ()
if $BDUMP_DIR
{debug ' Inside IPS module, found dumpdir ',$BDUMP_DIR,', get log info'
 if ?testFile('r',$fil = catFile($BDUMP_DIR,get_alert_name()))
 {# Find associated trace files
  debug ' Inside IPS module, finding trace files referenced in the alert.log'

  # At a minimum, take the alert log back to the last instance startup
  debug ' Inside IPS module, determining line count for alert log display'
  report alert_log
  var @tbl = grepFile($fil,'Starting ORACLE instance','n')
  var $lst = field(':',0,$tbl[-1])
  var $max = getLength()

  if expr('<',$max,$ALERT_TAIL)
  {write '---+!! Entire ALERT.log Content'
   call writeFile($fil)
  }
  else
  {var $cnt = $ALERT_TAIL
   var $min = expr('-',$max,$cnt)
   incr $min
   if $lst
   {var $min = min($lst,$min)
    var $cnt = expr('-',$max,$min)
    incr $cnt
   }
   write '---+!! Last ',$cnt,' lines of ALERT.log'
   call writeLines($fil,$min,$max)
  }
  toc '2:[[',getFile(),'][rda_report][Alert.log]]'

  debug ' Inside IPS module, analyzing alert log report'
  var $rpt = getFile('/')
  report alert_sum
  call search_alert(\@tim,$rpt,false)
  if isCreated(true)
   toc '2:[[',getFile(),'][rda_report][Alert.log Analysis]]'
 }
}

=head2 bdump - Recent Background Trace Files

Collects the most recent trace files for each type of background process. Old
files are not considered.

=cut

if and($TRACE_DIR,$BDUMP_MAX)
{debug ' Inside IPS module, gathering most recent background trace files'
 report bdump
 title '---+!! Most Recent Background Trace Files'
 title $TOC
 title '%BR%'
 title '   * Background trace files not older than ',$LOG_AGE,' days'
 title '   * Limited to ',$BDUMP_MAX,' files of each type'
 title '   * File size lower than ',$BDUMP_VOLUME,' MiB'
 title '   * Links point to files that have been collected in their original \
             format. Opening them directly in your browser can present \
             security risks. To prevent them, access the file outside the \
             browser or use the link to save them and use an adequate viewer.'

 prefix
 {write '---+ Collected Files'
  write '|*Type*|*Trace File*| *Size*|*Last Modification*|'
 }
 var $vol = expr('*',$BDUMP_VOLUME,1048576)
 var ($cnt,@tbl,%fil,%ref) = (0)
 loop $nam (grepDir($TRACE_DIR,\
                    concat('^\Q',$ORACLE_SID,'\E_[^_\.]+_.*\.trc$'),\
                    concat('itm',$LOG_AGE)))
 {incr $ref{$typ = field('_',1,$nam)}
  next expr('>',$ref{$typ},$BDUMP_MAX)
  call push($fil{$typ},$nam)
 }
 loop $typ (keys(%ref))
 {loop $nam (@{$fil{$typ}})
  {var $fil = catFile($TRACE_DIR,$nam)
   if expr('<',get_size($fil),$vol)
   {incr $cnt
    output d,concat('bdump',$cnt,'_',$nam)
    if ${CUR.O_LAST}->write_data($fil)
    {var $col{getShortPath($fil,true)} = ${CUR.O_LAST}->get_raw(true)
     write '|',$typ,' |[[',last,'][_blank][',encode($fil),']]| ',\
           getSize($fil),'|',getLastModify($fil,'<UTC>'),' |'
     end ${CUR.O_LAST}
     next
    }
    end ${CUR.O_LAST}
   }
   call push(@tbl,$fil)
  }
 }

 if @tbl
 {write '---+ Other Trace Files'
  write 'Files not collected due to permissions problems or size constraints.'
  write '|*Trace File*| *Size*|*Last Modification*|'
  loop $fil (@tbl)
   write '|',encode($fil),'| ',getSize($fil),'|',\
         getLastModify($fil,'<UTC>'),' |'
 }

 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Recent Background Trace Files]]'
}

=head2 udump - Recent User Dumps

When requested, most recent user dumps can be collected.

=cut

if and($UDUMP_DIR,$UDUMP_AGE)
{debug ' Inside IPS module, gathering most recent user dumps'
 report udump
 title '---+!! Most Recent User Dumps'
 title $TOC
 title '%BR%'
 title '   * User dumps not older than ',$UDUMP_AGE,' days'
 title '   * User dump volume lower than ',$UDUMP_VOLUME,\
       ' MiB after compression'
 title '   * Links point to files that have been collected in their original \
             format. Opening them directly in your browser can present \
             security risks. To prevent them, access the file outside the \
             browser or use the link to save them and use an adequate viewer.'
 prefix
 {write '---+ Collected Files'
  write '|*User Dump*| *Size*|*Last Modification*|'
 }
 var $vol = expr('*',$UDUMP_VOLUME,1048576)
 var ($cnt,$tot,$flg,@tbl) = (0,0,true)
 loop $nam (grepDir($UDUMP_DIR,concat('^\Q',$ORACLE_SID,'\E_.*\.trc$'),\
                    concat('itm',$UDUMP_AGE)))
 {var $fil = catFile($UDUMP_DIR,$nam)
  if exists($col{$key = getShortPath($fil,true)})
  {write '|[[',$col{$key},'][_blank][',encode($fil),']]| ',getSize($fil),'|',\
         getLastModify($fil,'<UTC>'),' |'
   next
  }
  elsif $flg
  {incr $tot,get_size($fil)
   if expr('<',$tot,$vol)
   {incr $cnt
    output d,concat('udump',$cnt,'_',$nam)
    if ${CUR.O_LAST}->write_data($fil)
    {write '|[[',${CUR.O_LAST}->get_raw(true),'][_blank][',encode($fil),']]| ',\
           getSize($fil),'|',getLastModify($fil,'<UTC>'),' |'
     end ${CUR.O_LAST}
     next
    }
    end ${CUR.O_LAST}
   }
   else
    var $flg = false
  }
  call push(@tbl,$fil)
 }

 if @tbl
 {write '---+ Other Files'
  write 'Files not collected due to permissions problems or collected size \
         constraints.'
  write '|*User Dump*| *Size*|*Last Modification*|'
  loop $fil (@tbl)
   write '|',encode($fil),'| ',getSize($fil),'|',\
         getLastModify($fil,'<UTC>'),' |'
 }

 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Recent User Dumps]]'
}

=head2 high_version_count - High Version Count Information

Gathers high version count information.

=cut

# Check for the existence of ORA-00600 [17059] within last 24 hours
var ($day,$mon,$yer,$hur,$min,$sec) = \
  split('-|\s+|:',getLocalTime(expr('-',time(),86400)))
var $tim = mktime($sec,$min,$hur,$day,$mon,$yer)
loop $lst (@tim)
{if expr('<',difftime($tim,$lst),0)
  break $ERR = true
}

if $ERR
{debug ' Inside IPS module, gathering high version count information'
 report high_version_count
 if match($ORACLE_VERSION,'^12')
 {set $sql
  {SELECT '[[[' || CHR(10) || '| ' ||
  "       version_count || '|' ||
  "       sql_id || ' |' ||
  "       REPLACE(REPLACE(REPLACE(LTRIM(sql_text),
  "         '|', '&#124;'),
  "         '<', '&lt;'),
  "         '>', '&gt;') || ' |' ||  CHR(10) || ']]]'
  " FROM v$sqlarea
  " WHERE con_id = sys_context('USERENV', 'CON_ID')
  "   AND version_count > 3000
  " ORDER BY version_count;
  }
 }
 else
 {set $sql
  {SELECT '[[[' || CHR(10) || '| ' ||
  "       version_count || '|' ||
  "       sql_id || ' |' ||
  "       REPLACE(REPLACE(REPLACE(LTRIM(sql_text),
  "         '|', '&#124;'),
  "         '<', '&lt;'),
  "         '>', '&gt;') || ' |' ||  CHR(10) || ']]]'
  " FROM v$sqlarea
  " WHERE version_count > 3000
  " ORDER BY version_count;
  }
 }
 if loadSql($sql)
 {prefix
  {write '---+ High Version Count Information'
   write '| *Version Count*|*SQL ID*|*SQL Text*|'
  }
  call writeLastSql()
  if hasOutput(true)
   write $TOP
 }
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][High Version Count Information]]'

=head2 Child Cursors Information

Identifies the reason codes to determine the cause for high number of child
cursors leading to ORA-600 [17059] error.

=cut

 debug ' Inside IPS module, gathering child cursors information'
 pretoc '2:Child Cursors Information'
 if match($ORACLE_VERSION,'^12')
 {set $sql
  {SELECT '|UNBOUND_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SQL_TYPE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OPTIMIZER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OUTLINE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|STATS_ROW_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LITERAL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FORCE_HARD_PARSE| ' ||
  "        SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BUFFERED_DML_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INST_DRTLD_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SLAVE_QC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TYPECHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|AUTH_CHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DESCRIBE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LANGUAGE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TRANSLATION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_EQUIV_FAILURE| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INSUFF_PRIVS| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INSUFF_PRIVS_REM| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|REMOTE_TRANS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OVERLAP_TIME_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|EDITION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|NO_TRIGGER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ANYDATA_TRANSFORMATION| ' ||
  "        SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PDDL_ENV_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pddl_env_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DIFFERENT_LONG_LENGTH| ' ||
  "        SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' ||
  "        SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|DIFF_CALL_DURN| ' ||
  "        SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_UACS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|CROSSEDITION_TRIGGER_MISMATCH | ' ||
  "        SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0')))
  "       || '|' || CHR(10) || '|PQ_SLAVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROLL_INVALID_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_STALEOBJ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_DEBUG| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LOAD_OPTIMIZER_STATS| ' ||
  "        SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ACL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_ARCHIVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOCK_USER_SCHEMA_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|REMOTE_MAPPING_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOAD_RUNTIME_HEAP_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|HASH_MATCH_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PURGED_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_LENGTH_UPGRADEABLE| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0'))) || '|'
  "       || '|' || CHR(10) || '|USE_FEEDBACK_STATS| ' ||
  "        SUM(TO_NUMBER(DECODE(use_feedback_stats,'Y',1,'N','0'))) || '|'
  " FROM v$sql_shared_cursor
  " WHERE address IN (SELECT address
  "                    FROM v$sqlarea
  "                    WHERE sql_id = ':1');
  }
 }
 elsif match($ORACLE_VERSION,'^112')
 {set $sql
  {SELECT '|UNBOUND_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SQL_TYPE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OPTIMIZER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OUTLINE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|STATS_ROW_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LITERAL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FORCE_HARD_PARSE| ' ||
  "        SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BUFFERED_DML_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INST_DRTLD_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SLAVE_QC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TYPECHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|AUTH_CHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DESCRIBE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LANGUAGE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TRANSLATION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_EQUIV_FAILURE| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INSUFF_PRIVS| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INSUFF_PRIVS_REM| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|REMOTE_TRANS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OVERLAP_TIME_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|EDITION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|NO_TRIGGER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ANYDATA_TRANSFORMATION| ' ||
  "        SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMPLETE_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DIFFERENT_LONG_LENGTH| ' ||
  "        SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' ||
  "        SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|DIFF_CALL_DURN| ' ||
  "        SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_UACS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|CROSSEDITION_TRIGGER_MISMATCH | ' ||
  "        SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0')))
  "       || '|' || CHR(10) || '|PQ_SLAVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROLL_INVALID_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_STALEOBJ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_DEBUG| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LOAD_OPTIMIZER_STATS| ' ||
  "        SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ACL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_ARCHIVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOCK_USER_SCHEMA_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|REMOTE_MAPPING_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOAD_RUNTIME_HEAP_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|HASH_MATCH_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PURGED_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_LENGTH_UPGRADEABLE| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0'))) || '|'
  " FROM v$sql_shared_cursor
  " WHERE address IN (SELECT address
  "                    FROM v$sqlarea
  "                    WHERE sql_id = ':1');
  }
 }
 elsif match($ORACLE_VERSION,'^111')
 {set $sql
  {SELECT '|UNBOUND_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SQL_TYPE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OPTIMIZER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OUTLINE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|STATS_ROW_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LITERAL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FORCE_HARD_PARSE| ' ||
  "        SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BUFFERED_DML_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INST_DRTLD_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SLAVE_QC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TYPECHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|AUTH_CHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DESCRIBE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LANGUAGE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TRANSLATION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROW_LEVEL_SEC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(row_level_sec_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INSUFF_PRIVS| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INSUFF_PRIVS_REM| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|REMOTE_TRANS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OVERLAP_TIME_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|EDITION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|NO_TRIGGER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ANYDATA_TRANSFORMATION| ' ||
  "        SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMPLETE_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DIFFERENT_LONG_LENGTH| ' ||
  "        SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' ||
  "        SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|DIFF_CALL_DURN| ' ||
  "        SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_UACS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|CROSSEDITION_TRIGGER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0')))
  "       || '|' || CHR(10) || '|PQ_SLAVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROLL_INVALID_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_STALEOBJ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_DEBUG| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LOAD_OPTIMIZER_STATS| ' ||
  "        SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ACL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_ARCHIVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOCK_USER_SCHEMA_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|REMOTE_MAPPING_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOAD_RUNTIME_HEAP_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|HASH_MATCH_FAILED| ' ||
  "        SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))) || '|'
  " FROM v$sql_shared_cursor
  " WHERE address IN (SELECT address
  "                    FROM v$sqlarea
  "                    WHERE sql_id = ':1');
  }
 }
 elsif match($ORACLE_VERSION,'^102')
 {set $sql
  {SELECT '|UNBOUND_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SQL_TYPE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OPTIMIZER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OUTLINE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|STATS_ROW_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LITERAL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SEC_DEPTH_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(sec_depth_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BUFFERED_DML_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INST_DRTLD_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|SLAVE_QC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TYPECHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|AUTH_CHECK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DESCRIBE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|LANGUAGE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TRANSLATION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROW_LEVEL_SEC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(row_level_sec_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|ROW_LEVEL_SEC_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|INSUFF_PRIVS_REM| ' ||
  "        SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|REMOTE_TRANS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|OVERLAP_TIME_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|SQL_REDIRECT_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(sql_redirect_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|NO_TRIGGER_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ANYDATA_TRANSFORMATION| ' ||
  "        SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|INCOMPLETE_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|DIFFERENT_LONG_LENGTH| ' ||
  "        SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' ||
  "        SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' ||
  "        SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_UACS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' ||
  "        SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROW_SHIP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(row_ship_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|PQ_SLAVE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|ROLL_INVALID_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|PX_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|MV_STALEOBJ_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' ||
  "       CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) ||
  "       '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' ||
  "        SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|'
  " FROM v$sql_shared_cursor
  " WHERE address IN (SELECT address
  "                    FROM v$sqlarea
  "                    WHERE sql_id = ':1');
  }
 }
 var %qid = ()
 loop $lin (grepLastSql('^\|\s\d+\|'))
  incr $qid{field('\s*\|\s*',2,$lin)}
 loop $qid (keys(%qid))
 {report concat('rc_',$qid)
  prefix
  {write '---+ Non-Sharable Child Cursors Potentially Causing ORA-600 [17059]'
   write '---## Using SQL_ID: ',$qid
   write '|*Reason Code*|*Value*|'
  }
  call writeSql(bindSql($sql,$qid))
  if isCreated(true)
  {write $TOP
   toc '3:[[',getFile(),"][rda_report]['",$qid,"' Query Identifier]]"
  }
 }
 unpretoc
}

# Disable the group title in next index
if isTocCreated()
 toc '-:RDBMS'

=head1 SEE ALSO

L<DB:DBinfo|collect::DB:DBinfo>,
L<DB:DCdb|collect::DB:DCdb>

=begin credits

=over 10

=item RDA 8.00: Vickie Carbonneau, Terri Ann Nasshan.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut