title: Display Dynamic EXECUTE Output Within pl/sql From sqlplus
body: <p>How to get the dynamic select results of <code>EXECUTE</code> within PL/SQL from <code>Oracle sqlplus</code>?</p>
  
  <p>I'm writing a simple sqlplus script to collect the sum of all <code>NUMBER</code> columns of a given table:</p>
  
  <pre><code>SET SERVEROUTPUT ON
  
  DECLARE
          CURSOR column_cur IS
                  SELECT column_name FROM ALL_TAB_COLS
                  WHERE owner = '&amp;scheme_name' AND table_name = '&amp;table_name'
                  AND data_type = 'NUMBER';
          sql_query VARCHAR2(32767);
  BEGIN
          sql_query := 'select ';
          FOR column_rec IN column_cur LOOP
                  sql_query := sql_query || 'SUM(' || column_rec.column_name ||
                          ') "SUM(' || column_rec.column_name || ')", ';
  
          END LOOP;
          sql_query := substr(sql_query, 0, length(sql_query)-2) || -- remove trailing ', '
                  ' from &amp;scheme_name' || '.&amp;table_name';
          EXECUTE IMMEDIATE sql_query;
  END;
  /
  </code></pre>
  
  <p>The dynamically generated SQL statement, when executed, gives something like:</p>
  
  <pre><code>SUM(X) | SUM(Y) | SUM(Z) |
  --------------------------
  111    | 222    | 333    |
  </code></pre>
  
  <p>However, even with <code>SET SERVEROUTPUT ON</code>, running the sqlplus script gives only:</p>
  
  <pre><code>PL/SQL procedure successfully completed.
  </code></pre>