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.
2 Comments
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>
That’s by design. CREATE_SERVICE() is provided as a convenience feature.