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

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,  

Displaying Multiple Columns in Oracle APEX Popup LOV

Fig 1: Multiple Columns Popup LOV Here I am giving an elegant way for displaying multiple columns in  Oracle APEX  Popup LOV. This has been inspired by Oracle E-Business Suite.  List of Values:  A List of Values is a static or dynamic definition used to display a specific type of page item, such as popup lists of values, a select list, a check box, a radio group, or multiple select lists. Step 1: Keep your LOV query ready. SELECT  emp . ename employee ,        emp . empno ,        dept . dname department ,        emp . job ,        mgr . ename  manager ,         to_char ( emp . hiredate , 'DD-Mon-RRRR' )  hire_date ,         to_char (( nvl ( emp . sal , 0 )  +  nvl ( emp . comm , 0 )),   '999G999G999G999G990D00' )                                            AS  total_salary    FROM  eba_demo_chart_emp emp ,        eba_demo_chart_emp mgr ,        eba_demo_chart_dept dept   WHERE   1           =  1     AND  emp . mgr    = mgr . empno     AND  emp . deptno = dept . deptno

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