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.
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)
- Change to: RawToHex(sys_guid())
- Drop the GUID option altogether
- Change the default PK option to Via triggers and Sequence
- You can learn more helpful tips like the ones above by visiting the Traust Consulting Blog.
- APEX 5x or higher
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.