sqlplus -s error capture

Shell Programming and Scripting
1

Hi folks a wee problem that requires some help.

I am writing a script that allows me to automate the changing of a password in sqlplus

the problem i encounter is that the code contains a case statement that allows the helpdesk to select whether to allow or deny access by switching the password, this works fine unless you select the password already held in the sql password field

the environment is such that I have to press the enter key three times (because of error messages)

this then throws me out of oracle db and back to my menu

is there anyway I can overcome this so that if there is an error I can return straight to the menu

this is my code
sqlplus -s the_username/${the current password} @set_password ${the new password}

2 
UIPW="username/password"

PrntCode=$(echo "$UIPW
           set pages 0
           SELECT job_num
           FROM   jobs
           WHERE  jobs_name = '&1'; " |
           $ORACLE_HOME/bin/sqlplus -s  $new_value) 
echo "$1"

If you embed your sql code, stdin becomes the script rather than the terminal like it does with your call.

This will just bomb and not require user intervention - $? will have the return code. Know that the return code is not just one or zero. Non-zero is failure.