1

I'm trying to write a script that:

  1. Prompts you for a name
  2. Does a select count in the sqlplus database to see if it exists
  3. End user looks at query results and prompt asks user if they would like to run statement2
  4. If you say yes then a statement2 runs. If no script is killed

The issue I am having is after the first query runs to check if it exists, I'm not sure how to add a prompt asking if you want to insert into the database

Here is my code so far:

echo "Please enter last name: 'input;"
read input

statement1="select count(*) as count from users
where fname = '"$input"'"

statement2="INSERT INTO users VALUES ('"$input"');
$ORACLE_HOME********************************
    $statement1
    /
    quit;
Eossql

The part I can't figure out is after statement 1 is executed I want the system to ask if statement 2 should be executed. Any suggestions?

Thanks!

EDIT: Here is my edited code thanks to CDahn suggestion. This solution works but I was wondering if there is a cleaner way to do this instead of connecting to sqlplus twice. 

echo "Please enter last name: 'input;"
read input
Statement2="INSERT INTO users VALUES ('"$input"');"
output1=$($sqlplus -s User/Pass@connection <<EOF
set head off
select count (*) from users
where fname = '$input';
EXIT;
EOF
)

read -p "User $input appears $output1 times. Create user? [Y/n]" answer
if [ -z "$answer" -o "$answer" == "y" -o "$answer" == "Y" ]
then

$sqlplus -s User/Pass@connection << Eossql
set autocommit on
set head off
$Statement2
    quit;
Eossql

else
    echo "Not creating user"
fi
connollyc4
  • 155
  • 1
  • 3
  • 15

1 Answers1

2

You need to add calls to sqlplus to get the output of the queries you've written. See this SO question for assistance with that.

Once you have the output, you can use a conditional, as Raman Sailopal suggested. Something like:

output1="`sqlplus $statement1`"

read -p "User $input appears $output1 times. Create user? [Y/n]" answer
if [ -z "$answer" -o "$answer" == "y" -o "$answer" == "Y" ]
then
    sqlplus $statement2
else
    echo "Not creating user."
fi
CDahn
  • 1,795
  • 12
  • 23
  • That's not really what he's asking. He can't figure out how to make a Shell Script ask for confirmation before continuing to execute statements. – Ulises André Fierro Jul 19 '17 at 21:39
  • 1
    @UlisesAndréFierro, he's got way more problems than that. He's not even querying the database to make a decision in the first place. I figured I'd at least try to get him in the area rather than flag it as either duplicate or unsalvageable. But I agree, he's _asking_ for help with conditionals and prompting the user, but he _needs_ help with everything else too. – CDahn Jul 19 '17 at 21:42
  • I assumed he was showing the portion of the code he was having issues with and that he somehow was executing the statements, but you're right, if that's the whole script then he's definitely asking the wrong question. – Ulises André Fierro Jul 19 '17 at 21:46
  • @CDahn I'm trying to make the script ask for confirmation before executing statement2. Sorry for the confusing post. – connollyc4 Jul 19 '17 at 22:15
  • @connollyc4, yep, but you can't ask for confirmation until you have the result of the first statement. Your script is currently not talking to sqlplus. If you forgot to include that in your sample code, it would be helpful if you did that. If that's literally your whole script, you need to add calls to sqlplus to get the count from the database first. – CDahn Jul 20 '17 at 02:23
  • @CDahn I really only want statement1 to run and a prompt to ask if statement2 should run regardless of the output of statement1 the end user can see the result of statement1 to make the decision to run statement2 – connollyc4 Jul 20 '17 at 02:45
  • @connollyc4, okay, then I agree that this is a duplicate question. See [question noted above](https://stackoverflow.com/questions/226703/how-do-i-prompt-for-yes-no-cancel-input-in-a-linux-shell-script) for how to prompt the user for additional input. It's just odd, since you already have code in your script to ask the user for input, so I thought you really meant something else. – CDahn Jul 20 '17 at 02:48
  • @CDahn the issue I am running into is prompting for a question after the script establishes a connection with Sqlplus and after the end user can see the result of statement1. If the SQL aspect was not part of it then it would be easy for me ask the end user multiple questions via prompt – connollyc4 Jul 20 '17 at 03:23
  • @connollyc4, I thought I showed you how to do that in my answer, above. But, since you're still having difficulties, I'm afraid we just don't fully understand what you're asking. You might try editing your question to be more in line with [mcve], or possibly asking a different question. Good luck! – CDahn Jul 20 '17 at 12:26
  • @CDahn Thanks for your suggestion. For some reason I thought It did something differently initially. I updated the code with your suggestion. Do you know if there is a cleaner way to do this then what I did? Thanks again for all your help! – connollyc4 Jul 22 '17 at 00:34
  • @connollyc4, not as far as I know in a shell script. You're stuck with connecting twice. – CDahn Jul 22 '17 at 02:11