TechnicalBits

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;

Below PL/SQL Block will help you to download file from specific directory path to your browser.

 First you need to register the directory path of file with oracle using below syntax.

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name: Database-unique directory name.
path_name: Operating system directory path (note that if the OS is case-sensitive, the name must be exact)

grant read, write on directory directory_name to database_schema

DECLARE
 
  V_Amount  Integer;
  V_Src_Loc Bfile;
  v_b       BLOB;


BEGIN

  DBMS_LOB.CREATETEMPORARY(V_B,true);
   v_src_loc  := BFILENAME('MY_DIR', 'myfile.xls');
--MY_DIR is the directory registered with a path where file is present.

   Sys.Dbms_Lob.Open(V_Src_Loc, Dbms_Lob.Lob_Readonly);
   V_Amount := SYS.Dbms_Lob.Getlength(V_Src_Loc);

   htp.flush;
   htp.init;
-- Set the MIME type
   OWA_UTIL.mime_header ('application/octet', FALSE);
   htp.p('Content-length: ' || V_Amount );
   HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
   OWA_UTIL.http_header_close;


   Sys.Dbms_Lob.Loadfromfile(V_B, V_Src_Loc, V_Amount);
   Sys.Dbms_Lob.Close(V_Src_Loc);
  UTL_FILE.FREMOVE(location=>'MY_DIR',filename=>'myfile.xls');

   wpg_docload.download_file(V_B);
   apex_application.stop_apex_engine;



 exception
 when others then
   htp.p('Error '||SQLERRM);

END;

Following PL/SQL Block generates workbook (.xsl) file with two worksheet's named as Employees and Customers containing detail level data about employee and customer.
By using below code,you can directly export the various sql queries output with multiple sheets into single workbook.

To store generated workbook at specific path, first you need to register that path with oracle using below syntax.

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name: Database-unique directory name.
path_name: Operating system directory path (note that if the OS is case-sensitive, the name must be exact)

grant read, write on directory directory_name to database_schema

DECLARE
  v_fh        UTL_FILE.FILE_TYPE;
  v_dir       VARCHAR2(30) := 'MY_DIR'; --Directory Name
  V_File      Varchar2(30) := 'myfile.xls';
  
  V_Amount  Integer;
  V_Src_Loc Bfile;
  v_b       BLOB;
  
  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
    UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
    FOR j in 1..col_cnt
    LOOP
      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    END LOOP;
    UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
    -- Output the data
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN v_ret = 0;
      UTL_FILE.PUT_LINE(v_fh,'<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(v_fh,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
                      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
                      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell ss:StyleID="OracleDate">');
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
                      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
        ELSE
          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
          UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
          UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
          UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
        END CASE;
      END LOOP;
      UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
  END;
  --
  PROCEDURE start_workbook IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'<?xml version="1.0"?>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
  END;
  PROCEDURE end_workbook IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'</ss:Workbook>');
  END;
  --
  PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Table>');
  END;
  PROCEDURE end_worksheet IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'</ss:Table>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Worksheet>');
  END;
  --
  PROCEDURE set_date_style IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'<ss:Styles>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Style ss:ID="OracleDate">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Style>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Styles>');
  END;
BEGIN
  v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
  start_workbook;
  set_date_style;
  Start_Worksheet('Customers');
  run_query('select * from customer where rownum<10');
  end_worksheet;
  Start_Worksheet('Employees');
  Run_Query('select * from user where rownum<10');
  end_worksheet; 
  end_workbook;
  UTL_FILE.FCLOSE(v_fh);
  
END;

1. Name: Highlight based on LOB/Department Column
2. Event: After Refresh
3. Selection Type: Region
4. Region: {select your report region}
5. Condition: No Condition
6. Action: Execute JavaScript code
7. Fire On Page Load: True
8. Code: 

 this.affectedElements.find('table.apexir_WORKSHEET_DATA td[headers="DEPARTMENT_LOB"]').each(function(i){

 var lThis=$(this);

 if(lThis.text()=="ERP"){

  lThis.parent().children().css({"background-color":"#C0C0C0"});

  lThis.parent().children().css({"color":"#CC0000"});

 }

});   var lThis=$(this); if(lThis.text()=="ERP"){  lThis.parent().children().css({"background-color":"#C0C0C0"});   lThis.parent().children().css({"color":"#CC0000"});  }
}); 

1. Selection Type: Region
2. Region: {select your report region}

• Result :

Note : You need to modify dynamic action JavaScript part td[headers="Column_Name"] according your report column alias.

SET SERVEROUTPUT ON;
DECLARE
 V1 VARCHAR2(32767);
 l_file UTL_FILE.FILE_TYPE;
 V_COUNT    NUMBER      :=0;
 V_FILENAME VARCHAR2(50):=NULL;
 l_buffer raw(32767);
 l_amount NUMBER := 32767;
 l_pos    NUMBER := 1;
 L_BLOB BLOB;
 L_BLOB_LEN NUMBER:=0;

  CURSOR GET_CLAIM
  IS SELECT RECEIPT,ATTACH_FILENAME FROM EMPLOYEE_DATA ;
 
BEGIN
FOR REC IN GET_CLAIM
LOOP
L_BLOB     := REC.RECEIPT;
v_FILENAME := rec.ATTACH_FILENAME;
l_blob_len := dbms_lob.getlength(l_blob);
L_POS      := 1; --re-init l_pos var
-- Open the destination file.
l_file     := UTL_FILE.FOPEN('LOCAL_DIR',v_FILENAME, 'WB');
DBMS_OUTPUT.PUT_LINE(V_FILENAME||'  '||l_blob_len);
 
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len
LOOP
dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
utl_file.put_raw(l_file, l_buffer, true);
L_POS := L_POS + L_AMOUNT;
END LOOP;

UTL_FILE.FCLOSE(l_file);
V_COUNT:=V_COUNT+1;

END LOOP;
DBMS_OUTPUT.PUT_LINE(V_COUNT||' '||'files created');

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
 RAISE_APPLICATION_ERROR(-20000, 'ERROR: Invalid PATH FOR file.');
 DBMS_OUTPUT.PUT_LINE('error');
-- Close the file if something goes wrong.
WHEN OTHERS THEN
 IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
 END IF;
 DBMS_OUTPUT.PUT_LINE('error');


END ;

10. Here you can set source to dataset parameter. Source can be Fields, parameters, variables, user defined expressions etc. Thus select appropriate source and click on “Apply”.