Posted on

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,
p.product_name,
p.product_description,
p.category,
decode(p.product_avail, ‘Y’,’Yes’,’N’,’No’) product_avail,
p.list_price,
(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,
tags
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 72.18.0.1:32713/0rds/apexw0rld/0rdersystem/api/v01 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.

Posted on

Installing APEX 18.1 in a Docker container

I have APEX 5.1 installed in a docker container, when APEX 18.1 was out I thought about creating a new image, but I have customer applications running on APEX 5.1. Then I saw the idea of Dimitri Gielis about cloning the database and installing APEX on the new environment.  What you will be needing is your existing Oracle 12c image (you can download it from my docker hub).

  1. Oracle 12 image
  2. ORDS 18.1

Clone the database just like Dimitri Gielis is suggesting in his blog.

Create ORDS image from Oracle “docker-images-master” (you can download it from Git)

— copy apex18.1 to docker
docker cp apex_181..zip oracle12c:/temp/apex_18.1.zip

— shell to enter the container
docker exec -it oracle bash -c “source /home/oracle/.bashrc; bash”

— install APEX inside the docker container (login as sysdba)

@apexins SYSAUX SYSAUX TEMP /i/

— Run the apex_rest_config command
@apex_rest_config.sql

— before creating the ORDS container make sure that the needed users have no password expired.

alter user APEX_PUBLIC_USER identified by oracle;
alter user APEX_LISTENER identified by oracle;
alter user APEX_REST identified by oracle;

once APEX installation is done, create a new ORDS container from the image.

— create ords container
docker run -t -i \
–name ords \
–network=oracle_network \
-e DB_HOSTNAME=oracle \
-e DB_PORT=1521 \
-e DB_SERVICENAME=ORCLPDB1 \
-e APEX_PUBLIC_USER_PASS=oracle \
-e APEX_LISTENER_PASS=oracle \
-e APEX_REST_PASS=oracle \
-e ORDS_PASS=oracle \
-e SYS_PASS=oracle \
–volume /Volumes/HDD/docker/apex/18.1/images:/usr/local/tomcat/webapps/i \
-p 8081:8080 \
ords18:3.0.12

I have the old ORDS3 running on 8080, for ORDS18 I’m using 8081.

You are done, You have both APEX versions running side by side. now you can connect to APEX18.1 on localhost:8081/ords

If you have any question, just drop me a message.