title: How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
body: <p>In MS SQL Server if I want to check the results from a Stored procedure I might execute the following in Management Studio.</p>
  
  <pre><code>--SQL SERVER WAY
  exec sp_GetQuestions('OMG Ponies')
  </code></pre>
  
  <p>The output in the results pane might look like this.</p>
  
  <pre><code>ID    Title                                             ViewCount   Votes 
  ----- ------------------------------------------------- ---------- --------
  2165  Indexed View vs Indexes on Table                  491         2  
  5068  SQL Server equivalent to Oracle’s NULLS FIRST     524         3 
  1261  Benefits Of Using SQL Ordinal Position Notation?  377         2 
  
  (3 row(s) affected)
  </code></pre>
  
  <p>No need to write loops or PRINT statements.   </p>
  
  <p>To do the same thing in Oracle I might execute the following anonymous block in SQL Developer</p>
  
  <pre><code>--ORACLE WAY
      DECLARE
          OUTPUT  MYPACKAGE.refcur_question;
          R_OUTPUT MYPACKAGE.r_question;
          USER    VARCHAR2(20);
  
  BEGIN
  
    dbms_output.enable(10000000);
    USER:= 'OMG Ponies';
    recordCount := 0;
  
  
  
    MYPACKAGE.GETQUESTIONS(p_OUTPUT =&gt; OUTPUT, 
    p_USER=&gt; USER, 
  
    ) ;
  
  
  
  
    DBMS_OUTPUT.PUT_LINE('ID |  Title | ViewCount | Votes' );
  
    LOOP 
      FETCH OUTPUT
      INTO R_OUTPUT;
  
           DBMS_OUTPUT.PUT_LINE(R_OUTPUT.QUESTIONID || '|' || R_OUTPUT.TITLE 
                 '|' || R_OUTPUT.VIEWCOUNT '|' || R_OUTPUT.VOTES);
            recordCount := recordCount+1;
  
  
  
  
   EXIT WHEN OUTPUT % NOTFOUND;  
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Record Count:'||recordCount);
        CLOSE OUTPUT;
  
  
      END;
  </code></pre>
  
  <p>This outputs like </p>
  
  <pre><code>ID|Title|ViewCount|Votes 
  2165|Indexed View vs Indexes on Table|491|2  
  5068|SQL Server equivalent to Oracle’s NULLS FIRST|524|3 
  1261|Benefits Of Using SQL Ordinal Position Notation?|377|2 
  Record Count: 3
  </code></pre>
  
  <p>So the SQL version has 1 line and the oracle has 18 and the output is ugly. Its exacerbated if there are a lot of columns and/or the data is numeric.   </p>
  
  <p>What's odd to me about this is that if I write this statement in either SQL Developer or Management studio...</p>
  
  <pre><code>SELECT 
  ID, 
  Title, 
  ViewCount, 
  Votes
  FROM votes where user = 'OMG Ponies'  
  </code></pre>
  
  <p>The results are fairly similar. This makes me feel like I'm either missing a technique or using the wrong tool.</p>