Store return code of shell script in oracle table


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Store return code of shell script in oracle table
# 1  
Old 05-04-2005
Power Store return code of shell script in oracle table

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  
Old 05-04-2005
Power shell script return code

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  
Old 05-04-2005
You might be better off at the Oracle-related forum.
# 4  
Old 05-04-2005
Quote:
Originally Posted by sveera
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 ?
Veera
Answer: no, sqlldr is not necessary and more clumsy than the following method:

Script 1:
Code:
#! /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:
Code:
#! /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

Last edited by tmarikle; 05-04-2005 at 02:38 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help Needed: UNIX shell variables to store Oracle table records

Hello Folks, I'm working on a requirement to automate the process of generating report(csv file) using metadata info stored in an Oracle table and E-mail it to respective people. Meta data table: Report_ID,Report_SUB_ID,Report_DB,Report_SQL,Report_to_email_Id 1,1,DEV,'select * From... (2 Replies)
Discussion started by: venkat_reddy
2 Replies

2. Shell Programming and Scripting

Shell script to export data from Oracle table .

Hi, I want to write a shell script which will export data from oracle table . I don't want to save that data . I want the queries . Right now i am right clicking on the table and clicking on export as to my desktop . Please let me know if any one have any idea . (2 Replies)
Discussion started by: honey26
2 Replies

3. Shell Programming and Scripting

Shell script to query Oracle table

Hi, unix gurnis I need help for following requirement for writing a shell scritp. log in to oracle database, query one table total records (select count(*) from table1), pass the return value to a file. Thanks in advance (2 Replies)
Discussion started by: ken002
2 Replies

4. Shell Programming and Scripting

how to store the return values of stored procedure in unix shell script.

hi i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh). the called stored procedure returns some values through OUT variables i want to assign the return values of stored procedure in to unix shell script variable. can you provide me the code. ... (1 Reply)
Discussion started by: barani75
1 Replies

5. HP-UX

return code from oracle to unix script

Hi I'm writing a shell script that connects to oracle database and fires query to check the availability of data in a table. In case of no data found then what will be the return code and how to handle in that in variable. Kindly provide with an example for better understanding... Thanks... (1 Reply)
Discussion started by: ksailesh
1 Replies

6. Shell Programming and Scripting

run shell script from oracle store procedure

hi, this is urgent..can i run a shell script from store procedure without using java. (8 Replies)
Discussion started by: arnabb4u
8 Replies

7. Shell Programming and Scripting

Capture Oracle return code in shell script

I am using the following code in my shell script list=`sqlplus -s $user/$pwd@$dbms<<EOF WHENEVER SQLERROR EXIT SQL.SQLCODE set pagesize 0 feedback off verify off heading off echo off select * from control_tbl where src_nm=$3 and extrct_nm=$4; exit SQL.SQLCODE; EOF` ERROR=$?... (1 Reply)
Discussion started by: Vikas Sood
1 Replies

8. Shell Programming and Scripting

how to return an array of elements from oracle to shell script

Hi all, I have a interresting problem. My application is as follows: From a shell script i will conn to a oracle database and fetch few rows using a select statement. Then i want to sort these rows and return a column (collection of values of a column from the selected results) as array... (3 Replies)
Discussion started by: satyakiran
3 Replies

9. Shell Programming and Scripting

updating a column in oracle table using shell script

Hi friends, i am having a variable declared in .profile.i am changing its value in a shell script and then i am connecting to oracle and then from there i am calling a .sql called update.sql STATUS is the variable declared in the .profile =============================== if sqlplus <<END... (3 Replies)
Discussion started by: sveera
3 Replies

10. Shell Programming and Scripting

update a oracle table using shell script

Hi, I would like to know how to update a table in Oracle database, if a command in one shell script either successfully completes or it fails.(like Y if its success or N if its a failure) While the command is running,I am able to view the log file created in the Unix machine.After the command... (2 Replies)
Discussion started by: ann_124
2 Replies
Login or Register to Ask a Question