Quick SQL in Oracle APEX is a wonderful tool that helps to generate SQL with shorthand syntax. This blog highlights one minor change to the option of generating primary keys via Triggers and GUIDs. I believe that a change to the GUID method will help prevent future errors.
I know what you might be thinking:
- “What problem?”
- “I have never run into any issues”
For my own part, I’ve never had any errors as a result of using Quick SQL-generated tables with the to_number GUID option. However, the potential issue is with the auto-generated primary key.
Example:
to_number(sys_guid(), ‘XXXXXXXXXXXXXXXX’);
When APEX generates the following code, it is problematic for a few reasons:
- The datatype used for sys_guid() should be a Raw or Varchar2
- There is a risk of data conversion errors
- Ask Tom said it isn’t a good idea (see references section below)
Resolutions
- Change to: RawToHex(sys_guid())
- Drop the GUID option altogether
- Change the default PK option to Via triggers and Sequence
Learn More
- You can learn more helpful tips like the ones above by visiting the Traust Consulting Blog.
References
- https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2570145300346198113
- https://asktom.oracle.com/pls/apex/asktom.search?tag=convert-sys-guid-to-number
Requirements
- APEX 5x or higher
Disclaimer:
We do not take responsibility for any unintended or unwanted consequences in your instance of Oracle, Oracle APEX, or related products as a result of reading our blogs or following our guides. Though the information is fully tested and generally safe to use, our lawyers really have a thing against admitting potential wrongdoing. If it makes you feel any better, one time they asked me to book them on a flight in first class. I did so, but I booked the furthest seat back in first class. That will definitely teach them some humility.
