#!/usr/bin/ksh
# set -xv
# B. Polarski
# 02 May 2006
# modified : 20-Jul-2007 Added support for MV
#                        Added support for trigger
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
function help
{
cat <<EOF

   Output the source of an object.

      src <object> -u <OWNER> -f -l
      src -syn <SYNONYM>  -u <OWNER>  # show what is behind a synonym 



     -f : the object is a fixed view.
          Don't forget to add a \ before the $.
          ie ) src -f GV\\\$SGASTAT

     -l : If the object is a package, list only the procedure. Use dsk <package> if you want to see the parameters also

   Where object can be any of the type PKG, FUNCTION, PROCEDURE, VIEW, TRIGGER

EOF
exit
}
if [ -z "$1" ];then
   help
fi
typeset -u fowner
typeset -u OBJ_NAME
typeset -u SYN_NAME

while [ -n "$1" ]
do
   case "$1" in
      -h ) help 
           exit ;;
     -syn) ACTION=SYN ; SYN_NAME=$2; shift;;
      -u ) fowner=$2 ; 
           WHERE_OWNER=" where owner='$fowner' " ; 
           AND_OWNER=" and owner='$fowner' " ; 
           AND_OWNER2=" and owner=''$fowner'' " ; 
           shift ;;
     -f  ) ACTION=FIXED_VIEW ;;
     -l  ) ACTION=LIST_FUNCT ;;
       * ) OBJ_NAME=$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
# --------------------------------------------------------------------------
if [ "$ACTION" = "LIST_FUNCT" ];then

   sqlplus -s "$CONNECT_STRING"<<EOF

set pagesize 333 linesize 124
select PROCEDURENAME,PROCEDURE#,OVERLOAD# from procedureinfo$ a, obj\$ b where upper(b.name) = '$OBJ_NAME' and a.OBJ# = b.obj# 
   order by PROCEDURE#;
EOF
exit
elif [ "$ACTION" = "FIXED_VIEW" ];then

   sqlplus -s "$CONNECT_STRING"<<EOF
set pagesize 333 linesize 124
select view_definition from v\$fixed_view_definition where VIEW_NAME = '$OBJ_NAME';
EOF
exit
elif [ "$ACTION" = "SYN" ];then

   sqlplus -s "$CONNECT_STRING"<<EOF
col owner format a18
col table_owner format a18
col table_name format a22
col db_link format a30
set linesize 124 pagesize 333
select s.owner, SYNONYM_NAME,
      table_owner , table_name, o.object_type
          from dba_synonyms s, dba_objects o
            where synonym_name = '$SYN_NAME' and 
                  s.table_name  = o.object_name and
                  s.table_owner = o.owner $AND_OWNER 
/
EOF
exit
fi

# main  of src
VAR=`sqlplus -s "$CONNECT_STRING" <<EOF
set pause off pagesize 0 head off feed off verify off
Column SqlStmnt New_Value SqlStatement noprint
select decode(count(*),1
    , 'object_type from dba_objects where object_name=''$OBJ_NAME'' and object_type in (''VIEW'',''PACKAGE'',''FUNCTION'',''PROCEDURE'',''TRIGGER'',''MATERIALIZED VIEW'') $AND_OWNER2'
    , 'owner,object_type from dba_objects where  object_name = ''$OBJ_NAME'' and object_type <> ''TABLE PARTITION'' $AND_OWNER2') SqlStmnt
      from 
        dba_objects 
      where 
        object_name = '$OBJ_NAME' and object_type in ('VIEW','PACKAGE','FUNCTION','PROCEDURE','TRIGGER','MATERIALIZED VIEW') 
        and object_type <> 'TABLE PARTITION' $AND_OWNER ;
select &SqlStatement ;
EOF`
#echo "|$VAR|"
var=`echo "$VAR"|sed 's/MATERIALIZED VIEW/MV/g'| sed '/^$/d'| awk '{print $0}'`
#echo "|$var|"
NBR=`echo "$var" |wc -l`
if [ $NBR -gt 1 ];then
   echo "\n Use src -u <ONWER> $OBJ_NAME  option"
   echo " There are more than one copy of object $OBJ_NAME : "
   echo "$VAR\n\n"
   exit
elif [ $NBR -eq 0 ];then
   echo "I did not found any object of name $OBJ_NAME in DB\n"
   exit
fi

OBJ_TYPE=`echo "$var"| sed 's/MATERIALIZED VIEW/MV/g' |sed '/^$/d'|awk '{print $1}'| tr -d '\n'`
#cho "O=$OBJ_TYPE"
echo
echo
# ........................................
# Views
# ........................................
#set -x
if [ "$OBJ_TYPE" = "VIEW" ];then

sqlplus -s "$CONNECT_STRING" <<EOF

set pagesize 0 linesize 125 termout on pause off embedded on verify off heading off
set long 32000

select distinct '       --> ' ||owner ||  '.' || VIEW_NAME ||  ' : VIEW' FROM   dba_views WHERE  view_name  = '$OBJ_NAME'  $AND_OWNER
/
prompt
SELECT text FROM   dba_views WHERE  view_name  = '$OBJ_NAME' $AND_OWNER
/

EOF
# ........................................
# package, function, procedure
# ........................................
elif [ "$OBJ_TYPE" = "PACKAGE" -o "$OBJ_TYPE" = "FUNCTION" -o  "$OBJ_TYPE" = "PROCEDURE" ];then
sqlplus -s "$CONNECT_STRING" <<EOF

set pagesize 0 linesize 125 termout on pause off embedded on verify off heading off

set head off

select distinct '       --> '||owner||'.'|| name ||  ' : ' || TYPE FROM   dba_source
WHERE  name  = UPPER('$OBJ_NAME') $AND_OWNER
/
prompt
SELECT DECODE(ROWNUM,1,'CREATE OR REPLACE '||text,text)
FROM   dba_source
WHERE  name  = UPPER('$OBJ_NAME') $AND_OWNER
ORDER BY owner, type, line
/

EOF
elif [ "$OBJ_TYPE" = "MV" ];then

sqlplus -s "$CONNECT_STRING" <<EOF

set pagesize 0 linesize 125 termout on pause off embedded on verify off heading off head off
set long 4000
col query format a88
col mname format a35

select distinct '  MATERIALIZED VIEW     --> '||owner||'.'|| mview_name ||  ' : '  FROM  dba_mviews where mview_name = '$OBJ_NAME' $AND_OWNER
/
select query from dba_mviews where mview_name = '$OBJ_NAME' $AND_OWNER
/
EOF
elif [ "$OBJ_TYPE" = "TRIGGER" ];then

sqlplus -s "$CONNECT_STRING" <<EOF

set pagesize 0 linesize 125 termout on pause off embedded on verify off heading off head off
set long 4000
col query format a88
col mname format a35

select distinct '  TRIGGER --> '||owner||'.'|| trigger_name ||  ' : '  FROM  dba_triggers where trigger_name = '$OBJ_NAME' $AND_OWNER
/
select 'Create or replace  trigger '||
DESCRIPTION   ,
TRIGGER_BODY
 from dba_triggers where TRIGGER_NAME = '$OBJ_NAME' $AND_OWNER
/
EOF
fi

echo