Sunday 10 September 2017

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

Friday 1 September 2017

Display formatted console with Oracle apex 5.1(Oracle Application Express)

Let's say we want to display object as a table when we console. Normally we just console everything and displaying as it is without formatting our object.It would be nice and beautiful if we just format it a bit. But question is HOW?

Ummm.... May be a structured way is way to go!! WAIT someone just said structured way then we must use table(That's what I think).

We can test this in console by pressing F12(Most of the browser support this using windows)

F12 ~> Console(Chrome)
F12 ~> Debugger(Firfox)

Code Snippet :

var Apps =  [
    {"Application":"Sample Database", "Status":"Activated"},
    {"Application":"Interactive Grid", "Status":"Activated"},
    {"Application":"Oracle Stuff", "Status":"De-activated"}
];
console.table(Apps);
Output :


Cool right!! Just check how much cleaner it looks and it's easy to verify all the values which we are getting.

Now, let's try this in our apex application:
Here i did use a blueprint application we will just create a simple page and static region in it.(this will show case you how we can achieve the same in complex scenario when we have to deal with Ajax and Json)

Region will have :

<label><input type="checkbox" class="chkCompare" title='Pranav' id='1' />
  <span style="font-size: 30px;">Pranav</span>
</label><br><br>
<label><input type="checkbox" class="chkCompare" title='From' id='2'/>  <span style="font-size: 30px;">From</span></label><br><br>
<label><input type="checkbox" class="chkCompare" title='India' id='3'/>  <span style="font-size: 30px;">India</span></label><br><br>
<label><input type="checkbox" class="chkCompare" title='Work as a' id='4'/>  <span style="font-size: 30px;">Work as a</span></label><br><br>
<label><input type="checkbox" class="chkCompare" title='Freelancer' id='5'/>  <span style="font-size: 30px;">Freelancer</span></label>

Now add some javascript code:

function test1(){
  var checkedElemets = $('.chkCompare:checked');
  var values = checkedElemets.map(function() {
    return {
      id: this.id,
      name: this.title
    };
  }).get();
  console.clear();
  console.table(values);      
}

Of course we have a button on a page which will call this function and kindly check in DOM window what output message it displays in console.

Output:

Demo : Formatted Console

Again this is very neat and simple example just try to use this in complex scenario it will help.

Some of the very awesome console API we can find here:
Console API Reference
Debugging Asynchronous JavaScript with Chrome DevTools



Show values in right side of shuttle

While working with select list and shuttle, when we want to display values into right side of shuttle depending upon selection from select ...