title: How to pass a query stored in a variable to a sql file. Shell script
body: <p>I am creating a shell script where I have saved entries from a text file into an array.  Those values are properly stored and show the correct contents.  One of those entries contains a simple query and I want to pass it to a sql file.  With that sql query I want to save the results into a text file.</p>
  
  <p>Here is the part of the code that calls the sql file to run the sql script</p>
  
  <pre><code>PURGE_SITES=purge_site.txt
  logmsg "USERID - $PURGES_SITE" n
  
  QUERY=${Unix_Array[4]}
  
  
  echo $QUERY
  sqlplus -s $USER/$PASS &lt;&lt;EndSQL
       @purges_sites.sql $PURGE_SITES '$QUERY'
       EXIT SQL.SQLCODE
  EndSQL
  </code></pre>
  
  <p>for now query stored in ${Unix_Array[4]} is "select -1 from dual"</p>
  
  <p>Here is the file contents of the .sql file</p>
  
  <pre><code>set echo off ver off feed off pages 0
  
  accept fname prompt 'Loading Sites...'
  
  spool &amp;1;
  &amp;2
  /
  spool off
  </code></pre>
  
  <p>It gives me error and reads &amp;2 as "&amp;2" instead of the query saved in the variable.  However when i edit the .sql file and add something beforehand, it will display the correct data from the variable.</p>
  
  <p>Here is the output</p>
  
  <pre><code>select -1 from dual
  File Name===&gt; results.txt
  select -1 from dual
  Loading Sites...SP2-0042: unknown command "&amp;2" - rest of line ignored.
  SP2-0103: Nothing in SQL buffer to run.
  </code></pre>
  
  <p>Here is the output if I add something before &amp;2.</p>
  
  <pre><code>select -1 from dual
  File Name===&gt; results.txt
  select -1 from dual
  Loading Sites...select * from table_table select -1 from dual
                            *
  ERROR at line 1:
  ORA-00933: SQL command not properly ended
  </code></pre>
  
  <p>I typed in select * from table_table before &amp;2.
  So its actually retrieving the value from the variable but something needs to come beforehand in order to pass correctly.</p>
  
  <p>Is there a system execute command in oracle that will execute a query?  &amp;2 just by itself is not allowed.</p>