Manipulating HTML Table using JS in Oracle APEX

Figure 1: Oracle Apex and Javascript

I am new to Oracle APEX as well as to JavaScript. Recently, at my work, I came across a problem. I did not find any precise Google search result to solve the problem. Now as the problem is resolved, I am writing this blog to make it available on the Google search machine.

In this blog, I will sum values of a column in a table using JS and would display the sum at the last row in the column. Manipulating a HTML table in JS is a straight forward task. In this case, however, the table is made using Oracle APEX hence one can not have that freedom of using DOM(Document Object Module) selector.

If you are here because of your interest in JS solution then it is recommended to jump directly to JS code in heading 3. On the other hand if you are reading this because of Oracle APEX then it is better to stay the whole way until the end.

Problem:

It was a fine day of spring during the lockdown. As usual I was enjoying my home office when all of sudden a message from a user of one of my applications is received to me via MS teams. He was complaining about an error message ORA-01000 maximum open cursors exceeded and the uncomplete table in the application.

Figure 2:ORA-01000 maximum open cursors exceeded

For my knowledge I went to the internet to find that what actually the Cursor is. The answer that I found on an Oracle website is

Similar to any application that uses Oracle Database as backend repository, Oracle Identity Manager runs several SQL statements. For every SQL statement execution in Oracle Database, certain area in the memory is allocated. Oracle PL/SQL allows you to name this area. This private SQL area is called context area or cursor. These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache. To keep a renegade session from filling up the library cache or clogging the CPU with millions of parse requests, the OPEN_CURSORS database parameter must be set to limit the cursors.

The OPEN_CURSORS parameter sets the maximum number of cursors that each session can have open, per session. For example, if the value of OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

It was ideally the Database Administrator to increase the value of the stated OPEN_CURSORS in order to resolve the issue but due to some reasons this did not happen. So I needed to muster up my courage and to resolve the issue by my own. The first step was to find the root cause of the error message.

Root cause:

Figure 3: Column Values Sum

As can be seen above, in the application the sum of all the values in QTY column are summed together and are displayed in the last row. This is done by using Compute Sum option of Oracle APEX in the column settings, as shown below.

Figure 4: Oracle APEX Compute Sum Option

It is noticed that when the Compute Sum option is active on the column with a large number of rows. It causes this error. It seems that the OPEN_CUSORS parameter is set to a certain value by the DBA(Database Administrator) and when the application want to make a select query on the big number of column values in order to sum them it exceeds this set values of OPEN_CURSORS.

As said, one way is to request the DBA to increase the values of OPEN_CURSORS parameter while the other way is to add the values of the column on the frontend using JavaScript.

Adding column values using JavaScript:

As the goal is to do a sum of a column values in a table named Startlist. Hence, a Static ID is assigned to the table, as shown in the below figure. This Static ID is necessary to identify the particular table in DOM using JS.

Figure 5: Assigning Static ID to a item in Oracle APEX

var $tr = $(‘table#report_Startlist_15032021 tbody tr’);

var rowCount = $tr.length;

In JS code, first of all the JQuery selector is used to identify the table by the id that is assigned to it and to retrieve all its row elements. The number of row elements are calculated and assigned to a variable rowCount.

sumVal = 0;

$tr.each(function(index, element) {

if(index > 0 && index < rowCount-1)

{

sumVal = sumVal + (parseFloat($tr[index].cells[4].innerText));

}});

In the third line of the code a variable sumVal is declared with a value 0. After that a loop is iterated on each first row element to the second last row element. In each iteration the innerText of the forth cell of the row is sum to the existing value of sumVal variable.

var row = document.createElement(“tr”); //Creating a row

var cell = document.createElement(“td”); // Creating a column cell

var cellText = document.createTextNode(“Report Total:”); // Creating a text

var boldText = document.createElement(“B”); // Making the test bold

boldText.appendChild(cellText)

cell.appendChild(boldText);

row.appendChild(cell)

In the ninth line of the JS code and onward as displayed above. A table row and its first cell is created respectively. A bold format text “Report Total:” is inserted into the cell and then the cell is appended to the row.

Immediately after this a for loop is used to create two empty consecutive cells after the first column. Now in total there are three cells in the row. The loop is simple as below,

for (var j = 1; j < 4; j++) {

var cell = document.createElement(“td”);

row.appendChild(cell)

}

As so far three row cells have been created. One with the text and two empty. Now a forth cell is created where the value of the sumVal variable is inserted. As calculated above sumVal contains the sum of all the values of the QTY column.

var cell = document.createElement(“td”);

var cellText = document.createTextNode(sumVal);

var boldText = document.createElement(“B”);

boldText.appendChild(cellText)

cell.appendChild(boldText); // Inserting into cell

row.appendChild(cell); // adding cell to a row

var $tbody = $(‘table#report_Startlist_15032021 tbody’);

$tbody[0].appendChild(row); //adding row to the bottom of the table

In the last four lines of the code, the cell is added to the row and then the row is appended to the bottom of the table.

Deploying JS in APEX:

In APEX though there is an option to run a JS code when Page Loads, as shown below.

Figure 6: Running JS in APEX

This option is not recommended as this makes the troubleshooting process difficult. I rather prefer to create dynamic action in this situation. Dynamic Actions are useful to assemble all the additional codes in an APEX application together. The condition for the dynamic action to run is shown in the below figures.

Figure 7: Dynamic Actions in Oracle APEX

It is shown above that the Dynamic Action, which is named as SUM_QTY, should trigger When the Page Load Event occurs. As the Page Load event occurs the condition is True and hence the Execute JavaScript Code runs.

In the figure below the settings of the Dynamic Action are defined. The JS code that is explained in the above heading should be put in the Code section. As the table on which the JS code is working is named as Startlist hence the in the region of the dynamic action the same name is used.

Figure 8: Event Actions in Oracle APEX

Result:

At the end, we get the result as shown below, the values in QTY column are added without using the Compute Sum option in APEX, rather the column values are added using JS.

Figure 9: Result of the JS manipulation

Application Developer | Internet Technologist |