Storing the result from a v$ table into a variable in a script

Typically while selecting from a V$ table in a Unix script, I have to escape the “$” character as shown below:

sqlplus -s $sqlplus_command << EOF > backup_check_temp.txt
  select  value 
  from    V\$RMAN_CONFIGURATION 
  where   NAME = 'RETENTION POLICY'
  ;  
  exit;
EOF

The above will successfully run the SQL and write the output in the spool file.

However, if instead of the output in a spool file, you want the output in a Unix variable for further processing, you would change the code to

export ret_period=`sqlplus -s $sqlplus_command << EOF
                   select   value 
                   from     V\$RMAN_CONFIGURATION 
                   where    NAME = 'RETENTION POLICY'
                   ;
EOF`
if  [ -z "$ret_period" ]; then
    export ret_period='Not found'
fi    

This will fail with:

+ export 'ret_period=                select value from V where NAME = '\''RETENTION POLICY'\''
                                       *
ERROR at line 1:
ORA-00942: table or view does not exist'

The reason is that the second method of coding results in two levels of interpretation, each of which requires the “$” character to be escaped. To get the code working, it has to be coded as follows (note the two “\\” escape characters):

export ret_period=`sqlplus -s $sqlplus_command << EOF
                   select   value 
                   from     V\\$RMAN_CONFIGURATION 
                   where    NAME = 'RETENTION POLICY'
                   ;
EOF`
if  [ -z "$ret_period" ]; then
    export ret_period='Not found'
fi