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:

• Clipartmax - For REST Image
• Oracle REST Data Services PL/SQL Package Reference