Download Data to CSV/Excel

TO  FETCH  DATA  FROM  DATABASE &  SAVE IT INTO EXCEL FILE

Step 1: Write a procedure to get data from the database using HTML codes and insert it into a variable of type Clob. As the Example shown below.

PROCEDURE DOWNLOAD_CSV(p_batch_id number) AS

v_download clob;

v_insert varchar2(32000) := '';

v_length  NUMBER;

l_filename VARCHAR2(100);

v_blob  blob;

v_clob clob;

v_mime  VARCHAR2(4000) := 'application/octet-stream';

v_name varchar2(150) := 'Student Names';

v_lang1 varchar2(50) := 'I Lang';

v_lang2 varchar2(50) := 'II Lang';

v_lang3 varchar2(50) := 'III Lang';

v_lang4 varchar2(50) := 'Subject';

v_lang5 varchar2(50) := 'Subject';

v_lang6 varchar2(50) := 'Subject';

v_sub1 varchar2(50);

v_sub2 number;

v_sub3 number;

v_batch varchar2(50) := 'Batchid';

cursor Cur is

select a.firstname,a.lastname

from ct_admission_info a,ct_stud_batch_tbl s

where a.id = s.studid

and s.batchid = p_batch_id;

cursor cur1 is select * from ct_dummy;

begin

/*select subject_id into v_sub1 from CT_BATCH_SUBJECT_TBL

where subject_id = 61

and  batch_id = p_batch_id;

select shortname into v_sub1 from ct_subject_master a, CT_BATCH_SUBJECT_TBL b

where a.id = b.subject_id

and b.batch_id = p_batch_id

and b.subject_id = 61;

select subject_id into v_sub2 from CT_BATCH_SUBJECT_TBL

where subject_id = 62

and  batch_id = p_batch_id;

select subject_id into v_sub3 from CT_BATCH_SUBJECT_TBL

where subject_id = 63

and  batch_id = p_batch_id;*/

v_insert :='<table border ="1" width = "100%">';

v_insert := v_insert||'<tr>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_batch;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_name;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang1;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang2;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang3;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang4;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang5;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang6;

v_insert := v_insert||'</th>';

v_insert := v_insert||'</tr>';

v_insert := v_insert||'<tr>';

v_insert := v_insert||'<td bgcolor="orange">';

v_insert := v_insert||p_batch_id;

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="orange">';

v_insert := v_insert||'';

v_insert := v_insert||'</td>';

 insert into ct_dummy (select shortname from ct_subject_master a, CT_BATCH_SUBJECT_TBL b

where a.id = b.subject_id and b.batch_id = p_batch_id);

for j in cur1

loop

v_insert := v_insert||'<td bgcolor="orange">';

v_insert := v_insert||j.subject;

v_insert := v_insert||'</td>';

end loop;

v_insert := v_insert||'</tr>';

/*v_insert := v_insert||'<td>';

v_insert := v_insert||v_sub2;

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td>';

v_insert := v_insert||v_sub3;

v_insert := v_insert||'</td>';

v_insert := v_insert||'</tr>';*/

for i in cur

loop

v_insert := v_insert||'<tr>';

v_insert := v_insert||'<td>';

v_insert := v_insert||'';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td>';

v_insert := v_insert||i.firstname||i.lastname;

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

/*

for j in c_sub

loop

v_insert := v_insert||'<td>';

v_insert := v_insert||j.subject_id;

v_insert := v_insert||'</td>';

end loop;*/

v_insert := v_insert||'</tr>';

end loop;

v_insert :=v_insert||'</table>';

v_download := v_insert;

v_blob := clob_to_blob_func(v_download); ***

l_filename := 'Attendance.xls';

 ** owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );

                 -- set the size so the browser knows how much to download

   v_length := DBMS_LOB.GETLENGTH(v_blob);

   htp.p('Content-length: ' || v_length);

               -- the filename will be used by the browser if the users does a save as

   htp.p('Content-Disposition:  attachment;filename="'||l_filename|| '"');

                -- close the headers           

   owa_util.http_header_close;

                -- download the BLOB

   wpg_docload.download_file(v_blob);**

  

   delete from ct_dummy;

   commit;

     

END DOWNLOAD_CSV;

Note *** : It is a Standard Function which converts CLOB into BLOB, this is then called inside the procedure as shown above.

Standard Function to converts CLOB into BLOB is given below.

create or replace

FUNCTION clob_to_blob_func(v_clob Clob) RETURN BLOB  AS

v_blob Blob;

v_in Pls_Integer := 1;

v_out Pls_Integer := 1;

v_lang Pls_Integer := 0;

v_warning Pls_Integer := 0;

v_id number(10);

begin

dbms_lob.createtemporary(v_blob,TRUE);

DBMS_LOB.convertToBlob(v_blob,v_clob,DBMS_lob.getlength(v_clob),

v_in,v_out,DBMS_LOB.default_csid,v_lang,v_warning);

RETURN v_blob;

END  clob_to_blob_func;

Note ** : The code with two stars is the standard codes used to give filename.