The Problem
When using bash profile to execute SQL commands, it errors out when it encounters some special characters. For example, consider the following script:
#!/bin/ksh . ~/.bash_profile sqlplus '/ as sysdba' <<EOF spool /tmp/test.log select count(*) from v_$session; spool off EOF
The log file will contain the following
SQL> select count(*) from v_;
select count(*) from v_
*
ERROR at line 1:
ORA-00942: table or view does not exist
The select count(*) from v_$session will work absolutely fine when issued directly using sqlplus, whereas when issued through shell script it errors out.
The Solution
The log file clearly shows where the issue lies. It is performing a select count(*) from v_ which obviously does not exist. The v_$session value is ignored. That is, anything after the “_” symbol, a special character has been ignored.
Ensure that the special characters are addressed through their escape sequences. In this case, we can overcome the issue by simply rewriting the script as follows:
sqlplus '/ as sysdba' <<EOF spool /tmp/test.log select count(*) from v_\$session; spool off EOF
If you are going to be writing something that requires extensive database access, shell scripting is not the best option. It might be better to rewrite the script in PL/SQL, Perl (which uses syntax similar to that used in shell scripting), Python, Java, or another language of your choice. Shell scripting can be an effective tool for quickly automating repetitive and error-prone administration tasks. The examples in this post provide an introduction to the possibilities available, but are far from comprehensive. Every system has distinct quirks and foibles and a unique configuration. An administrator will develop unique solutions to meet the needs of the particular system.