How to do sqlplus in bash if else?

Question

I am trying to write a bash function which should delete some entries from table1 if the user enters 1, otherwise remove different entries. My function looks like this:

function reset_db
{
        if [ $usr_input == 1 ]  
        then
        sqlplus -s $USR/$pwd@$SID << EOF

        delete from table1 where component = 'ABC';
        delete from table2 where component = 'ABC';
        exit
EOF
        else if [ $usr_input == 2 ]

        delete from table1 where component = 'XYZ';
        delete from table2 where component = 'XYZ';
        exit
EOF
fi
}

I am getting error: syntax error near unexpected token `fi'

I am sure that it is happening because I am using if-else incorrectly somewhere but not able to figure out a way to fix it.

Also please let me know how can I post code under the same thread if I have any more follow up questions.

Please don't use "Thanks" in your questions. It is useless noise. — tckmn
– tckmn, Commented Feb 25, 2013 at 19:47

bagage · Accepted Answer · 2013-02-25 19:48:01Z

2

Your 'else if' is wrong, the correct syntax is 'elif'.

answered Feb 25, 2013 at 19:48

bagage

1,1241 gold badge21 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

itsh Over a year ago

Thank you for the quick response. Actually I do programming in Java and now I am learning bash (which is going to be my scripting language). So I am having hard time with syntax. Anyways thanks again.

chepner · Accepted Answer · 2013-02-25 19:58:58Z

1

You need to repeat the command in each clause of the if statement:

function reset_db
{
        if [ $usr_input == 1 ]  
        then
        sqlplus -s $USR/$pwd@$SID << EOF

        delete from table1 where component = 'ABC';
        delete from table2 where component = 'ABC';
        exit
EOF
        elif [ $usr_input == 2 ]; then

        sqlplus -s $USR/$pwd@$SID << EOF
        delete from table1 where component = 'XYZ';
        delete from table2 where component = 'XYZ';
        exit
EOF
fi
}

As a simplification, you should refactor this:

reset_db () {
    if [[ $usr_input = 1 ]]; then
      to_delete='ABC'
    elif [[ $usr_input = 2 ]]; then
      to_delete='XYZ'
    else
      return
    fi
    sqlplus -s "$USR/$pwd@$SID" <<EOF
    delete from table1 where component = '$to_delete'
    delete from table2 where component = '$to_delete'  
    exit
EOF
    fi
}

answered Feb 25, 2013 at 19:58

chepner

538k77 gold badges594 silver badges746 bronze badges

7 Comments

itsh Over a year ago

Thank you. That's very helpful to have a precise code. But I have some schemas which end with 'ABC'. Ex: read_ABC, write_ABC, execute_ABC. If I give something like: delete from table1 where component = '%\_$to_delete' then I am getting "invalid character" error. How can I do this?

itsh Over a year ago

It is pointing the error at "%".

delete from upgrade_details where component = %_'ABC'                                                * ERROR at line 1: ORA-00911: invalid character

igr Over a year ago

It would be better to use bind variable.

itsh Over a year ago

It is fixed. There was a typo. Also I used "like" to do the string match.

itsh Over a year ago

1. I want to prompt user for the owner name and read in a variable called owner ` read -p "Enter schema owner: " owner 2. Now I want to pass this value to this block of sql: declare usr2kill varchar2(5) := '&owner'; sqlstmt varchar2(10); ......some more code to delete the users... My code looks like this:

function reset_db { read -p "Enter Schema Owner: " owner  declare         usr2kill varchar2(5) := '&owner';         sqlstmt varchar2(10); begin ......some more code to drop the users...

Please let me know how can I do this. Thanks in advance.

|

Collectives™ on Stack Overflow

How to do sqlplus in bash if else?

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related