Pass Variables from Shell to Sql

[Lynda15]

Hi all,
I need to know if it's possible to pass variables from a shell script to sqlplus.
The script reads a Table Name from user input & executes a Select statement based on
that input.

Script:

....
echo "Enter table name: \c "
read answer

sqlplus 'oracle/*** ' <<!
      spool columns.sql
      @select.sql
      spool off
exit
!

Inside script SELECT.SQL , i need to use the variable "answer" to run the query.
($answer doesn't do the job...)
Any help would be highly appreciated.
Thanks in advance...

[burschik]

You are already using a here document to provide sql statements to the standard input of sqlplus. Instead of using "@select.sql" to include that file, you could include the statements it contains, and use variables in these statements, e.g.:

echo "Enter table name: \c "
read answer

sqlplus 'oracle/*** ' <<END
      spool columns.sql
      select whatever from wherever where some_value=$answer
      spool off
      exit
END

[Lynda15]

You are already using a here document to provide sql statements to the standard input of sqlplus. Instead of using "@select.sql" to include that file, you could include the statements it contains, and use variables in these statements, e.g.:

echo "Enter table name: \c "
read answer

sqlplus 'oracle/*** ' <<END
      spool columns.sql
      select whatever from wherever where some_value=$answer
      spool off
      exit
END

Thanks for ur reply....
I know this works, I've already tried it....
but in my case, if I need the select.sql to be outside the script, how can it work?

[burschik]

Assuming that "select.sql" contains shell-variable references, like "$answer", for example, you could do something like this:

eval echo  $(< select.sql ) | sqlplus 'oracle/*** '

IMPORTANT NOTE: You must use the less-than operator rather than < But I was not able to enter that character in the edit field.