REST your EBS data with Oracle RESTful Data Services (ORDS)
Posted by Abishek Kathiresan
Introduction
Here we share that how to REST your EBS data with Oracle RESTful Data Services (ORDS)
APEX and ORDS
- APEX uses Oracle Rest Data Services (ORDS) another built-in feature of the Oracle Database, to create Web services.
- Like APEX, ORDS uses PL/SQL programming language, the same language your EBS developers have been using all along.
- Using ORDS and APEX, your developers can help generate safe, effective REST-enabled Web services for your EBS system.
Lets get into installation
1. Apex Installation:
Apex software version:apex_4.2.4.zip
Create a new tablespace to act as the default tablespace for APEX.
CREATE TABLESPACE apex DATAFILE ‘/u01/app/oracle/oradata/db11g/apex01.dbf’
SIZE 100M AUTOEXTEND ON NEXT 1M;
cd /u01/apex
cp apex_4.2.4.zip /u01/apex
unzip apex_4.2.4.zip
cd apex
connect sqlplus
@apexins.sql APEX APEX TEMP /i/
Once complete, change the admin password by running the “apxchpwd.sql” scripts as the SYS user.
sql> @apxchpwd.sql
Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users by running the “apex_rest_config.sql” script.
SQL> CONN / AS SYSDBA
SQL> @apex_rest_config.sql
2. ORDS Installation:
ords.2.0.10.289.08.09.zip
unzip ords.2.0.10.289.08.09.zip
java -jar ords.war configdir /u01/ORDS
java -jar ords.war
[oracle@dspl1225 ORDS]$ java -jar ords.war
Feb 27, 2018 5:01:31 AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /u01/ORDS/ords
Enter the name of the database server [localhost]:129.144.180.26
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:EBSDB
Enter the database user name [APEX_PUBLIC_USER]:oracle$123
Enter the database password for oracle$123:[oracle@dspl1225 ORDS]$
[oracle@dspl1225 ORDS]$ java -jar ords.war
Feb 27, 2018 5:04:43 AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /u01/ORDS/ords
Enter the name of the database server [localhost]:129.144.180.26
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:EBSDB
Enter the database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:1
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Feb 27, 2018 5:05:35 AM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Static Image location need to give Apex images: /u01/apex/images
nohup java -jar ords.war &
Login to Apex Admin Console
1.Create Workspace and assign default schema as apps.
2.Login to workspace as you created .
Ex:
Workspace Name :Mobile
Username : admin
Password : XXXXXXXXX
3. Creating the Web Service
Navigation: SQL Workshop > RESTful Services > Create
Source
SELECT hca.account_number,
hp.party_name customer_name,
hca.cust_account_id,
hca.object_version_number cust_object_version_number,
TO_CHAR(hca.creation_date,’mm/dd/yyyy’) creation_date,
hca.status,
hca.customer_type,
hca.customer_class_code,
hca.sales_channel_code,
hp.tax_reference,
hp.address1,
hp.address2,
hp.city,
hp.postal_code,
hp.state,
hp.object_version_number party_object_version_number
FROM ar.hz_cust_accounts hca, ar.hz_parties hp WHERE hp.party_id = hca.party_id AND hca.account_number = :custnum
Set Bind Variables
Click TEST