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