On 25 march 2019 I gave a presentation on APEX world 2019. the subject of the presentation was the same as the title of this post. I will put some of the used code here for the who want to check it afterwards.
- you can find the presentation here.
The used code for the demo:
After you unable the schema for using ORDS. In SQLDeveloper you go REST Data Services and create your first module
- Name: OverSystem
- URI: /ordersystem/api/v01/
- Pattern: orders
use this query as GET method
decode(p.product_avail, ‘Y’,’Yes’,’N’,’No’) product_avail,
(select sum(quantity) from demo_order_items where product_id = p.product_id) units,
(select sum(quantity * p.list_price) from demo_order_items where product_id = p.product_id) sales,
(select count(o.customer_id) from demo_orders o, demo_order_items t where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id) customers,
(select max(o.order_timestamp) od from demo_orders o, demo_order_items i where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
from demo_product_info p
Save and the go to any test client to test your new create API. the URL is http://<host>:<port/ords/<yourshema>/ordersystem/api/v01/orders
for the sake of the demo I create an APEX app in another environment.
- create a web service call in the shared components of the created app with the details as in the below image.
- Create a process to be executed after header.
- type: web service call
- Store Result In “Collection”
- collection name : ARTICLE_MANAGEMENT
- create a report with and use the SQL Query like below
Run your page and you have an application using REST API.
Your done. if you have any question, please drop me a message.