Simple sql script fails on execution

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

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?