William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

bash_plsql.sh

#! /bin/bash

ORASID=audevsrv
USERID=sipsys
PASSWD=fgcpass06
ORACLE_HOME=/opt/oracle/product/10.2.0.2/
SQLPLUS=$ORACLE_HOME/bin/sqlplus

SIPUSER_TMP=/tmp/sip_userlist
APPLICATION_TMP=/tmp/sip_application
SIPGROUP_TMP=/tmp/sip_group
CATEGORY_TMP=/tmp/sip_category
CATEGORY_VIEW_TMP=/tmp/sip_category_view
CATEGORY_ADD_TMP=/tmp/sip_category_add
GROUP_TMP=/tmp/sipgroup
GROUP_VIEW_CATEGORY_TMP=/tmp/sip_group_cat
GROUP_VIEW_RIGHT_TMP=/tmp/sip_group_right
RIGHT_TMP=/tmp/sip_right
CATEGORY_RIGHT_TMP=/tmp/sip_category_right
GROUP_CATEGORY_TMP=/tmp/sip_user_group
ONE_USER_GROUP_TMP=/tmp/sip_1_user_group
ONE_CATEGORY_RIGHT_TMP=/tmp/sip_1_category_right
ONE_GROUP_CATEGORY_TMP=/tmp/sip_1_group_category 

#########################
# APPLICATION table(s)
#########################
get_application() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$APPLICATION_TMP 2>&1
set heading off
set feedback off
set term off
select APP_ID||' '||APP_SHORT_NM from SIPSYS.APPLICATION
/
exit
EOF
}

###################################
# SIPUSER, APP_USER tables(s)
###################################

get_userlist() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$SIPUSER_TMP 2>&1
set heading off
set feedback off
set linesize 2000
set term off
select FIRST_NM||','||LAST_NM||','||MIDDLE_NM||','||SIPUSER.LOGON_ID||','||ORG_CODE||','||USER_ENABLED_FLG||','||PASSWORD_EXPIRE_DT||','||GRACE_LOGON_NBR||','||LAST_LOGON_DT||','||INVALID_NBR||','||APP_USER.APP_ID||','||USER_GROUP.GROUP_ID 
from SIPUSER, APP_USER, APPLICATION, USER_GROUP
where SIPUSER.LOGON_ID = APP_USER.LOGON_ID
and APPLICATION.APP_ID = APP_USER.APP_ID
and USER_GROUP.LOGON_ID = APP_USER.LOGON_ID
and USER_GROUP.APP_ID = APP_USER.APP_ID
/
exit
EOF
}

# Used by 'Add User'.
get_group_category() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$GROUP_CATEGORY_TMP 2>&1
set heading off
set feedback off
set term off
select distinct s.GROUP_ID||','||s.APP_ID||','||GROUP_NM||','||GROUP_LONG_DESC
from SIPGROUP s, GROUP_CATEGORY gc
where s.GROUP_ID = gc.GROUP_ID
and   s.APP_ID = gc.APP_ID
/
exit
EOF
}

# Used by 'Update User'.
get_one_user_group() {
	if [ $# -ne 1 ]; then
		echo "Calling get_one_user_group function:"
		echo "get_one_user_group usage: `basename $0` logonId "
		exit 1
	fi
	logon_id=${1}
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$ONE_USER_GROUP_TMP 2>&1
set heading off
set feedback off
set term off
select 1||','||GROUP_ID||','||APP_ID||','||GROUP_NM||','||GROUP_LONG_DESC from sipgroup where group_id in (select group_id from user_group where logon_id = '$logon_id');
select 0||','||GROUP_ID||','||APP_ID||','||GROUP_NM||','||GROUP_LONG_DESC from sipgroup where group_id not in (select group_id from user_group where logon_id = '$logon_id');
exit
EOF
}


add_sipuser() {
	if [ $# -ne 9 ]; then
		echo "Calling add_sipuser function:"
		echo "add_sipuser usage: `basename $0` logonId appId groupId firstName middleName lastName orgCode passwordDesc isEnabled"
		exit 1
	fi

	logonId=${1}
	appId=${2}
	groupId=${3}
	firstName=${4}
	middleName=${5}
	lastName=${6}
	orgCode=${7}
	passwdDesc=${8}
	isEnabled=${9}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
SIP.addUser('$logonId', $appId, $groupId, '$firstName', '$middleName', '$lastName', '$orgCode', '$passwdDesc', '$isEnabled');
END;
/
exit;
EOF
	get_userlist
}


update_sipuser() {
	if [ $# -ne 9 ]; then
		echo "Calling update_sipuser function:"
		echo "update_sipuser usage: `basename $0` logonId appId groupId firstName middleName lastName orgCode passwordDesc isEnabled"
		exit 1
	fi

	logonId=${1}
	appId=${2}
	groupId=${3}
	firstName=${4}
	middleName=${5}
	lastName=${6}
	orgCode=${7}
	passwdDesc=${8}
	isEnabled=${9}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
	SIP.updateUser('$logonId', $appId, $groupId, '$firstName', '$middleName', '$lastName', '$orgCode', '$passwdDesc', '$isEnabled');
END;
/
exit;
EOF
	get_userlist
}


delete_sipuser() {
	if [ $# -ne 3 ]; then
		echo "Calling delete_sipuser function:"
		echo "delete_sipuser usage: `basename $0` logonId appId groupId"
		exit 1
	fi

	logonId=${1}
	appId=${2}
	groupId=${3}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
	SIP.deleteUser('$logonId', $appId, $groupId);
END;
/
exit;
EOF
	get_userlist
}


get_one_category_right() {
	if [ $# -ne 2 ]; then
		echo "Calling get_one_category_right function:"
		echo "get_one_category_right usage: `basename $0` catId appId"
		exit 1
	fi

	catId=${1}
	appId=${2}
	
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$ONE_CATEGORY_RIGHT_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select 1||','||right_id||','||right_desc||','||right_long_desc from right where right_id in (select right_id from category_right where cat_id = $catId and app_id = $appId);
select 0||','||right_id||','||right_desc||','||right_long_desc from right where right_id not in (select right_id from category_right where cat_id = $catId and app_id = $appId) and app_id = $appId;
exit
EOF
}

get_one_group_category() {
	if [ $# -ne 2 ]; then
		echo "Calling get_one_group_category function:"
		echo "get_one_group_category usage: `basename $0` groupId appId"
		exit 1
	fi

	groupId=${1}
	appId=${2}
	
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$ONE_GROUP_CATEGORY_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select 1||','||CAT_ID||','||CAT_DESC||','|| TYPE_DESC||','|| CAT_LONG_DESC||','|| TYPE_LONG_DESC from CATEGORY where cat_id in (select cat_id from group_category where group_id = $groupId and app_id = $appId);
select 0||','||CAT_ID||','||CAT_DESC||','|| TYPE_DESC||','|| CAT_LONG_DESC||','|| TYPE_LONG_DESC from CATEGORY where cat_id not in (select cat_id from group_category where group_id = $groupId and app_id = $appId);
exit
EOF
}



get_category_right() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$CATEGORY_RIGHT_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select CAT_ID||' '||RIGHT_ID||' '||APP_ID from category_right
/
exit
EOF
}


######################
#  CATEGORY table(s)
######################

# No input parameter. (need APP_ID???)
get_category() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$CATEGORY_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select CAT_DESC||','||TYPE_DESC||','||CAT_LONG_DESC||','||TYPE_LONG_DESC||','||CAT_ID||','||APP_ID
from CATEGORY
/
exit
EOF
}

# input: catId, appId
get_category_view_right() {
  if [ $# -ne 2 ]; then
    echo "Calling get_category_view_right function:"
    echo "get_category_view_right usage: `basename $0` cat_id app_id"
    exit 1
  fi
	catId=${1}
	appId=${2}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$CATEGORY_VIEW_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select distinct RIGHT_DESC||','||RIGHT_LONG_DESC
from CATEGORY_RIGHT cr, RIGHT r, CATEGORY c
where c.CAT_ID = cr.CAT_ID
and c.APP_ID = cr.APP_ID
and cr.RIGHT_ID = r.RIGHT_ID
and cr.APP_ID = r.APP_ID
and c.CAT_ID = $catId
and c.APP_ID = $appId
/
exit
EOF
}

# No input parameter.
get_category_add_right() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$CATEGORY_ADD_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select RIGHT_DESC||','||RIGHT_LONG_DESC||','||RIGHT_ID||','||APP_ID from RIGHT
/
exit
EOF
}

add_category() {
  if [ $# -ne 5 ]; then
    echo "Calling add_category function:"
    echo "add_category usage: `basename $0` category_name category_type category_long_description type_long_description right_value(s)"
    exit 1
  fi

# appId=${1}
  catDesc=${1}
  typeDesc=${2}
  catLongDesc=${3}
  typeLongDesc=${4}
  rightIds=${5}  # multi right_ids: 570|582|586|589

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
SIP.addCategory('$catDesc', '$typeDesc', '$catLongDesc', '$typeLongDesc', '$rightIds');
END;
/
exit;
EOF
	get_category
}

update_category() {
  if [ $# -ne 7 ]; then
    echo "Calling update_category function:"
    echo "update_category usage: `basename $0` catId appId catDesc typeDesc catLongDesc typeLongDesc rightValues"
    exit 1
  fi

  catId=${1}
  appId=${2}
  catDesc=${3}
  typeDesc=${4}
  catLongDesc=${5}
  typeLongDesc=${6}
  rightValues=${7}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
  SIP.updateCategory($catId, $appId, '$catDesc', '$typeDesc', '$catLongDesc', '$typeLongDesc', '$rightValues');
END;
/
exit;
EOF

  get_category
}

delete_category() {
if [ $# -ne 2 ]; then
    echo "Calling delete_category function:"
    echo "delete_category usage: `basename $0` categoryId appId"
    exit 1
  fi

  categoryId=${1}
  appId=${2}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
  SIP.deleteCategory($categoryId, $appId);
END;
/
exit;
EOF
	get_category
}


####################
#  GROUP table(s)
####################

# select APP_ID||' '||GROUP_ID||' '||GROUP_NM from SIPSYS.SIPGROUP where APP_ID=$app_id
# cat /tmp/sip_group | sed '/^$/d' to remove null lines.
get_sipgroup() {
	app_id=$1
	if [ "${app_id}" = "" ]
	then
		app_id=566
	fi
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$SIPGROUP_TMP 2>&1
set heading off
set feedback off
set term off
select distinct SIPSYS.SIPGROUP.APP_ID||' '||SIPSYS.SIPGROUP.GROUP_ID||' '||GROUP_NM
from SIPSYS.SIPGROUP, SIPSYS.GROUP_CATEGORY
where SIPSYS.SIPGROUP.APP_ID = $app_id
and SIPSYS.SIPGROUP.GROUP_ID = SIPSYS.GROUP_CATEGORY.GROUP_ID
/
exit
EOF
}


# No input parameter.
# select GROUP_NM||','||GROUP_ID||','||GROUP_LONG_DESC||','||APP_ID
get_group() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$GROUP_TMP  2>&1
set heading off
set feedback off
set linesize 2000
set term off
select GROUP_NM||','||GROUP_LONG_DESC||','||GROUP_ID||','||APP_ID from SIPGROUP
/
exit
EOF
}


# input: groupId, appId
get_group_view_category() {
	group_id=${1}
	app_id=${2}
	if [ "${app_id}" = "" ]
	then
		app_id=566
	fi
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$GROUP_VIEW_CATEGORY_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select distinct CAT_DESC||','||TYPE_DESC||','||CAT_LONG_DESC||','||TYPE_LONG_DESC
from SIPGROUP g, GROUP_CATEGORY gc, CATEGORY c
where g.GROUP_ID = gc.GROUP_ID
and g.APP_ID = gc.APP_ID
and gc.CAT_ID = c.CAT_ID
and gc.APP_ID = c.APP_ID
and g.GROUP_ID = $group_id
and g.APP_ID = $app_id
/
exit
EOF
}

get_group_add_category() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$GROUP_VIEW_CATEGORY_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select CAT_ID||','||APP_ID||','||CAT_DESC||','||TYPE_DESC||','||CAT_LONG_DESC||','||TYPE_LONG_DESC from CATEGORY
/
exit
EOF
}


# input: groupId, appId
get_group_view_rights() {
	group_id=${1}
	app_id=${2}
	if [ "${app_id}" = "" ]
	then
		app_id=566
	fi
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$GROUP_VIEW_RIGHT_TMP 2>&1
set heading off
set feedback off
set linesize 2000
select distinct RIGHT_DESC||','||RIGHT_LONG_DESC
from SIPGROUP g, RIGHT r, GROUP_CATEGORY gc, CATEGORY_RIGHT cr
where g.GROUP_ID = gc.GROUP_ID
and g.APP_ID = gc.APP_ID
and gc.CAT_ID = cr.CAT_ID
and gc.APP_ID = cr.APP_ID
and cr.RIGHT_ID = r.RIGHT_ID
and cr.APP_ID = r.APP_ID
and g.GROUP_ID = $group_id
and g.APP_ID = $app_id
/
exit
EOF
}


# only category_list, no rights_list need.
add_group() {
  if [ $# -ne 3 ]; then
    echo "Calling add_group function:"
    echo "add_group usage: `basename $0` group_name group_long_description category_list"
    exit 1
  fi

#  appId=${1}
  groupName=${1}
  groupLongDesc=${2}
  categoryList=${3}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
SIP.addGroup('$groupName', '$groupLongDesc', '$categoryList');
END;
/
exit;
EOF
	get_group
}

update_group() {
  if [ $# -ne 5 ]; then
    echo "Calling update_group function:"
    echo "update_group usage: `basename $0` groupId appId groupName groupLongDesc categoryList"
    exit 1
  fi

  groupId=${1}
  appId=${2}
  groupName=${3}
  groupLongDesc=${4}
  categoryList=${5}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
  SIP.updateGroup($groupId, $appId, '$groupName', '$groupLongDesc', '$categoryList');
END;
/
exit;
EOF
	get_group
}

delete_group() {
  if [ $# -ne 2 ]; then
    echo "Calling delete_group function:"
    echo "delete_group usage: `basename $0` groupId appId"
    exit 1
  fi

  groupId=${1}
  appId=${2}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
  SIP.deleteGroup($groupId, $appId);
END;
/
exit;
EOF
	get_group
}

#################
#  RIGHT table(s)
#################

get_right() {
$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <$RIGHT_TMP 2>&1
set heading off
set feedback off
set linesize 2000
set term off
select RIGHT_DESC||','||RIGHT_LONG_DESC||','||RIGHT_ID||','||APP_ID  from RIGHT
/
exit
EOF
}

add_right() {
  if [ $# -ne 2 ]; then
    echo "Calling add_right function:"
    echo "add_right usage: `basename $0` right_description right_long_description"
    exit 1
  fi

  # meaningful and sensitive
  rightDesc=${1}
  rightLongDesc=${2}

echo "$rightDesc>, ";
$SQLPLUS ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set heading off
set feedback off
set term off
BEGIN
SIP.addRight('$rightDesc', '$rightLongDesc');
END;
/
exit;
EOF
	get_right
}


update_right() {
  if [ $# -ne 4 ]; then
    echo "Calling update_right function:"
    echo "update_right usage: `basename $0` rightId appId rightDesc rightLongDesc"
    exit 1
  fi

  rightId=${1}
  appId=${2}
  rightDesc=${3}
  rightLongDesc=${4}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
  SIP.updateRight($rightId, $appId, '$rightDesc', '$rightLongDesc');
END;
/
exit;
EOF
	get_right
}

delete_right() {
  if [ $# -ne 2 ]; then
    echo "Calling delete_right function:"
    echo "delete_right usage: `basename $0` rightId appId"
    exit 1
  fi

  rightId=${1}
  appId=${2}

$SQLPLUS -s ${USERID}/${PASSWD}${ORASID:+@}$ORASID <<-EOF
set feedback off
set term off
BEGIN
  SIP.deleteRight($rightId, $appId);
END;
/
exit;
EOF
	get_right
}


###############################
# TEST
###############################
#add_sipuser test_fdx1 566 610 4 5 6 7 8 9
#get_application
#get_sipgroup $1
#get_userlist
#add_sipuser $1 $2 $3 $4 $5 $6 $7 $8 $9
#update_sipuser $1 $2 $3 $4 $5 $6 $7 $8 $9
#delete_sipuser $1 $2 $3
#add_sipuser test_fdx2 566 610 4 5 6 7 8 9
#add_sipuser test_fdx9 566 610 aa 0 aa aa 11 1 
#get_category
#get_group
#get_right
#get_category_right
#get_one_category_right 601 566
#get_one_group_category 608 566
#
# The usage of this script
# -----------------------------------
# 
# Please add the following codes in your cgi scripts to use /home/fgc/bin/logon/sipusermaint_SP.sh:
# 
# 1) first call the script after init scripts at the very beginning:
#   . $HOME/bin/login/sipUserMaint_SP.sh
# or:
#   . ./sipUserMaint_SP.sh
# 
# 2) in the place which needs results, call functions:
# 
# get_application
# 
# get_sipgroup $1
# 
# get_userlist
# 
# add_sipuser $1 $2 $3 $4 $5 $6 $7 $8 $9
#
# update_sipuser $1 $2 $3 $4 $5 $6 $7 $8 $9
#
# delete_sipuser $1 $2 $3
# 
# That should work.
# 
Advertisements

One response to “bash_plsql.sh

  1. powercashadvance.com 10/26/2011 at 5:19 pm

    The points you contributed here are really useful. It turned out such a pleasurable surprise to get that looking forward to me as i woke up today. They are always to the point and simple to understand. Thanks for your time for the innovative ideas you’ve shared right here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: