#!/bin/bash
#
# @script pkgmd5 -  Oracle dictionary checksum for PL/SQL objects
# @author juhanak.cz
# @date 18.11.2010
# @version 1.1
#
#############################################################################

MD5SUM=/usr/bin/md5sum
TMP_OUT=/tmp/my-plsql-pkg.list
VERSION="v.1.1"
PARAM1="$1"
PARAM2="$2"

print_syntax() {
    echo ""
    echo "pkmd5 $VERSION | juhanak.cz - Oracle dictionary checksum for PL/SQL objects"
    echo ""
    echo "Usage: pkgmd5 [-s] PKG_NAME"
    echo "    or pkgmd5 all"
    echo "       -s ... ......... with source code (optional)"
    echo "       PKG_NAME........ PL/SQL object name in DB dictionary"
    echo "       all............. all objects in DB dictionary"
}

construct_pkglist() {
sqlplus -S "/ as sysdba" << EOF
set echo off
set pages 0 
set long 10000
set head off
set feedback off
spool $TMP_OUT
select owner||'.'||name from dba_source
group by owner,name 
order by owner,name 
/
spool off
EOF
}

get_pkgsource() {
sqlplus -S "/ as sysdba" << EOF
set echo off
set pages 0
set long 10000
set head off
set feedback off
select text from dba_source where UPPER(owner||'.'||name)=UPPER('$1')
order by owner,name,line
/
EOF
}

db_info() {
sqlplus -S "/ as sysdba" << EOF
set echo off
set pages 0
set long 10000
set head off
set feedback off
select 'SID:'||instance_name || ' VERSION:'||version from v\$instance;
select parameter||':'||value from nls_database_parameters where parameter='NLS_CHARACTERSET'
/
EOF
}

##### start 

if [ -z "$PARAM1" ]
  then
    print_syntax
    echo ""
    echo "Error: missing argument"
    exit 1
fi

if [ "$PARAM1" = "all" ]
then
	echo "pkgmd5 $VERSION | juhanak.cz "
	echo "****************************************************************"
	echo "\$ORACLE_HOME: $ORACLE_HOME"
	echo "\$ORACLE_SID: $ORACLE_SID"
    db_info
	echo "****************************************************************"
    echo "starts `date`"
	echo "constructing plsql-pkg list .."
	construct_pkglist > /dev/null
	echo "computing md5 hashes ..."

	for pkg_name in $(cat $TMP_OUT); do
		HASH=`get_pkgsource "$pkg_name" | $MD5SUM`
		echo "$HASH  $pkg_name"
  	done
	
	# delete tmp file
	rm $TMP_OUT
	echo "finish `date`"
else
    # with source code show
    if [ "$PARAM1" = "-s" ]
    then
	    get_pkgsource "$PARAM2"
    fi
	get_pkgsource "$PARAM2" | $MD5SUM
fi