Simple sql script fails on execution

Question

Below is a sample bash script and it seems to error out when i execute the bash. The sql works on its own but in a script oddly.

Please advise

• JOB_RUNNING='SELECT count(1) from gv b,gv a WHERE b.paddr = a.addr AND type='''USER''' AND b.status='''ACTIVE''' AND b.program='''Thin Client''' * ERROR at line 1: ORA-00942: table or view does not exist'

JOB_RUNNING=`sqlplus -s "/ as sysdba" << EOF
SET FEEDBACK OFF;
SET HEADING OFF;
SELECT count(1) from gv\$session b,gv\$process a WHERE b.paddr = a.addr AND type='USER' AND b.status='ACTIVE' AND b.program='Thin Client';
 exit;
EOF
`

Answer 1

The output shows from gv b,gv a so the $ is still being treated as a shell variable.

From BashFAQ:

Backslashes (\) inside backticks are handled in a non-obvious manner ...

Either double-escape those:

JOB_RUNNING=`sqlplus -s "/ as sysdba" << EOF
SET FEEDBACK OFF;
SET HEADING OFF;
SELECT count(1) from gv\\$session b,gv\\$process a WHERE b.paddr = a.addr AND type='USER' AND b.status='ACTIVE' AND b.program='Thin Client';
 exit;
EOF
`

or use $() instead of backticks:

JOB_RUNNING=$(sqlplus -s "/ as sysdba" << EOF
SET FEEDBACK OFF;
SET HEADING OFF;
SELECT count(1) from gv\$session b,gv\$process a WHERE b.paddr = a.addr AND type='USER' AND b.status='ACTIVE' AND b.program='Thin Client';
 exit;
EOF
)

See also: What is the benefit of using $() instead of backticks in shell scripts?