Re: PL/SQL termination status to Unix

From: Paul Zola <pzola_at_us.oracle.com>
Date: 1995/10/04
Message-ID: <44t1hv$lum_at_inet-nntp-gw-1.us.oracle.com>#1/1

} In article <812560391.10539_at_stercon.demon.co.uk>,
}    steve_at_stercon.demon.co.uk (steve roach) wrote:

} >How would I report a PL/SQL script termination status to a calling
} >Unix script? The Unix bit would look something like:
} >
} > sqlplus username/password ...
} > echo $?
} >
} >The PL/SQL, say, updates a table and needs to return a status code to
} >Unix depending if the target row existed or not. I solved this one
} >some years ago but with an older version of PL/SQL and within a VAX
} >DCL script. I remember that it worked very well.
}

The following should work for you. Season to taste.

########################### cut here ###############################

#!/bin/sh

sqlplus -s scott/tiger << EOF+

    variable estatus number;
    begin

        :estatus := 3;
    end;
/

    column old_estatus new_value exit_status noprint     select :estatus old_estatus from dual;

    exit exit_status
EOF+ echo "exit status is $?"

########################### cut here ###############################

In case it's not obvious, you could put the assignment to the 'estatus' variable inside of whatever conditional code you'd like, including an exceptions clause ....

        -paul

---

Paul Zola                                             Senior Technical Analyst
World-Wide Technical Support                                 Tools Escalations
==============================================================================

Computers possess the truly profound stupidity of the inanimate. - B. Sterling Received on Wed Oct 04 1995 - 00:00:00 CET