Hurray! I finally REST enabled Interactive Grid for Oracle APEX. It took a week to realise Interactive Grid cannot use REST data source fully. You will need to custom PLSQL for DML processing to save Interactive Grid data. Thanks to this helpful blog for giving a good example on how to do it. The coding is much easier than direct ORDS codes as APEX provide utility API to exec the REST data sources. You can refer to the blog for implementation but I will share the pitfalls to look out for.
Turn On Debug
The most important steps is to turn on APEX debug and enable level 8 debugging. You must also add debug log to your procedure and PLSQL code. Level 8 give you detailed logging. You can always switch back to level 4 when you find the error. Debug will show the JSON payload when you exec the REST service. Do copy this payload to Postman to test that it is formed correctly. A common mistake is the typo and missing quotes in the JSON format.
t_log_level := 8; — Application Express engine: messages when procedures/functions are entered
REST JSON have no data types formatting during http transmission. Thus, you need to format and validate your data before you send and receive at target. If you are using date types, you will often hit character to number conversion error. Be sure to format your date to the desired date mask “DD-MM-YYYY H24:MM:SS” and convert to varchar. Do be careful of null values and put in validation checks to prevent this error. You should also validate for unique primary key. REST status code is notoriously not helpful. Do add loggers at TARGET system to check where the errors occurs.
It will take a few version upgrade before Interactive Grid can be REST out of the box. In the meantime, you just have to follow this custom PLSQL method for REST connectivity between APEX and target system.