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:

Quick SQL Code Generated

to_number(sys_guid(), ‘XXXXXXXXXXXXXXXX’);

When APEX generates the following code, it is problematic for a few reasons:

  1.  The datatype used for sys_guid() should be a Raw or Varchar2
  2.  There is a risk of data conversion errors
  3.  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

 

Quick SQL PK Options

Learn More

References

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.