#!/usr/bin/ksh
#set -xv
SBINS=$SBIN/scripts
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
# Modified : 17 Jun 2009    Added the get_hash_Value function
# ......................................................................................................
function help
{
  cat <<EOF

      Show statment :


           st  <hash_value|sql_id>
           st  <hash_value|sql_id> -f   # put the stament into file in $SBIN/tmp/<hash_value>.sql

EOF
exit
}
# ......................................................................................................
function get_hash_value
{
 if [ -n "${1%%*[a-z]*}" ];then
    # $1 is only digit
    echo "$1"
    return
 fi
 ret=`sqlplus -s "$CONNECT_STRING" <<EOF
 set head off pagesize 0 feed off verify off
 select distinct hash_Value from v\\$sql where sql_id = '$1';
EOF`
 echo "$ret"
}
# ......................................................................................................
#                                Main 
# ......................................................................................................
if [ -z "$1" ];then
   help
fi

while [ -n "$1" ]
do
   case "$1" in
    -f ) TOFILE=TRUE ;;
    -h ) help ;;
    *  ) HASH_VALUE="$1" ;;
   esac
   shift
done

. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
HASH_VALUE=`get_hash_value $HASH_VALUE`
FOUT=$SBIN/tmp/sql_$HASH_VALUE.sql

if [ "$TOFILE" = TRUE ];then
   F_USER=`sqlplus -s "$CONNECT_STRING" <<EOF
set linesize 64 pagesize 0 head off feed off
select username from v\\$sql ,dba_users where
       hash_value='$HASH_VALUE' and parsing_user_id = user_id and rownum = 1;
EOF`
#  PASS=`grep "$ORACLE_SID:$F_USER:" $SBINS/.passwd | cut -f3 -f:` 2>/dev/null
  echo "rem F_USER=$F_USER"
  SQL=`sqlplus -s "$CONNECT_STRING" <<EOF
set linesize 32767 pagesize 0 head off feed off
col sql_text format A64
break on sql_text
select '|'||a.sql_text||'|'
from v\\$sqltext a
where a.HASH_VALUE = '$HASH_VALUE'
order by piece
/
EOF`
VAR="`echo $SQL | tr -d '\n'`"

echo $VAR| sed -e 's@|@@' -e 's@\(.*\)|$@\1@' -e 's@| |@@g' | sed  -e 's/[wW][hW][eW][rW][eW]/\
WHERE \
    /g' -e    's/[sS][eE][lL][eE][cC][tT]/\
SELECT  \
    /g' -e    's/[fF][rR][oO][mM]/\
FROM   \
    /g' -e    's/ [aA][nN][dD] /\
     AND /g' -e    's/ [oO][rR][dD][eE][rR] [bB][yY] /\
ORDER BY /g'  -e    's/ [gG][rR][oO][uU][pP] [bB][yY] /\
GROUP BY /g' -e 's/[,][^,][^,]*[,][^,][^,]*[,]/\
    /g' -e    's/[sS][eE][tT]/\
SET    /g' > $FOUT
echo "File : $FOUT"

echo $VAR| sed -e 's@|@@' -e 's@\(.*\)|$@\1@' -e 's@| |@@g' | sed  -e 's/[wW][hW][eW][rW][eW]/\
WHERE \
    /g' -e    's/[sS][eE][lL][eE][cC][tT]/\
SELECT  \
    /g' -e    's/[fF][rR][oO][mM]/\
FROM   \
    /g' -e    's/ [aA][nN][dD] /\
     AND /g' -e    's/ [oO][rR][dD][eE][rR] [bB][yY] /\
ORDER BY /g'  -e    's/ [gG][rR][oO][uU][pP] [bB][yY] /\
GROUP BY /g' -e 's/[,][^,][^,]*[,][^,][^,]*[,]/\
    /g' -e    's/[sS][eE][tT]/\
SET    /g'
elif [ -n "$SQL_ID" ];then


sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 80 termout on pause off embedded on verify off heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'get sql text' nline from sys.dual
/
prompt

set heading on
set linesize 132 pagesize 0
col HASH_VALUE format 999999999
col sql_text format A64
break on sql_text

prompt
prompt .    Type      sq -hv $HASH_VALUE  to see stats on this sql
prompt
select a.sql_text from v\$sqltext a where a.sql_id = '$SQL_ID' order by piece
/
select sid from v\$session where sql_id = '$SQL_ID' or prev_sql_id = '$SQL_ID'
/
exit

EOF

else

sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 80 termout on pause off embedded on verify off heading off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'get sql text' nline from sys.dual
/
prompt

set heading on
set linesize 132 pagesize 0
col HASH_VALUE format 999999999
col sql_text format A64
break on sql_text

prompt
prompt .    Type      sq -hv $HASH_VALUE  to see stats on this sql
prompt
select a.sql_text
from v\$sqltext a
where a.hash_value = '$HASH_VALUE'
order by piece
/
select sid
from v\$session
where sql_hash_value = '$HASH_VALUE' or prev_hash_value = '$HASH_VALUE'
/
exit

EOF
fi