Note: I have updated this blog post. The original text remains, but I have added highlighted text with additional information and improved recommendations.
Let's start with, the method I prefer has the same issue. Is my method any better? I still think so, but for other reasons. Please read the paragraph with ** The paragraph below is important!!! ** Please also read the last paragraph. Maybe it should be first, but, please read it.
QuickSQL is great and I appreciate the SYS_GUID Primary Key Population Method for its elegance. With SYS_GUID, this
my_table
my_column
produces
create table my_table (
id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
constraint my_table_id_pk primary key,
my_column varchar2(4000 char)
)
;
There are no triggers and everything works great. It does not create easily guessable primary key values (though depending on DB version and OS, they may be easier than I would like). And...again, no triggers. That's great.
The Flaw(s)
The problem is that the number that it generates is so large that it causes javascript errors if you reference it as a number. Generally speaking, the primary key will be hidden on a page and you won't run into an issue. But now and then you may want to have it as an item on a page. In this case, it will automatically be an item of type "Number Field."
The number being so large causes an issue in ORDS REST services as well. If you define an ORDS REST module, template and handler with an integer parameter, the SYS_GUID is going to fail because the number is longer than what is accepted as an integer by ORDS (or probably SQL or PL/SQL).
Basically, SYS_GUID is just too long.
As it turns out, just about any method that provides a "GUID" that is not easily guessable is going to be too long. In the Oracle Database (SQL and PL/SQL) the largest INTEGER is 2147483647. In Javascript it is 9007199254740991. While those are both pretty long numbers, you will likely exceed both before long if you use any other method (that does not create an easily guessable value). For example, the APEX method of creating a GUID is (more or less)
return wwv_seq.nextval
|| lpad(trunc(sys.dbms_random.value(1, 99999)),5,'0')
|| to_char(sys.systimestamp,'ff6');
When it started, this method generated numbers like 397128060537. The sequence caches 1000 values, so it goes up pretty quickly. Currently on apex.oracle.com the sequence is at 73263631. This means that apex.oracle.com is currently creating primary key values greater than 7326363152290593809. Let's take a look at that:
| Value | Comments |
| Oracle Database Max Integer Value | 2147483647 | not very big |
| Oracle Database Max Number Value | big, really big | I don't know, but it's big |
| Javascript Max Value | 9007199254740991 | seems big |
| apex.oracle.com current value as of the date of this blog post | 7326363152290593809 | On a new APEX install this value is about 397128060537 and that does not break Javascript. As it grows, though, it will reach a size that does break javascript--which is the case on apex.oracle.com today. |
| |
** The paragraph below is important!!! **
This means that you have to take all of the precautions below regardless of how you generate your GUID. If you don't, you are likely to have the system work for weeks, months, or even years, before things suddenly start breaking.
Interactive Grid
When you attempt to save the form or interactive grid it will create an error. In the javascript console you will see "Value exceeds number precision" followed by the ID. On the screen, for an Interactive Grid, you will see "Correct errors before saving." However there won't be any real indication what the error is.
When you have a form the error is [label] "must be a valid number." This is still not very helpful.
The "Solution"
All you have to do to fix this is change the item type from "Number Field" to "Text." Sure, it's easy, once you understand the issue, but I just don't like it. But, you need to do it this way. It's really the only safe method.
What to do?
I'm still wrestling with this. It's a rare problem, so maybe I just address it as it comes up. Should I just stop using SYS_GUID and use a trigger instead? I do like the APEX method, wwv_flow_id.next_val. (See https://apex.oracle.com/ideas/FR-2821 for more details on that.)
I wish I could set the column default to my own function.
At this point, I create my own sequence (my_sequence) and then a function that I use for every primary key (or “almost every” primary key):
return my_sequence.nextval
|| lpad(trunc(dbms_random.value(1, 99999)),5,'0')
|| to_char(systimestamp,'ff6');
The sequence (my_sequence) should cache a reasonable number of values, typically 100. The same function, and hence the same sequence, is used for every table.
What are your thoughts on this?
So, in the end, the thing to do is to handle all of this assuming that you will eventually exceed the safe integer values of whatever language you are using. Even though your value is a number, you need to treat it as a string (or whatever the appropriate datatype is for your language).
Is wwv_flow_id.next_val any better than SYS_GUID?
I still think wwv_flow_id.next_val (or your own function that does the same thing) is better than SYS_GUID. I don't know exactly why, but I like it. I have a few reasons.
It is ever-increasing.
I have control over it.
Though large, it is smaller than SYS_GUID.
I am confident that if I add 2 of them the result will also be unique. This is how APEX works with offsets. This may also be true of SYS_GUID, but I don't know for sure.
SYS_GUID has an advantage
With SYS_GUID, you are going to run into the issues that this blog post addresses right away. With the wwv_flow_id.next_val method it will show up weeks, months, or years after you have it running in production. Maybe that should be the message of this blog post...buried all the way at the bottom.
