ORDS a world of APEX and Database API

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

  1. Name: OverSystem
  2. URI: /ordersystem/api/v01/
  3. Pattern: orders

use this query as GET method

select p.product_id,
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,
p.product_id img,
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.

(C) Application 126 Shared Components Show All Web Service References MyOrders Edit REST web Reference RES REST Web Reference Name URL Proxy Override Basic Authentication HTTP Method Output Format Response XPath Response Namespace New Record Delimiter Parameter Delimiter REST Input Parameters Input Type No input parameters defined for this service HITP Request Headers http://1 lord Yes GET JSON in Name Value Pairs Name x Content-Type

  • 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

Code Editor - SQL Query 1 2 3 4 5 6 7 8 9 10 11 12 13 select jt.product_id, jt.product_name, jt.product_description, from apex _ collections, j son _ table (c tobØØ1, '$. items columns ( product_id varchar2 path '$. product_id' , product _ name varchar2 path '$. product _ name' , j t. category, jt. list _ price product _ description varchar2 path $.product_description' , category varchar2 path '$. category' , list_price varchar2 path '$. list _ price' ) as jt where collection name 'ARTICLE MANAGEMENT' ;

Run your page and you have an application using REST API.

Your done. if you have any question, please drop me a message.