• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

SQL query error when Using Shell Script

by admin

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
Note: We have replaced select count(*) from v_$session as select count(*) from v_\$session. There is a backslash after “v_”.

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
  2. How to Shrink the datafile of Undo Tablespace in Oracle Database
  3. How to duplicate a Oracle Database to a previous Incarnation
  4. How to Use Udev Rules to Create oracleasm Disks in CentOS/RHEL 8
  5. Oracle Multitenant: How to Create CDB and PDB
  6. How to load SELinux Module For Oracleasm
  7. Dynamic Oracle Net Server Tracing
  8. Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
  9. Script To Get Tablespace Utilization In Oracle Database 12c
  10. How to Move User datafiles between ASM Diskgroups using Incrementally Updated Backups

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright