Store return code of shell script in oracle table

Shell Programming and Scripting
1

Hi friends,
I have to do the following things :
1) there should be a shell script returning the returning the return code of the script. and i have to add some more details like on which machine is has run , at what time and other details and then using plsql i have to add a row to Oracle table.

2) then i have to write another script which will monitor the table for the machine_name and gets the job status of the earlier shell script .and depending on the success(0) or failure(1) (which are the status codes) a mail has to be sent to the respective groups.

Do i have to use SQLLOADER or is there any other way ?
how to i input the return code of the shell script into the table ?
Please help
thanks in advance
Veera

2

Hi friends,
I am able to insert the return code of some unix job into the database with storing the return code into some variable and then using that value in the insert statement.
But now i have to write a trigger which will be fired when ever there is an update or insert of the table.
Then based on the value in the status_code( return code of the script) , the trigger has to send a mail to soem group.

Please help
thanks in advance
Veera

3

You might be better off at the Oracle-related forum.

4

Answer: no, sqlldr is not necessary and more clumsy than the following method:

Script 1:

#! /bin/ksh
...
myscript # Call your script
RC=$? # Get your script's return status
HOST=$(uname -n)
# Call SQL*Plus
sqlplus username/password <<EOF
BEGIN
    INSERT INTO status_table VALUES('${HOST}', '${RC}');
    COMMIT;
END;
/
EOF

or
sqlplus username/password <<EOF
BEGIN
    your_plsql_sp('${HOST}', '${RC}');
END;
/
EOF

Scrpt 2 that monitors the status table:

#! /bin/ksh
...
# Loop forever
while :
do
    {
        sqlplus -S username/password <<EOF
set heading off feedback off serveroutput on
DECLARE
    STATUS_CODE VARCHAR2(50) := 'none';
BEGIN
    -- Attempt to find the status for the specified host
    SELECT 'StatusCode:' || 0
    INTO    STATUS_CODE
    FROM   status_table
    WHERE  hostname = '${HOST}';

    -- Found it, print the status and drop out of PL/SQL block
    DBMS_OUTPUT.PUT_LINE (STATUS_CODE);

EXCEPTION
    -- No status yet, drop out with a -1 code
    WHEN NO_DATA_FOUND THEN
        SELECT 'StatusCode:-1'
        INTO   STATUS_CODE
        FROM   dual;

        DBMS_OUTPUT.PUT_LINE (STATUS_CODE);
END;
/
EOF
    } | while read LINE # Find out what SQL*Plus says
    do
        # Look for the status message
        if print $LINE | /usr/xpg4/bin/grep -iq StatusCode
        then
            # Parse out the actual status code and break from inner loop
            STATUS_CODE=$(print $LINE | awk -F: '{print $2}')
            break
        fi
    done

    # If status code is not -1, break out of endless loop
    if [ ${STATUS_CODE:=-1} -ge 0 ]
    then
        break
    fi
    # Otherwise, continue monitoring the status table
    print "Still waiting for status..."
    sleep 5
done

if [ ${STATUS_CODE} -eq 0 ]
then
    maix -s Success...
else
    maix -s Sorry, it failed...
fi

This is one way of accomplishing your task.

If you really want to use a trigger then you are going to have to utilize UTL_SMTP.

Thomas