Getting into ORDS

February 29, 2020Posted inAPEX, ORDS

Firstly we need to enable ORDS.

Base url : <SERVER_URL>:<SERVER_PORT>/ords/

To hide the schema name from the API endpoint we put any alias in the url mapping pattern.

Copy Code
BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => '<SCHEMA_NAME>',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => '<ANY_ALIAS_INPLACE_OF_SCHEMA_NAME>',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Till now our end is up to the below URL, and we can start building APIs.

<SERVER_URL>:SERVER_PORT>/ords/<ANY_ALIAS_INPLACE_OF_SCHEMA_NAME>

There are two ways to build the APIs
Quick Build
Manual Build

1. Quick Build

Copy Code
BEGIN
  ORDS.define_service(
    p_module_name    => 'Quick_API_MODULE',
    p_base_path      => 'v1/',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  COMMIT;
END;
/

Check in DB and Restful APIs.

Modules

SELECT id, name, uri_prefix
FROM   user_ords_modules
where name = 'Quick_API_MODULE';

Templates

SELECT id, module_id, uri_template
FROM   user_ords_templates
where module_id= 822768;

Handlers

SELECT id, template_id, source_type, method, source
FROM   user_ords_handlers where
template_id= 822769;

How url works

Base ORDS URL : <SERVER_URL>:SERVER_PORT>/ords/
Schema (alias): <SERVER_URL>:SERVER_PORT>/ords/<ANY_ALIAS_INPLACE_OF_SCHEMA_NAME>

Module        : <SERVER_URL>:SERVER_PORT>/ords/<ANY_ALIAS_INPLACE_OF_SCHEMA_NAME>/Quick_API_MODULE

Template      : <SERVER_URL>:SERVER_PORT>/ords/<ANY_ALIAS_INPLACE_OF_SCHEMA_NAME>/Quick_API_MODUL/employees/

2. Manual Build

Copy Code

-- Create module
  ORDS.define_module(
    p_module_name    => 'TEST',
    p_base_path      => 'TEST',
    p_items_per_page => 0);
  
-- Create tamplate
  ORDS.define_template(
   p_module_name    => 'TEST',
   p_pattern        => 'employees/');

-- Create handler
  ORDS.define_handler(
    p_module_name    => 'TEST',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);
    
  COMMIT;
END;
/
Tags: