SQL select statement in UNIX inside/within IF..THEN statement.

Question

Hi -

I just want ask the steps when trying to create a simple SQL select statement in UNIX inside/within IF..THEN..FI statement.

I know how to use the 'select' and 'if..then' statements in SQL*Plus, but I'm having a difficulties having a UNIX script to point to variables: If 'ABC' to 'Select...'

Example:

if [ "$?" = 'ABC' ] then SELECT employid, name, age FROM tablename; else exit 1 fi

if [ "$?" = 'XYZ' ] then SELECT employid, name, age FROM tablename; else exit 1 fi

How do I put it in a UNIX script more correctly syntax wise and right to the point?

Thanks.

Answer 1

This sounds like you're trying to embed SQLPlus in a shell script. From memory the incantation should look something like:

if [ $? -eq ABC ]; then
SQLPLUS /S USER/PASS@Instance <<EOF
SET echo off;
SET pagesize 0;
SET heading off;
SPOOL foo.out
select foo from bar
EOF
fi

Everything between the SQLPLUS and EOF is passed to SQLPlus, so we have some statements to control the formatting (you may want different ones) and the actual query. The SPOOL command in the SQLPlus script sends the output to a file. For more detailed docs on using SQLPlus, You can download them from Oracle's web site.

Answer 2

The above answer was ok. However, I knew that, using SQLPlus in a shell script and unfortunately, I don't need the SQLPlus script to send the output to a file. In other words: Is there any other way of doing this, just print the output to a log?

Answer 3

Remember that echo is your friend.

if [ "$?" = "ABC" ] then echo SELECT employid, name, age FROM tablename; else exit 1; fi

You can embed shell script variables and such. Be careful of the need to quote things the shell wants to act upon, like quotes and semi-colons.

Answer 4

Have you considered using perl or other scripting language that includes database connection functionality. That way you avoid the clunky shell script/SQL*Plus linkage

Answer 5

about if command

scripter