title: Return the SQL Statement of an Explicit Cursor
body: <p>Taking the example shown in Oracle Forums: <a href="https://community.oracle.com/thread/2544010" rel="nofollow noreferrer" title="Generating excel&#40;xls&#41; using plsql">Generating excel(xls) using plsql</a>, i would like to generate an excel file from a query's data set.</p>
  
  <p>The example provided there works. however, there are some challenges i'm encountering:</p>
  
  <ol>
  <li>My SQL query is quite long and exceeds the <code>VARCHAR2</code> 4000 Character limitation.</li>
  <li>I would like to pass parameters to my query.</li>
  <li>I would like to do it in a very simple manner and without using Dynamic SQL (if possible).</li>
  </ol>
  
  <p>What I've come up is something like below:</p>
  
  <p><strong>Package Definition:</strong></p>
  
  <pre><code>create or replace package tabletoexcel
  as
  
    PROCEDURE run_query(p_fh  IN UTL_FILE.FILE_TYPE
                      , p_cur IN SYS_REFCURSOR);
    PROCEDURE start_workbook (p_fh  IN UTL_FILE.FILE_TYPE);
    PROCEDURE end_workbook (p_fh  IN UTL_FILE.FILE_TYPE);
    PROCEDURE start_worksheet(p_fh        IN UTL_FILE.FILE_TYPE
                           ,  p_sheetname IN VARCHAR2);
    PROCEDURE end_worksheet (p_fh  IN UTL_FILE.FILE_TYPE);
    PROCEDURE set_date_style (p_fh  IN UTL_FILE.FILE_TYPE);
  
  end tabletoexcel;  
  
  create or replace package body tabletoexcel
  as
  
    PROCEDURE run_query(p_fh    UTL_FILE.FILE_TYPE
                      , p_sql IN VARCHAR2) IS
      v_v_val     VARCHAR2(4000);
      v_n_val     NUMBER;
      v_d_val     DATE;
      v_ret       NUMBER;
      c           NUMBER;
      d           NUMBER;
      col_cnt     INTEGER;
      f           BOOLEAN;
      rec_tab     DBMS_SQL.DESC_TAB;
      col_num     NUMBER;
    BEGIN
      c := DBMS_SQL.OPEN_CURSOR;
      -- parse the SQL statement
      DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
      -- start execution of the SQL statement
      d := DBMS_SQL.EXECUTE(c);
      -- get a description of the returned columns
      DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  
      -- bind variables to columns
      FOR j in 1..col_cnt
      LOOP
        CASE rec_tab(j).col_type
          WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
          WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
          WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
        ELSE
          DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
        END CASE;
      END LOOP;
  
      -- Output the column headers
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Row&gt;');
      FOR j in 1..col_cnt
      LOOP
        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Cell&gt;');
        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Data ss:Type="String"&gt;'||rec_tab(j).col_name||'&lt;/ss:Data&gt;');
        UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Cell&gt;');
      END LOOP;
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Row&gt;');
  
      -- Output the data
      LOOP
        v_ret := DBMS_SQL.FETCH_ROWS(c);
        EXIT WHEN v_ret = 0;
        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Row&gt;');
        FOR j in 1..col_cnt
        LOOP
          CASE rec_tab(j).col_type
            WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Cell&gt;');
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Data ss:Type="String"&gt;'||v_v_val||'&lt;/ss:Data&gt;');
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Cell&gt;');
            WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Cell&gt;');
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Data ss:Type="Number"&gt;'||to_char(v_n_val)||'&lt;/ss:Data&gt;');
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Cell&gt;');
            WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Cell ss:StyleID="OracleDate"&gt;');
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Data ss:Type="DateTime"&gt;'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'&lt;/ss:Data&gt;');
                        UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Cell&gt;');
          ELSE
            DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
            UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Cell&gt;');
            UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Data ss:Type="String"&gt;'||v_v_val||'&lt;/ss:Data&gt;');
            UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Cell&gt;');
          END CASE;
        END LOOP;
        UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Row&gt;');
      END LOOP;
      DBMS_SQL.CLOSE_CURSOR(c);
    END run_query;
  
    PROCEDURE start_workbook (p_fh    UTL_FILE.FILE_TYPE) IS
    BEGIN
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;?xml version="1.0"?&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"&gt;');
    END start_workbook;
  
    PROCEDURE end_workbook (p_fh    UTL_FILE.FILE_TYPE) IS
    BEGIN
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Workbook&gt;');
    END end_workbook;
    --
    PROCEDURE start_worksheet(p_fh    UTL_FILE.FILE_TYPE
                          ,   p_sheetname IN VARCHAR2) IS
    BEGIN
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Worksheet ss:Name="'||p_sheetname||'"&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Table&gt;');
    END start_worksheet;
  
    PROCEDURE end_worksheet (p_fh    UTL_FILE.FILE_TYPE) IS
    BEGIN
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Table&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Worksheet&gt;');
    END end_worksheet;
    --
    PROCEDURE set_date_style (p_fh    UTL_FILE.FILE_TYPE) IS
    BEGIN
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Styles&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:Style ss:ID="OracleDate"&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Style&gt;');
      UTL_FILE.PUT_LINE(gv_file_type,'&lt;/ss:Styles&gt;');
    END set_date_style;
  
  end tabletoexcel;  
  </code></pre>
  
  <p>now, my anonymous block would be preferably something like below <strong>(please take note of the comments)</strong>:</p>
  
  <pre><code>declare
  
      l_fh            UTL_FILE.FILE_TYPE;
      l_directory     VARCHAR2(30) := 'EMPLOYEE_FILE_DIR';
      l_filename      VARCHAR2(30) := 'myfile.xls';    
      l_sql_statement VARCHAR2(4000);    
  
      Cursor emp_cur (p_emp_no     varchar2
                     ,p_payroll_id number
                     ,p_bg_id      number)
      is
      select  *
      from    table_a
      where   employee_number     = nvl(p_emp_no, employee_number)
      and     payroll_id          = nvl(p_payroll_id, payroll_id);
      and     business_group_id   = p_bg_id
      -- This is the Really really long query but i just placed Table_A for Sample Purposes
  
  BEGIN
  
      l_fh := UTl_file.FOPEN(upper(l_directory),l_filename,'w',32767);
      tabletoexcel.start_workbook (l_fh);
      tabletoexcel.set_date_style (l_fh);
      tabletoexcel.start_worksheet(l_fh, 'EMP');
  
      tabletoexcel.run_query(emp_cur('1', 2, 3));
      -- I'm sure this won't work, but i would like to pass something simple as this.
  
      tabletoexcel.end_worksheet (l_fh);
      tabletoexcel.end_workbook (l_fh);
      UTl_file.FCLOSE(l_fh);
  
  END;
  </code></pre>
  
  <p>Is it possible to pass an explicit cursor definition to a <code>DBMS_SQL</code> package?
  I'm thinking a Built-In Function that Returns the SQL Statement of an Explicit Cursor would solve this problem. Perhaps a Ref Cursor?</p>