A note on using a numerical sys_guid with JavaScript

 

I've been working on some tables that use the built-in function sys_guid(), converted into a number. I've faced this same problem a couple of times now, so thought I'd post this to try and drill it in.

The spec in the table looks like this (note the id column).

create table emp2 (
  id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
  ename varchar2(255),
  sal number
);

-- populate data with that in the pre-existing emp table
insert into emp2 (ename, sal)
select ename, sal
from emp;

Next, I add a classic report to my page, which includes a button to give the employee a raise. My logic will be to submit the page and to leverage the `apex.page.submit` function.

 

As you can see, the length of the number is quite long.

OK, so on a new column I add to my report, I specify the the URL target as.

javascript:apex.page.submit({request:"GIVE_RAISE",set:{"P1_ID":#ID#} });

And when I inspect the button target, this translates into the (formatted) call

apex.page.submit({
  request:"GIVE_RAISE",
  set:{
    "P1_ID":268793843538709172209236997983336433537
  }
});

OK, all looks good so far.

Next I will add to an on-submit process with the following SQL statement:

update emp2
set sal = sal*1.1
where id = :P1_ID;

However, when I click the button, there was no change. What's going on?
I take a look at my session state, and I don't see this very long number, but rather I see "2.6879384353870916e+38". OK, so somewhere in the process it's converted my number into scientific notation. And by somewhere, this is happening with JavaScript - go ahead and type in that number to your console and you will see in the output it is converted to scientific notation.

So, my next inclination was, in my update statement, to wrap the bind variable in a call to `to_number` - to no avail. And indeed if I run a simple query in SQL commands, we can see there is a vast difference. Look at all those 0's!


In the end, there is a simple solution. We want JavaScript to treat the value as a string rather than a number, so we just have to wrap the value in quotes. With this simple change, you will be able to give your employees a raise - that will make them very happy.

javascript:apex.page.submit({request:"GIVE_RAISE",set:{"P1_ID":"#ID#"},showWait:true}); 

Credits

Opening splash photo by Markus Winkler on Unsplash

Popular posts from this blog

Installing Oracle Instant Client on Ubuntu

Report row buttons firing a dynamic action

APEX 5 blob column uploads