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
