Mostly Making oracle easy, mostly:
Search for:
In ORDS

Create a ORDS REST API in a single PL/SQL call

2 Mins Read

Installed ORDS and set it up for your Oracle Database? Need to quickly test if you’re able to create a REST API for your Oracle schema? Here’s a single PL/SQL call that will in one step:

  • create a module
  • define a template
  • publish a handler
  • …and all commented!

This will be as short post, you can literally copy and paste this into your IDE, run it on your REST Enabled Schema, and you should have an API to exercise.

When you’re done testing or debugging with this Module, you’ll want to either DELETE or deactivate the service via:

  • PROCEDURE delete_module
  • P_STATUS => ‘NOT_PUBLISHED’

The PL/SQL Code

There’s a public synonym point to ORDS_METADATA.ORDS called ‘ORDS’, but this block will call it specifically. 

begin
 ORDS_METADATA.ORDS.create_service(
      p_module_name        => 'day_zero',
      p_base_path          => 'day0',
      p_pattern            => 'greeting/',
      p_method             => 'GET',
      p_source_type        => 'json/item',
      p_source             => 'select ''Hello!'' from dual',
      p_status             => 'PUBLISHED',    
      p_module_comments    => 'see if we can create a module',
      p_template_comments  => 'see if we can create a template',
      p_handler_comments   => 'see if we can say Hello!');
 COMMIT;
END;
/

Run this block of code ‘as a script,’ if you’re using SQL Developer, then simply make the GET request. Since it’s a HTTP GET, you can test the API with your favorite browser.

Is there anybody out there?

Looking for the Docs?

Something I really enjoy about our ORDS PL/SQL APIs is how well documented they are, both in the Docs and in their PL/SQL SPECs.

So let’s take a look at ORDS_METADATA.ORDS. Our PROCEDURE in question is right at the very top.

You’ll see ‘create_service’ right above this, but it’s deprecated, and says so in the comments.

Double-checking that it’s really there

If we go to the REST Workshop or to the REST Data Services Item in SQL Developer for our schema, we’ll see our Module, Template, and Handler.

We’re not just seeing things, so that’s good.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

2 Comments

  1. Rajeshwaran Jeyabal Reply

    was exporting with the latest version of SQL Dev, but it still doesn’t make use of “ords.create_service”
    instead, it still goes with enable_schema/define_module/template/handlers etc.
    the same with latest version of SQLCL too. Any reason ?

    — Generated by Oracle SQL Developer REST Data Services 22.2.1.234.1810
    — Exported REST Definitions from ORDS Schema Version 22.3.1.r2901647
    — Schema: DEMO Date: Tue Jan 10 10:38:51 IST 2023
    —
    BEGIN
    ORDS.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => ‘DEMO’,
    p_url_mapping_type => ‘BASE_PATH’,
    p_url_mapping_pattern => ‘demo’,
    p_auto_rest_auth => FALSE);

    ORDS.DEFINE_MODULE(
    p_module_name => ‘DEPT_DEMO’,
    p_base_path => ‘/dd/’,
    p_items_per_page => 25,
    p_status => ‘PUBLISHED’,
    p_comments => NULL);
    ORDS.DEFINE_TEMPLATE(
    p_module_name => ‘DEPT_DEMO’,
    p_pattern => ‘deptdemo’,
    p_priority => 0,
    p_etag_type => ‘HASH’,
    p_etag_query => NULL,
    p_comments => NULL);
    ORDS.DEFINE_HANDLER(
    p_module_name => ‘DEPT_DEMO’,
    p_pattern => ‘deptdemo’,
    p_method => ‘GET’,
    p_source_type => ‘resource/lob’,
    p_items_per_page => 25,
    p_mimes_allowed => ”,
    p_comments => NULL,
    p_source =>
    ‘select ”image/png”,y from the_blob’
    );

    COMMIT;
    END;
    /

    demo@PDB1> version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.4.0.0 build: 22.4.0.342.1215
    demo@PDB1>
    demo@PDB1> REST export DEPT_DEMO
    — Generated by SQLcl REST Data Services 22.4.0.0
    — Exported REST Definitions from ORDS Schema Version 22.3.1.r2901647
    — Schema: DEMO Date: Tue Jan 10 10:42:13 IST 2023
    —
    BEGIN
    ORDS.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => ‘DEMO’,
    p_url_mapping_type => ‘BASE_PATH’,
    p_url_mapping_pattern => ‘demo’,
    p_auto_rest_auth => FALSE);

    ORDS.DEFINE_MODULE(
    p_module_name => ‘DEPT_DEMO’,
    p_base_path => ‘/dd/’,
    p_items_per_page => 25,
    p_status => ‘PUBLISHED’,
    p_comments => NULL);
    ORDS.DEFINE_TEMPLATE(
    p_module_name => ‘DEPT_DEMO’,
    p_pattern => ‘deptdemo’,
    p_priority => 0,
    p_etag_type => ‘HASH’,
    p_etag_query => NULL,
    p_comments => NULL);
    ORDS.DEFINE_HANDLER(
    p_module_name => ‘DEPT_DEMO’,
    p_pattern => ‘deptdemo’,
    p_method => ‘GET’,
    p_source_type => ‘resource/lob’,
    p_items_per_page => 25,
    p_mimes_allowed => ”,
    p_comments => NULL,
    p_source =>
    ‘select ”image/png”,y from the_blob’
    );

    COMMIT;
    END;
    demo@PDB1>

Write A Comment