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>


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

Original text of this message