QuickSQL Yes. SYS_GUID I want to like you but...
As much as I want to like the QuickSQL SYS_GUID option, it has a flaw that I can't get past.
5 min read
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
-- create tables 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 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.
-- return a sequence # + 11 random digits return wwv_seq.nextval || lpad(trunc(dbms_random.value(1, 99999)),5,'0') || to_char(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:
|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|
** 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.
When you have a form the error is [label] "must be a valid number." This is still not very helpful.
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.
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.