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”.

Your problem is not exceptions or errors, it's just no data to show.

• UTL_FILE is an oracle pl/sql package that is supplied to allow PL/SQL to read and create text files in the file system.
• UTL_FILE can only read and create text files. Specifically, it cannot be used to read or create binary files.
• UTL_FILE is an appropriate tool for creating reports in the form of  flat file from the database. UTL_FILE is also used  for reading files.

The following steps must be followed in order to run UTL_FILE package.

Step 1) Connect as sys database user

Step 2) Create a directory:

SQL> CREATE OR  REPLACE DIRECTORY utl_file_dir  AS ‘E:\PLSQL’;

Directory created.
In the above query utl_file_dir  is the logical name for the path ‘E:\PLSQL’.

• We can mention the logical name utl_file_dir  inside the program in uppercase within single quotes (utl_file_dir is mapped to the directory ‘E:\PLSQL’)

• We can create any number of logical path names(DBA directories) in oracle 10g.

• A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example:

CONNECT / AS SYSDBA
GRANT read, write ON DIRECTORY utl_file_dir TO scott;

• Provide user access to the UTL_FILE package (created by catproc.sql):

GRANT EXECUTE ON UTL_FILE TO scott;

Consider following example that performs read and write operations on file from client machine to server machine:

Note: In Client Server File operations please make your source and destination folder shared to avoid issues while directory creation.

CREATE OR REPLACE DIRECTORY server_dir AS '\\NINANI-D01\joss_upload';
GRANT READ, WRITE ON DIRECTORY server_dir TO scott;

CREATE OR REPLACE DIRECTORY client_dir AS '\\APATIL-D01\joss_upload';
GRANT READ, WRITE ON DIRECTORY client_dir TO scott;

DECLARE
 V1 VARCHAR2(32767);
 F_READ UTL_FILE.FILE_TYPE;
 F_WRITE UTL_FILE.FILE_TYPE;
 v_count number:=0;
BEGIN
  F_READ := UTL_FILE.FOPEN('CLIENT_DIR','Extract.csv','R');
  F_WRITE := UTL_FILE.FOPEN('SERVER_DIR', 'Extract.csv', 'W');
 
    BEGIN
    LOOP
       UTL_FILE.GET_LINE(F_READ,V1);
       UTL_FILE.PUT_LINE(F_WRITE, V1);
       v_count:=v_count+1;
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of file..');
  END;   
 
  UTL_FILE.FCLOSE(F_read);
  UTL_FILE.FCLOSE(F_WRITE);
 
   DBMS_OUTPUT.PUT_LINE(V_COUNT||' Records Inserted');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END ; 

CREATE TABLE na_dept (deptno NUMBER (10), dname  VARCHAR2(10), loc VARCHAR2(10));

        CREATE VIEW na_dept_view AS SELECT *FROM na_dept;

INSERT INTO na_dept_view VALUES      (10,'ACCOUNTING','ENGLAND');

SELECT *FROM na_dept;

What is View?

• A view is a named and validated SQL query which is stored in the Oracle data dictionary. 
• View does not have storage of its own (i.e. View does not hold any data) .
• It is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables.
• A view is a logical representation of the data that can be used just like a table in SELECT statement.
• They represent the data of one of more tables and derives its data from the tables on which it is based.
• DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. These tables are called base tables. Views can be based on actual tables or another view also.
• Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

Views are useful for security and information hiding, but can cause problems if nested too deeply. 
Some of the advantages of using views:

1. Reduce the complexity of SQL statements
2. Share only specific rows in a table with other users
3. Hide the NAME and OWNER of the base table

CREATE TABLE na_dept (deptno NUMBER (10), dname VARCHAR2(10), loc VARCHAR2(10));

        CREATE VIEW na_dept_view AS SELECT *FROM na_dept;

INSERT INTO na_dept_view VALUES     (10,'ACCOUNTING','ENGLAND');

SELECT *FROM na_dept;