sqloracle-databasesys-refcursor

Return the SQL Statement of an Explicit Cursor

Taking the example shown in Oracle Forums: Generating excel(xls) using plsql, i would like to generate an excel file from a query's data set.

The example provided there works. however, there are some challenges i'm encountering:

  1. My SQL query is quite long and exceeds the VARCHAR2 4000 Character limitation.
  2. I would like to pass parameters to my query.
  3. I would like to do it in a very simple manner and without using Dynamic SQL (if possible).

What I've come up is something like below:

Package Definition:

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,'<ss:Row>');
    FOR j in 1..col_cnt
    LOOP
      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
    END LOOP;
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Row>');

    -- Output the data
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN v_ret = 0;
      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Row>');
      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,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
                      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
                      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell ss:StyleID="OracleDate">');
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
                      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
        ELSE
          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
          UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
          UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
          UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
        END CASE;
      END LOOP;
      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Row>');
    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,'<?xml version="1.0"?>');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
  END start_workbook;

  PROCEDURE end_workbook (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Workbook>');
  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,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Table>');
  END start_worksheet;

  PROCEDURE end_worksheet (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Table>');
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Worksheet>');
  END end_worksheet;
  --
  PROCEDURE set_date_style (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Styles>');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Style ss:ID="OracleDate">');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Style>');
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Styles>');
  END set_date_style;

end tabletoexcel;

now, my anonymous block would be preferably something like below (please take note of the comments):

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;

Is it possible to pass an explicit cursor definition to a DBMS_SQL 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?

Solution

  • Yes, you can do that with DBMS_SQL.TO_CURSOR_NUMBER function. You Procedure will look like this:

    PROCEDURE run_query(p_cur IN OUT SYS_REFCURSOR) IS
    ...
BEGIN

    c := DBMS_SQL.TO_CURSOR_NUMBER(p_cur);

    -- get a description of the returned columns
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
    ...

    Then you have to call it like this:

    declare

    l_cur SYS_REFCURSOR;

BEGIN

    OPEN l_cur FOR
       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
       ...;

    tabletoexcel.run_query(l_cur);

    OPEN FOR Statement allows CLOB as statement, so there is no practical limit in terms of size.

    Since you don't know at design time which columns will be selected (at least I assume so) there is no way to get rid of DBMS_SQL.DESCRIBE_COLUMNS and DBMS_SQL.DEFINE_COLUMN. Otherwise you can use FETCH Statement instead of DBMS_SQL.FETCH_ROWS(c)