Download line wise(record) csv using Oracle Application express

We are going to discuss about how we can download the CSV file for line wise records in oracle apex.

Let's Create a interactive(IR)/classical report on our page(based on emp table).

Region Query :

SELECT  empno,
ename,
job,
sal,
'<a class="btn btn-large btn-primary logout" href="#"> <span class="pi_barcode fa fa-download"  onclick="download_csv('||empno||','''||empno||''')"  style="align-items: center"></a>' AS "Dwnd_Csv"
FROM emp

What's this ?
<a class="btn btn-large btn-primary logout" href="#"> <span class="pi_barcode fa fa-download"  onclick="download_csv('||empno||','''||empno||''')"  style="align-items: center"></a>


Here we are defing what class we want and what should it look like. Oracle application express have inbuilt classes define so we just need to refernce them to get the desire output.
Onclick: It's an event will fire whenever user clicks on Icon.(Our function download_csv(param1,prama2);

So far so good but where is our actual function !

We need to create a function in Function and Global Variable Declaration and call it using onclick event.

function  download_csv(eno,eno) { // here you can specify you're params for demo purpose i'd pass more params
apex.server.process ("download_csv",{ x01:eno, x02:eno},
                                     {
                                        type: 'GET',
                                        dataType: 'json',
                                        success: function(l_json_str)
{
// first we make sure it's a object or not then deal with it accordingly.
var arrData = typeof l_json_str != 'object' ? JSON.parse(l_json_str) : l_json_str;
var CSV = '';
var row = "";
for (var index in arrData[0]) {
row += index + ',';
}
row = row.slice(0, -1);
CSV += row + '\r\n';

for (var i = 0; i< arrData.length; i++) {
var row = "";
for (var index in arrData[i]) {
var arrValue = arrData[i][index] == null ? "" : arrData[i][index] ;
row += arrValue + ',';
}
row.slice(0, row.length - 1);
CSV += row + '\r\n';
}
var fileName = "EmployeeDetails"; // This will be our CSV file name.
/*if(msieversion()){
var IEwindow = window.open();
IEwindow.document.write('sep=,\r\n' + CSV);
IEwindow.document.close();
IEwindow.document.execCommand('SaveAs', true, fileName"barcode.csv");
IEwindow.close();
} else {*/
var uri = 'data:application/csv;charset=utf-8,' + escape(CSV);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = fileName + ".csv";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
//}
}
});
}


Javascript function is calling our ajax server process which is...

Create Ajax callback with the name: download_csv

DECLARE
l_json_str VARCHAR2(1000); -- based on data we are fetching we need to change this
L_eno VARCHAR2(50):= apex_application.g_x02;
BEGIN

l_json_str:='[';
for x in (SELECT * FROM emp
WHERE empno = L_eno )
LOOP
l_json_str:=l_json_str||'{"EmpNo":"'||x.empno||'","EmpName":"'||x.ename||'","Job":"'||x.Job||'","Salary":"'||x.sal||'"},';
END LOOP;
l_json_str:=substr(l_json_str,1,length(l_json_str)-1);
l_json_str:=l_json_str||']';
sys.htp.p(l_json_str);              
END;

Assuming we know how ajax server process works with-in oracle application express.


Demo : Download_csv
Twitter : Follow
Linked-In : Connect

Find table size and set column size

Find table size :

 select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type='TABLE' and segment_name='<yourtablename>';

Set column size :

sql>column column_name format a30
sql>set linesize 300

Cheers.

v('') Vs. apex_application.globalvariable

As i describe in Previous post their are list of global variable which we can use in pl/sql aslo.
Now, Problem is v(' '); is not fix oracle apex may change it in future as they have done it in past so better approach will be use it as apex_application.globalvariable for that i have created a Function as shown below(change it as per need).

Function :

CREATE OR REPLACE FUNCTION GET_CUSTOMERNAME(custid Number) RETURN VARCHAR2 IS
tmpVar VARCHAR2(200);

BEGIN
 
     SELECT FIRST_NAMEINTO tmpVar FROM CUSTOMER WHERE CUST_ID = custid
   and cmp_id = apex_application.g_user;
   RETURN tmpVar;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END GET_CUSTOMERNAME;
/

Find column name of a particular string

So, basically when you need to find a particular column string from all the available table then its kind of lengthy process if you look into every table for particular column string so below query will make works more easier.

select table_name from dba_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';

Without DBA privileges:

select table_name from all_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';