codemonth.dk

One project every month - making stuff better ...

Take a random REST

I needed to test ORDS for some webservice related projects at work. So I decided to build a rest interface for all the different random data functions in the RANDOM_NINJA package to test out, just how easy it is.

First order is to setup ORDS. Like always Tim over at oracle-base have a couple of really good articles on installing and setting up ORDS itself. So no need for me to duplicate that.

After setting up ORDS, and enabling my schema for rest services like in this oracle-base article, it is time to enable the functions in the different data domain packages. I decided to keep the URL scheme simple, so that the path would be /[package_name]/[function_name]/ for any of the functions that are part of the RANDOM_NINJA package.

I'm always a little bit more happy when I can buy a domain. So I decided to buy one for this exercise. Also because I have some ideas for something a bit more useful then just these services, but that I will leave for a later blog post.

So the URL for all the random_ninja rest services will be:


http://api.binubuo.com/[package_name]/[function_name]/

For those who are curios about the name, it means "composed" in Tagalog (Language spoken on Philippine Islands), so some correlation to random data :)

There is quite the number of functions available in the RANDOM_NINJA package. So what would be a quick way to expose all of these functions, as rest services? For that I decided to create a "publish" service:


create or replace procedure ords_enable_block (
  ords_group_name         in          varchar2
  , ords_block_name       in          varchar2
  , ords_generator        in          varchar2
)

as

begin

  ords.define_template(
    p_module_name           => 'binuapi.random'
    , p_pattern             => ords_group_name || '/' || ords_block_name || '/'
  );

  ords.define_handler (
    p_module_name           => 'binuapi.random'
    , p_pattern             => ords_group_name || '/' || ords_block_name || '/'
    , p_method              => 'GET'
    , p_source_type         => ords.source_type_plsql
    , p_source              => 'begin apex_json.open_object; apex_json.write(''' || ords_block_name || ''', '|| ords_generator ||'); apex_json.close_object; end;'
    , p_items_per_page      => 0
  );

  commit;

end;
/

Once this procedure is compiled, I can expose all the functions with the following call for each function. So if I wanted to expose person_random.r_firstname, I would call:


begin
  ords_enable_block('person_random', 'r_firstname', 'person_random.r_firstname');
end;
/

This procedure will then expose that function at the following endpoint:


http://api.binubuo.com/person_random/r_firstname

So with that ready, I can now very quickly create endpoints for all the functions using the following sql:


select 'begin ords_enable_block('''|| lower(object_name) || ''', '''|| lower(procedure_name) ||''', '''|| lower(object_name) || '.'|| lower(procedure_name) ||'''); end;'
from all_procedures
where owner = '[owner_of_random_ninja]'
and procedure_name is not null
order by object_name;

This select gives me all the commands to expose the functions from all the packages. The one issue with this procedure currently is that it cannot handle any of the input parameters for the functions. I wanted to write that functionality, but got the following response from Oracle:

So if Oracle REST can be quick out the door with a solution to that, I wont build it. But I am a little bit impatient, because I have other plans ....

The following is a list of all the webservice endpoints for RANDOM_NINJA:

Tagged in : DBMS_RANDOM, DBMS_SQL, ORDS