Currently we have some custom integrations between our Oracle EBS (R12, database 12c) and other cloud-based systems such as travel system Concur and HR/Payroll system Ultipro to bring data into Oracle EBS from those system via Oracle Integration Cloud (OIC), The center pieces of OIC we are using are their cloud based connectors/adapters, we use specific OIC adapters for those systems to connect to those data services in the cloud and invoke their APIs to retrieve data, transfer to Oracle and process them. The response data from the REST API calls are JSON data.
As an alternative solution using OIC, we are also thinking of developing custom connectors/adapters for some integrations, Based on some Oracle cloud documents, it seems JSON components/packages in Oracle database are required to handle JSON data pulled from calling other system’s cloud-based APIs, and Oracle JSON components are part of APEX. I have no problem of invoking those REST APIs (such as Ultipro APIs) and get JSON response data using REST clients(such as Chrome native ARC, Insomnia, SOAP UI etc..), but I need some utilities such JSON packages inside Oracle database to handle those JSON data . My first question is whether we need to have APEX installed in our Oracle database or we can use Oracle-based JSON utilities (?) to handle JSON data from the REST APIs calls to achieve our goal ?
Another issue is about executing those REST API calls over cloud from within Oracle database or EBS environment, this is something I am not sure what we need to make it happen, do we need additional components such as ORDS (Oracle REST Data Services) for the cloud connections and calls? As I mentioned above I can call those APIs in cloud to get JSON data using those REST clients, but how to do similar calls from within Oracle database or EBS environment?
We need advices on these basic issues so that we can have basic understanding of technical infrastructure and roadmap to achieve our goals to integrate our Oracle EBS with other cloud-based data service systems, any advice and suggestions are greatly appreciated.
Thanks
Wei