#!/usr/bin/ksh
#---------------------------------------------------------------------------------
# program: smenu_get_str_in_sqlarea.ksh
# Author : 
# Date   : 08-Febuary-2006
#---------------------------------------------------------------------------------
SBINS=$SBIN/scripts
if [ -z "$1" ];then
   echo "I need a sub part of an sql"
fi
STR="$1"

HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`

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

sqlplus -s "$CONNECT_STRING" <<EOF

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


select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'List queries which have the substring : $STR ' nline
from sys.dual
/

set linesize 124  heading on
select hash_value,sid, username, substr(sql_text,1,60)
      from v\$sql a,v\$session b
      where sql_hash_value (+) = hash_value  and sql_text like '%${STR}%'
union all
select hash_value,sid, username, substr(sql_text,1,60)
       from v\$sql a,v\$session b
       where prev_hash_value (+) = hash_value and sql_text like '%${STR}%'
/
exit
EOF