How to delete an ORDS Template or Handler…

…without having to re-create the entire Module.

Our story: you have some REST APIs. And some of them are no longer necessary.

You want to delete either…

• A TEMPLATE and all of it’s associated HANDLERs OR
• A specific HANDLER

In my demo environment, I have an 101 MODULE with 27 Templates, and in those 27 Templates, I have 33 Handlers.

I want to delete one of those templates (with their handlers), OR I want to delete just one of those Handlers.

To do that in the SQL Developer Web interface is quite easy.

Using the GUI

Select your template, click the DELETE Button.

Select your handler, click the DELETE Button.

Using REST APIs

As you’re clicking those buttons, you could use your browser developer tools, and see that we do have REST APIs for deleting ORDS resources, including templates and handlers.

Using SQL Scripts

That’s what you’re here for, I think. If you want to have a script that can be applied to a system to change your REST APIs, then you have three options.

• Use a SQLcl Liquibase changeLog
• Re-create your module only having what you want
• ORDS_METADATA.ORDS_SERVICES PL/SQL API to DELETE the Handler or Template

This blog post will delve into ORDS_SERVICES PL/SQL API.

But first, let’s create a TEMPLATE with 3 Handlers, and have some ‘code’ to play with. In other words, we need some code that’s safe to delete.

Sample Code/API…to later DELETE

-- Generated by ORDS REST Data Services 23.2.3.r2421937
-- Schema: YOUR_SCHEMA  Date: Mon Oct 09 03:38:31 2023 
--

BEGIN
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'delete.later',
      p_base_path      => '/safe_to_delete/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'we''re going to build this module up, just to delete it later');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'delete.later',
      p_pattern        => 'template2',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'delete.later',
      p_pattern        => 'template2',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'select 2');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'delete.later',
      p_pattern        => 'template1',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'delete.later',
      p_pattern        => 'template1',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'select 1');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'delete.later',
      p_pattern        => 'template1',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'BEGIN
    null;
end;');

    
        
COMMIT;

END;

Run this in your API schema, and it will look like this –

ORDS_SERVICES PL/SQL API

Note: This package isn’t documented in the ORDS Docs. I’m getting that fixed, and you have my apologies for that oversight.

Schema: ORDS_METADATA
Package: ORDS_SERVICES
Procedure: DELETE_HANDLER, DELETE_TEMPLATE

We need the TEMPLATE & HANDLER ID, but probs also MODULE ID

We’ll use SQL for that.

First our MODULE –

I know the module name, so I can query for it’s ID from the USER_ORDS_MODULES view.

SELECT *
  FROM USER_ORDS_MODULES
 WHERE NAME = 'delete.later';

Let’s delete a HANDLER.

To delete a handler, I need it’s handler ID.

Here’s some SQL to get all the handlers from our module 15005.

SELECT A.ID,
       B.URI_TEMPLATE,
       A.METHOD,
       A.SOURCE,
       A.COMMENTS
  FROM
       USER_ORDS_HANDLERS A
        INNER JOIN USER_ORDS_TEMPLATES B ON A.TEMPLATE_ID = B.ID
WHERE
    B.MODULE_ID = 15005;

BEGIN
    ords_services.delete_handler(p_id => 15008);
END;
/

Ok, let’s execute that and see what happens to our POST handler on template1.

Let’s delete a template, and it’s handlers.

Without a template, there is no handler.

We need the template ID.

SELECT B.URI_TEMPLATE,
       B.ID TEMPLATE_ID,
       B.COMMENTS
  FROM USER_ORDS_TEMPLATES B
 WHERE B.MODULE_ID = 15005;

Ok, time to run the DELETE_TEMPLATE() procedure.

BEGIN
    ords_services.DELETE_TEMPLATE(p_id => 15009);
END;
/

Now we’ll query the templates list back out, and lo and behold, template2 is gone.

Let’s get serious for a moment

You should be source controlling your REST APIs, your SQL & PL/SQL code. You should be using an automated process to publish changes in your applications to your databases.

That can done with SQL scripts, using Liquibase, or just our PL/SQL APIs. Running these scripts ad hoc in production is NOT how you should be doing installs, upgrades, or patching your REST APIs.

Humans with keys on keyboards is where operator error enters the room, and where lack of properly tested backups creates amazing conference presentation stories later for the audience schadenfreude.