#!/usr/bin/ksh
#whatpackagetext ()
#------------------------------------------------------------------------------#
#    Module Name:  whatpackagetext                                             #
#                                                                              #
#    Purpose:      Display (in a Backup/Restore format) the Package            #
#                     Specification and Package Body.                          #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo 
            echo  " #--------------------------------------------------------#"
            echo  " # whatpackagetext:              Oracle Apps Package Text #"
            echo  " #--------------------------------------------------------#"
            echo                                                               
            read USRNAM?" Please Enter the Schema Name (APPS, etc.): "

            #------------------------------------------------------------------#
            #  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection      #
            #------------------------------------------------------------------#
            trap        "stty echo; return"     2                              
            trap        "stty echo; return"     3
            stty -echo

            read PASSWD?"        Enter the Oracle Schema Passwd:     "

            stty echo
            echo 
            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3

            read PKGNAM?"        Enter the Package-Name:             "

            clear
        else
            PASSWD=$1
            PKGNAM=$2
            clear
        fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatpackagetext_$$.txt

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  ${USRNAM}/${PASSWD}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     pagesize        9000
        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        SPOOL ${SPOOLNM}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps Package Text'           -
                LEFT    DATEVAR                                     skip 1 -
                LEFT    TIMEVAR                                     skip 1

        set     pagesize  0
        set     heading   OFF
        set     trimspool ON
        set     linesize  255

        SPOOL /tmp/${PKGNAM}.pks

        select text
        from   user_source
        where  name = '${PKGNAM}'
          and  type = 'PACKAGE'
        order  by line;
        SPOOL OFF


        SPOOL /tmp/${PKGNAM}.pkb

        select text
        from   user_source
        where  name = '${PKGNAM}'
          and  type = 'PACKAGE BODY'
        order  by line;


EXIT;
END_FILE

print
print  "Please see  /tmp/${PKGNAM}.pks"
print  "            /tmp/${PKGNAM}.pkb"
print
}
