Posted by : Akshay Patil Monday, 19 October 2015

Following PL/SQL block will help you to export or download table data directly in your browser with .xsl format (workbook with multiple sheets or tabs).
As shown below :

declare
    l_clob clob;
    l_blob blob;
    l_sql_delimiter varchar2(30);
    l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
    l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    l_dest_offset   integer := 1;
    l_source_offset integer := 1;


 PROCEDURE run_query(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

    l_clob := l_clob||'<ss:Row>';
    FOR j in 1..col_cnt
    LOOP
      l_clob := l_clob||'<ss:Cell>';
      l_clob := l_clob||'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>';
      l_clob := l_clob||'</ss:Cell>';
    END LOOP;
    l_clob := l_clob||'</ss:Row>';
    -- Output the data
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN v_ret = 0;
      l_clob := l_clob||'<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);
                      l_clob := l_clob||'<ss:Cell>';
                      l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
                      l_clob := l_clob||'</ss:Cell>';
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                      l_clob := l_clob||'<ss:Cell>';
                      l_clob := l_clob||'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>';
                      l_clob := l_clob||'</ss:Cell>';
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                      l_clob := l_clob||'<ss:Cell ss:StyleID="OracleDate">';
                      l_clob := l_clob||'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>';
                      l_clob := l_clob||'</ss:Cell>';
        ELSE
          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
          l_clob := l_clob||'<ss:Cell>';
          l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
          l_clob := l_clob||'</ss:Cell>';
        END CASE;
      END LOOP;
      l_clob := l_clob||'</ss:Row>';
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
  END;
  --
  PROCEDURE start_workbook IS
  BEGIN
    l_clob := l_clob||'<?xml version="1.0"?>';
    l_clob := l_clob||'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
  END;
  PROCEDURE end_workbook IS
  BEGIN
    l_clob := l_clob||'</ss:Workbook>';
  END;
  --
  PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
  BEGIN
    l_clob := l_clob||'<ss:Worksheet ss:Name="'||p_sheetname||'">';
    l_clob := l_clob||'<ss:Table>';
  END;
  PROCEDURE end_worksheet IS
  BEGIN
    l_clob := l_clob||'</ss:Table>';
    l_clob := l_clob||'</ss:Worksheet>';
  END;
  --
  PROCEDURE set_date_style IS
  BEGIN
    l_clob := l_clob||'<ss:Styles>';
    l_clob := l_clob||'<ss:Style ss:ID="OracleDate">';
    l_clob := l_clob||'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>';
    l_clob := l_clob||'</ss:Style>';
    l_clob := l_clob||'</ss:Styles>';
  END;



begin
l_clob := null;
dbms_lob.createtemporary(l_blob, true);
--l_clob :='akshay patil';
-- Generate your file content here in l_clob.

htp.flush;
    htp.init;
sys.OWA_UTIL.mime_header ('application/octet', FALSE);
-- sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' );
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
-- sys.htp.p('Content-Disposition: inline; filename="FILENAME.TXT"' );
sys.HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
sys.owa_util.http_header_close;

 start_workbook;
 set_date_style;
 Start_Worksheet('Customers');
 run_query('select * from u_customer where rownum<10');
 end_worksheet;
 Start_Worksheet('Employees');
 Run_Query('select * from u_user where rownum<10');
 end_worksheet;
 end_workbook;

dbms_lob.converttoblob (
dest_lob    => l_blob,
src_clob    => l_clob,
amount      => DBMS_LOB.LOBMAXSIZE,
dest_offset => l_dest_offset,
src_offset  => l_source_offset,
blob_csid   => DBMS_LOB.DEFAULT_CSID,
lang_context=> l_lang_context,
warning     => l_warning
);

sys.wpg_docload.download_file(l_blob);
apex_application.stop_apex_engine;
exception when others then
sys.htp.prn('error: '||sqlerrm);
apex_application.stop_apex_engine;
end;

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Welcome to My Blog

Study Basics

Oracle APEX Oracle SQL Oracle PL/SQL

Popular Post

Blogger templates

Total Pageviews

Powered by Blogger.

Unordered List

Follow us on Facebook!

- Copyright © TechnicalBits -Robotic Notes- Powered by Blogger - Designed by Johanes Djogan -