Problem solve Get help with specific problems with your technologies, process and projects.

Notify Oracle application users

Here is a script that spools the Mail ID's of Oracle Application users and mails them whatever you want to be notified.

Oracle Application users are different from normal database users that we typically set-up as Oracle DBAs. These users use the different modules of the E-Business suite through packages and thus enforcing security is different. The users are not available at the database level; the database only stores their information and authenticates them at the Application level. Once authenticated, the users connect to different modules through packages and the packages connect to the database objects through the module-specific users. These users are "super-users" for that module and exist at the database level. They own the actual table and indexes related to their module and all the other objects--such as packages, functions and procedures--that manipulate their data are owned by Application super-user apps.

These users would also be the actual end-users of the application. Sometimes it becomes very difficult to keep track of which users in which department actually use the Application. When you want to notify them of an outage or simple maintenance work like bringing the Concurrent Managers down, it becomes very difficult to identify and inform them of the impending maintenance.

Here is a script that spools the Mail ID's of the Application users and mails them whatever you want to notified them. This only requires that when a user is created, the Mail ID information is fed in and exists in the AOL tables of the database. The script was tested on 10.7 NCA and Rel 11. Be sure to test it at your setup before using it!

# Filename:    mailer 
# Author  :     Shankar Govindan. 
# Created :     12/28/2001 
# Description : This script is used for sending out a mail to Oracle APPS
# Users, It picks up the email_address of FND_USER, spools them and sends 
# out a mail. The mail message text can be placed in a message.lst file 
# and input the same. I have used a password file with system/manager. 
#    1st Argument: mail 
#    2nd Argument: MESSAGE (optional like SHUTDOWN) 

if ( [ "$1" != "mail" ] ) 
        echo "Usage: $0 mail message" 
        echo " Eg.: $0 mail SHUTDOWN (one word)" 

GROUP=`groups | cut -d" " -f1` 

function _mailercmd 
  echo "=======================================================================" 
  echo "Sending mail for Database ${ORACLE_SID} and the mail message is ${MESSAGE}                     " 
  echo "=======================================================================" 
 SYSTEM_PWD=`grep -i "^system/" $PWD_FILE` 
 case $CMD in 
            if [ "$SYSTEM_PWD" = "" ] ; then 
                echo "nERROR: userid=system does not exist in ${PWD_FILE}" 
                return 1 
            echo "** Sending mail for Oracle database=${ORACLE_SID}" 
            (echo ${SYSTEM_PWD}; echo set pages 0 pause off verify off feedback
                  off termout off; echo "select email_address from 
                  apps.fnd_user where upper(email_address) not in
                  ('N/A','NONE');") | sqlplus -s > $MAILDIR/mailers

            grep -i "" $MAILDIR/mailers >/dev/null 
            if [ $? = 0 ] 
              mailx -s "$MESSAGE" $(cat $MAILDIR/mailers) < message_file.lst 
              echo "For Database=$ORACLE_SID , mailing $MESSAGE" 
             return 1 
              echo "Database=$ORACLE_SID is DOWN" 
            return 0 
*) echo "nERROR: Invalid option=$CMD (in function _mailercmd)" 
            return 2 
if [ "$CMD" = "mail" ]; 
            _mailercmd $CMD $MESSAGE 
     echo " Database is down " 
return 1        

Reader Feedback

Manjushree D. writes: The script is somewhat confusing...maybe because it is not documented properly. My questions are:

  1. The first argument HAS TO BE 'mail'. Then why it is not included in the sctipt itself. User need not type it. Morever, it is checked more than once in the script, which appears to be unnecessary.
  2. I couldn't understand the line: GROUP=`groups | cut -d" " -f1`. This returns the user's primary group. It may or may not be dba. How is this PRIMARY group associated with Oracle functioning or privileges?
  3. The line: SYSTEM_PWD=`grep -i "^system/" $PWD_FILE` tells you that the Oracle system password is stored in a text file. This is a very insecure way of keeping passwords.
  4. I couldn't understand the purpose of: grep -i "" $MAILDIR/mailers >/dev/null.

The author responds:

  1. True, that is not necessary. If the script is part of a larger script and mail is one of them -- like say status check of processes and then call this function to mail to Apps users -- then it can be used.
  2. Well, the database passwords are owned by the Oracle group. Any user trying to login to the database and access Apps objects with System or SYS privilege should belong to the Oracle group. In 11i you can have different groups for different instances on the same server.
  3. Show me a better way and I will incorporate it; that's the way large shops keep passwords so shell scripts can access them.
  4. That's an example to check the output file for the MAILLIST mailer domain. The MAILLIST needs to be modified in the script with your MAIL ID, otherwise the mail would come to my account!

About the Author

Shankar Govindan is an Oracle Certified 7, 8 and 8i DBA working in Portland, Oregon.

For More Information

  • What do you think about this tip? E-mail the Editor at with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle applications implementation and upgrades

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.