Now that we depend on RESTful services for generating all of our reports, a new challenge exists when promoting applications from development to production. How do we synchronize APEX RESTful service? This step has become mission critical now that all of our reports are generated with APEX Office Print (AOP).

At Storm Petrel, we run one server for development and one server for production. With SQL Development and other tools, keeping comparing the database is straight forward. When we promote an APEX release to production, we tick through a basic checklist: check packages, check table structures, move APEX app. Not too bad.

APEX RESTful services are not “in the database”, therefore SQL Developer is blind to them.

APEX RESTful services are not “in an application”, therefore exporting an application ignores them.

We need a tool to help us.

It won’t take long to guess, that we will use PL/SQL, APEX, and RESTful services.

Tool Kit

Without going into APEX, how do you find APEX RESTful services? They are in public synonym and views that the team at Oracle provide for us. You can explore this views with three queries:

Select * from apex_rest_resource_handlers;

Select * from apex_rest_resource_modules;

Select * from apex_rest_resource_parameters;

Select * from apex_rest_resource_templates;

Build View

The first exploration involves playing with data, for that we built a view based on this query:

select 
	h.workspace       "workspace", 
	t.module_name     "module_name",
	m.uri_prefix      "uri_prefix",
	h.uri_template    "uri_template",
	h.method          "method",
	h.require_https   "require_https",
	h.source          "source",
	h.last_updated_on "updated",
	h.last_updated_by "updated_by",
	h.created_by      "created_by",
	h.created_on      "created"
from apex_rest_resource_handlers h
left join apex_rest_resource_templates t on
  t.template_id = h.template_id
left join apex_rest_resource_modules m on
  t.module_id = m.module_id
order by h.workspace, t.module_name, m.uri_prefix, h.uri_template;

The data in here mimics what you see in the APEX RESTful services.

Synchronize APEX RESTful Services

Compare the screenshot above with the data below:

APEX RESTful Services PLSQL

Synchronize APEX RESTful Services

With the need to compare data from two servers, it made sense to create a RESTful service based on the same data that shows in the query from above. So that is what we did:

API Query

How to Compare

With a few hours, effort in a PL/SQL package, you can query the RESTful services from two servers. In our case, we called them simply, LEFT and RIGHT. You could use an Oracle JSON table and run queries from there, or just spin through the data that you have. We opted to spin through the data that we fetched from the two URLs (left and right).

We wrote three functions:

select apex_rest_pkg.template_count_match from dual;
select apex_rest_pkg.uri_mismatch from dual;
select apex_rest_pkg.query_mismatch from dual;

In the first case, we know if the number of URI templates match. While not super useful, it is a bit of a guide.

The second query gives us a formatted CLOB that details all of the APEX RESTful services that are missing on the “right” server.

The third query compares the JSON queries within the RESTful services. If there is a mismatch, it tells us the name of the module, URI prefix, and URI template. We always copy the literal text from the development server and paste it to the production server.

APEX Rest Package

Alternatives

There are some fine free JSON comparison tools on line. You can certainly paste your data there.

I do rather wish that the SQL Developer database comparison tool has a stupid tool for comparing the values within a source and destination table. It is out of the scope of the tool, and if it were there someone would try to grind through 100,000 records. We are programmers, so writing code that fetches data, compares it, and react constitute fundamental skills. If you need help or the code, catch me on twitter @cmoore_sp, I’ll ship it to you. I don’t mind.

Next Steps

In Oracle APEX, we can add a push button, display the results in a text block and even email them to the developer.

-/qed