Skip to main content

REST Data Services in Oracle APEX: A PL/SQL API for Creating Them

    

Create REST Data Services in Oracle APEX Using the Oracle REST Data Services PL/SQL API

The objective of this blog post is to show you how to create REST Data Services in Oracle APEX using a PL/SQL API (Oracle REST Data Services: ORDS).

Oracle APEX provides a sample REST Data Services that can be accessed from Sample APEX Application and it can be installed from Gallery.

RESTful Data Services can be created with Oracle APEX. The same can be done using Oracle REST Data Services PL/SQL API.

To navigate to RESTful Data Services, SQL Workshop --> RESTful Services.

This sample REST Data Service (Screenshot) has module, templates, handlers and parameters.

Module

oracle.example.emp

Templates

1) empinfo/ 

2) employees/:id

Handlers 

1) The empinfo/ template has a GET handler

2) The employeees/:id has GET and PUT handler

Parameters:

The employees/:id template has a PUT handler and it has two parameters

It's already been developed by Oracle APEX and now we will build it using ORDS PL/SQL API.

Steps to follow,

1) Enable Schema (ORDS.ENABLE_SCHEMA)

ENABLE_SCHEMA enables Oracle REST Data Services to access the named schema.

2) Define Module (ORDS.DEFINE_MODULE)

DEFINE_MODULE defines a resource module. If the module already exists, then the module and any existing templates will be replaced by this definition; otherwise, a new module is created.

3) Define Template (ORDS.DEFINE_TEMPLATE)

DEFINE_TEMPLATE defines a resource template. If the template already exists, then the template and any existing handlers will be replaced by this definition; otherwise, a new template is created.

4) Define Handler (ORDS.DEFINE_HANDLER)

DEFINE_HANDLER defines a module handler. If the handler already exists, then the handler and any existing handlers will be replaced by this definition; otherwise, a new handler is created.

5) Define Parameter (ORDS.DEFINE_PARAMETER)

DEFINE_PARAMETER defines a module handler parameter. If the parameter already exists, then the parameter will be replaced by this definition; otherwise, a new parameter is created.

Query

In Script Editor (Navigation: SQL Worksop --> SQL Scripts --> Create), you can copy and paste the following script for compilation. Click "Run" to compile the script. 


It will create a RESTful Data Service for you.

BEGIN -- Enable Schema (Schema: FAMPROP) ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'FAMPROP', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'famprop', p_auto_rest_auth => FALSE); -- Define Module (Module: oracle.example.emp) ORDS.DEFINE_MODULE( p_module_name => 'oracle.example.emp', p_base_path => '/emp/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); -- Define Template (Template: empinfo/) ORDS.DEFINE_TEMPLATE( p_module_name => 'oracle.example.emp', p_pattern => 'empinfo/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); -- Define Handler (Method: GET) ORDS.DEFINE_HANDLER( p_module_name => 'oracle.example.emp', p_pattern => 'empinfo/', p_method => 'GET', p_source_type => 'json/query', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'select * from emp'); -- Define Template (Template: employees/:id) ORDS.DEFINE_TEMPLATE( p_module_name => 'oracle.example.emp', p_pattern => 'employees/:id', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); -- Define Handler (Method: GET) ORDS.DEFINE_HANDLER( p_module_name => 'oracle.example.emp', p_pattern => 'employees/:id', p_method => 'GET', p_source_type => 'json/query;type=single', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'select * from emp where empno = :id'); -- Define Handler (Method: PUT) ORDS.DEFINE_HANDLER( p_module_name => 'oracle.example.emp', p_pattern => 'employees/:id', p_method => 'PUT', p_source_type => 'plsql/block', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'begin update emp set ename = :ename, job = :job,
hiredate = :hiredate,
mgr = :mgr,
sal = :sal,
comm = :comm,
deptno = :deptno where empno = :id; :status := 200; :location := :id; exception when others then :status := 400; sys.htp.p( sys.htf.escape_sc(sqlerrm) ); end;'); -- Define Paramter (Parameter: ID) ORDS.DEFINE_PARAMETER( p_module_name => 'oracle.example.emp', p_pattern => 'employees/:id', p_method => 'PUT', p_name => 'ID', p_bind_variable_name => 'id', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); -- Define Paramter (Parameter: X-APEX-FORWARD) ORDS.DEFINE_PARAMETER( p_module_name => 'oracle.example.emp', p_pattern => 'employees/:id', p_method => 'PUT', p_name => 'X-APEX-FORWARD', p_bind_variable_name => 'location', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'OUT', p_comments => NULL); -- Define Paramter (Parameter: X-APEX-STATUS-CODE) ORDS.DEFINE_PARAMETER( p_module_name => 'oracle.example.emp', p_pattern => 'employees/:id', p_method => 'PUT', p_name => 'X-APEX-STATUS-CODE', p_bind_variable_name => 'status', p_source_type => 'HEADER', p_param_type => 'INT', p_access_method => 'OUT', p_comments => NULL); COMMIT; END; /

Output

https://apex.oracle.com/pls/apex/famprop/emp/empinfo/ (GET)

https://apex.oracle.com/pls/apex/famprop/emp/employees/:id (GET)

https://apex.oracle.com/pls/apex/famprop/emp/employees/:id (PUT)

Generate Swagger Doc

You can generate Swagger Document for the REST Data Service that you build.


Generated Swagger Doc: https://editor.swagger.io/?url=https://apex.oracle.com/pls/apex/famprop/open-api-catalog/emp/

That's it. Happy APEXing!

References/Credits:

Comments

Post a Comment

Popular posts from this blog

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  
5 comments
Read more

Razorpay Payment Gateway Integration with Oracle APEX

     The objective of this blog post is to show you how to integrate Razorpay Payment Gateway with Oracle APEX. What is Razorpay? Razorpay is the only converged payments solution company in India that allows your business to accept, process, and disburse payments via its product suite . With Razorpay, you have access to all payment modes, including credit and debit cards, UPI, and popular mobile wallets. Razorpay Payment Gateway Integration with Oracle APEX 20.x Below are the steps involved to get this process done. 1) Create a Razorpay Account 2) Get Razorpay API Keys in Test Mode 3) Get and Invoke Razorpay Orders API with Postman 4) Create a required Database Objects 5) Create a Sample Oracle APEX Application 6) Invoke Razorpay Payment Gateway using APEX_WEB_SERVICE API (Payment Process) 7) Verify Payment Status 8) Setup REST End Point to Receive Razorpay Webhook Response 9) Setup Razorpay Webhook 10) Test Card I) Create a  Razorpay  Account II) Get Razorpay API Keys in Test Mode AP
2 comments
Read more

Tool-tip with Image in Oracle APEX Report

I have built this  tool-tip with image feature  for one of my customer and they were very happy about it, So here I am giving an elegant way for adding tool-tip with image in Oracle APEX Report  using Jquery. Tool-tip:  The tool-tip, also known as info tip or hint , is a common graphical user interface element in which, when hovering over a screen element or component, a text box displays information about that element (such as a description of a button's function, or what an abbreviation stands for). The tool tip is displayed continuously as long as the user hovers over the element. Step 1:  Create table and populate some sample records. CREATE   TABLE  fxgn_documents  ( document_id  NUMBER   PRIMARY   KEY ,                                sequence_no  NUMBER ,                                file_name    VARCHAR2 ( 240 ),                              file_url     VARCHAR2 ( 4000 ),                              updated_by  VARCHAR2 ( 240 ),                                updated_on 
3 comments
Read more