title: Oracle Spool using Dynamic SQL
body: <p>I'm trying to pass a dynamic SQL statement to spool out to a text file using SQL*Plus, but I can't seem to execute the select statement I'm generating. </p>
  
  <pre><code>set linesize 10000 pagesize 0 embedded on
  set heading off feedback off verify off trimspool on trimout on  termout off
  set underline off
  
  COLUMN gen_sql   NEW_VALUE gen_sql_
  SELECT 'SELECT * FROM USER_TAB_COLS WHERE ROWNUM &lt; 10' gen_sql_ FROM DUAL;
  
  SPOOL 'myfilename.csv'
  
  EXECUTE IMMEDIATE &amp;gen_sql_
  
  SPOOL OFF
  /
  </code></pre>
  
  <p>I can't seem to use <code>EXECUTE IMMEDIATE</code>. Is there another way to execute the results of the select statement??</p>
  
  <p>MORE DETAIL:</p>
  
  <p>I have a set of views whose output I'd like to generate as <em>formatted</em> CSV files. I'm using dynamic SQL to create the formatting essentially. I generate something similar to:</p>
  
  <p><code>SELECT TRIM(col1)||','||TRIM(col2)...FROM {myview}</code></p>
  
  <p>I'm using the following to generate it this way:</p>
  
  <pre><code>COLUMN gen_sql   NEW_VALUE gen_sql_
  SELECT 'SELECT ' || LISTAGG ('TRIM('||COLUMN_NAME||')', '||'',''|| ') 
       WITHIN GROUP (ORDER BY COLUMN_ID) gen_sql FROM...
  </code></pre>
  
  <p>Anyway, I'm able generate this SQL statement and store into a SQL*PLUS variable, but I just need to execute it after the SPOOL statement so that it will print to the file. I'm not sure how to execute it. Normal statements work, such as:</p>
  
  <pre><code>SPOOL 'myfilename.csv'
  SELECT 1 col1 FROM DUAL;
  SPOOL OFF
  /
  </code></pre>
  
  <p>So, it would seem reasonable that I could something similar but executing the contents of my variable like:</p>
  
  <pre><code>SPOOL 'myfilename.csv'
  --- RUN MY DYNAMIC SQL ----
  SPOOL OFF
  /
  </code></pre>