…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.
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.