Re: The sql in Bourne shell takes long time
From: lovecreatesbea..._at_gmail.com <lovecreatesbeauty_at_gmail.com>
Date: 17 Nov 2006 05:38:54 -0800
Message-ID: <1163770734.235238.15060_at_f16g2000cwb.googlegroups.com>
} Received on Fri Nov 17 2006 - 14:38:54 CET
Date: 17 Nov 2006 05:38:54 -0800
Message-ID: <1163770734.235238.15060_at_f16g2000cwb.googlegroups.com>
frank.van.bortel_at_gmail.com wrote:
> What makes you believe it did not?
I didn't play a long time with databases, The SQL statement sequence of UPDATE, COMMIT, SELECT was specified by the designer. Thank you for the reminding, I will read the database manual for further information.
The following function still uses the same SQL statement sequence, it does work. (I solve my later problem and use the plsql script in Korn shell). Does this function still has that bug?
Thank you.
f_monMons_Db()
{
if [ $# -ne 1 ]; then echo ""$0"[${LINENO}]: usage: $0 <log_message_string>" exit 1 fi log_msg="$1" # Retrive threshold values and size of database tablespace occupied # in database, store them in temp config file. f_conf_read user="$f_conf_read_user" pass=`getdbpwd "$user"` sid="$f_conf_read_sid" host_name=`hostname` UMASK=`umask` umask 277 #OUTPUT=`sqlplus -s "$user"/"$pass"_at_"$sid" <<-EOF > /dev/null 2>&1 OUTPUT=`sqlplus -s "$user"/"$pass"_at_"$sid" <<-EOF DECLARE i VARCHAR2(20); len CONSTANT NUMBER(20) := 20 - 8; val CONSTANT VARCHAR2(8) := '11100064'; tm DATE; stm VARCHAR2(20); BEGIN UPDATE conf_sequence t SET t.seq_val = (SELECT decode(a.seq_init_flag, 0, a.seq_val + 1, decode(trunc(sysdate) - a.seq_date, 0, a.seq_val + 1, 1)) FROM conf_sequence a WHERE a.seq_name = 'ALARM_ID'), t.seq_date = trunc(sysdate) WHERE t.seq_name = 'ALARM_ID'; SELECT seq_val INTO i FROM conf_sequence WHERE seq_name = 'ALARM_ID'; COMMIT; tm := sysdate; stm := to_char(sysdate, 'YYYYMMDD'); i := lpad(i, len - length(i), '0'); stm := concat(stm, i); INSERT INTO alarm_info (ALARM_ID, ALARM_TYPE, ALARM_TM, INSERT_TM, ALARM_LEVEL, APPID, HOST_NAME, MODULE, ALARM_KEY, INSTANCE, ALARM_CONTENT, ALARM_STATUS) VALUES (stm, val, tm, tm, 'SERIOUS', 'monMons.sh', '$host_name', 'monMons.sh', 'alarm_key', 'monMons.sh', '$log_msg', 0); COMMIT; END; / EOF ` # error / successful message in executing plsql procedure can be read in # the OUTPUT variable, uncomment it in debug. but make sure the output # can be directed to stdout or stderr. echo $OUTPUT umask "$UMASK"
} Received on Fri Nov 17 2006 - 14:38:54 CET