Consuming a REST service in Oracle APEX is one thing. Publishing one is another, and it comes up as soon as somebody outside your application wants the data: a carrier, a marketplace, a reporting tool, all of whom want JSON over HTTP and none of whom should get a database account.
Oracle REST Data Services already runs your APEX instance, and it turns SQL and PL/SQL into REST APIs. This guide publishes orders as a proper module, publishes a table with AutoREST, protects both with OAuth2, and consumes the result back in APEX.
Every query, trigger, and snippet in this article runs against the Orbit Outfitters sample schema: customers, products, orders, stores, and about 2,300 orders of sample data. Install it once and you can follow along in your own workspace.
git clone https://github.com/devvinish/orb_tables.git -- then, as your schema: @orbit/install.sql
How ORDS Maps URLs
| Level | Is |
|---|---|
| Module | A group of related services under a base path, such as /sales/ |
| Template | A URI pattern within the module, where a part starting with a colon is a parameter |
| Handler | One HTTP method for a template, implemented as a query or a PL/SQL block |
The full URL stacks those together: the ORDS address, the schema's URL alias, the module's base path, and the template. Before any of it works the schema must be REST-enabled, which is what gives it that alias and sets whether its services require authentication by default.
Defining a Module
Services can be defined in three places, and all of them write the same ORDS metadata: SQL Workshop's RESTful Services page in APEX, SQL Developer Web under REST, or PL/SQL with the ORDS package.
Use the PL/SQL form. A script can be reviewed, committed to version control, and rerun in the next environment, which none of the point-and-click routes give you.
begin
ords.enable_schema(
p_enabled => true,
p_schema => 'ORBIT',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'orbit',
p_auto_rest_auth => true);
ords.define_module(
p_module_name => 'orbit.sales',
p_base_path => '/sales/',
p_items_per_page => 25);
ords.define_template(
p_module_name => 'orbit.sales',
p_pattern => 'orders/');
ords.define_handler(
p_module_name => 'orbit.sales',
p_pattern => 'orders/',
p_method => 'GET',
p_source_type => ords.source_type_collection_feed,
p_source => q'[
select order_number, order_date, status, order_total
from orb_orders
order by order_date desc, order_number desc]');
ords.define_template(
p_module_name => 'orbit.sales',
p_pattern => 'orders/:order_number');
ords.define_handler(
p_module_name => 'orbit.sales',
p_pattern => 'orders/:order_number',
p_method => 'GET',
p_source_type => ords.source_type_collection_item,
p_source => q'[
select o.order_number, o.order_date, o.status, o.order_total,
o.discount_pct, c.customer_name
from orb_orders o
join orb_customers c on c.customer_id = o.customer_id
where o.order_number = :order_number]');
commit;
end;That one script REST-enables the schema, defines a module, and gives it two templates: a collection and a single item addressed by order number, which arrives in the handler as a bind variable.
| Source type | Produces |
|---|---|
| Collection feed | Rows as an array of items, with pagination |
| Collection item | One row as a single JSON object, with no row meaning 404 Not Found |
| PL/SQL | A block that writes the response itself, reading the request body and setting the status code. This is what POST, PUT, and DELETE use |
Choosing collection item rather than feed for a single record is worth doing properly: it returns an object instead of a one-element array, and it gives you a real 404 for an order number that does not exist, which is what any client library expects.
One note on where you manage these. SQL Workshop's RESTful Services page still works in APEX 26.1 but is deprecated, and Oracle points you to SQL Developer Web instead, which you reach from the ORDS landing page by signing in as the REST-enabled schema. It shows the same modules with a workshop for editing, testing, and securing them.
Calling the Services
curl http://localhost:8080/ords/orbit/sales/orders/ORD-12283
The response turns column names into lowercase JSON properties, returns dates in ISO 8601 UTC, and adds a links array pointing at related resources such as the collection this order belongs to.
curl "http://localhost:8080/ords/orbit/sales/orders/?limit=2"
Collections page themselves. A limit and offset in the query string choose the page, and the response reports whether more rows follow, along with next and first links and a describedby link documenting the columns. Without a limit, a page holds however many items the module declared.
That pagination is free and it matters, because a handler returning every order in one response is a denial of service waiting for its first busy day.
AutoREST
AutoREST publishes a table or view with one call, generating GET for rows and single rows, POST for inserts, PUT and DELETE by primary key, and query-string filters. It is remarkably quick, and that is exactly why it needs thought.
declare
l_client ords_types.t_client_credentials;
begin
ords.enable_object(
p_enabled => true,
p_schema => 'ORBIT',
p_object => 'ORB_PRODUCTS',
p_object_type => 'TABLE',
p_object_alias => 'products',
p_auto_rest_auth => true);
l_client := ords_security.register_client(
p_name => 'orbit_catalog_reader',
p_grant_type => 'client_credentials',
p_support_email => 'it@orbit-outfitters.example',
p_description => 'Reads the Orbit product catalog',
p_client_secret => ords_types.oauth_client_secret());
ords_security.grant_client_role(
p_client_name => 'orbit_catalog_reader',
p_role_name => 'oracle.dbtools.role.autorest.ORBIT.ORB_PRODUCTS');
commit;
dbms_output.put_line('client_id: ' || l_client.client_key.client_id);
dbms_output.put_line('client_secret: ' || l_client.client_secret.secret);
end;Enabling the object publishes it, and ORDS creates a privilege protecting it plus a role granting that privilege. Registering a client with the client credentials flow produces an ID and a generated secret, and granting the role connects the two.
Store that secret when the script prints it, because ORDS keeps only a hash and will not show it again. Treat it exactly as you would a password.
curl -u "<client_id>:<client_secret>" -d grant_type=client_credentials \
http://localhost:8080/ords/orbit/oauth/token
curl -H "Authorization: Bearer <access_token>" \
"http://localhost:8080/ords/orbit/products/?limit=1"Without a token the request is refused with 401. With the client's credentials the partner exchanges them for a short-lived bearer token, an hour by default, and sends that with each call. Because AutoREST also generates writing methods, requiring authentication is not optional, which is why the schema was enabled with authentication on by default.
Now the caveat that decides whether you should use AutoREST at all for an external partner. It publishes the whole table, every column, including the cost price that a marketplace has no business seeing. For anything crossing a company boundary, write a module with handlers selecting exactly the columns and rows that partner may have. AutoREST is excellent for internal tools and prototypes, and a liability as a public API.
Securing Modules
Modules are protected the same way. Create a role, define a privilege listing the roles it requires and the URL patterns it protects, and attach it to the module, after which any client or user holding one of those roles may call the services.
Beyond client credentials, ORDS supports the authorization code and implicit flows, where a person signs in and approves access, and JWT profiles that accept tokens from an external identity provider such as OCI IAM, Microsoft Entra ID, or Okta. That last option is usually the right one in an organization that already has an identity provider, since it avoids issuing another set of credentials nobody will rotate.
Consuming the Services in APEX
An APEX application reads these like any other service. A REST Data Source of type Oracle REST Data Services, pointed at the collection URL, discovers the columns and understands the pagination, so a report fetches one page at a time rather than dragging everything across.
For the protected products, the data source uses a web credential of type OAuth2 Client Credentials holding the client ID and secret, and APEX requests and renews the tokens itself. You never write token-handling code, and the secret stays encrypted in the workspace rather than in your application.
Conclusion
ORDS turns the database you already have into an API without adding a server, and the structure is small enough to hold in your head: a REST-enabled schema gives you an alias, a module gives a base path, templates give URI patterns with colon parameters, and handlers implement the methods with a query or a PL/SQL block. Choose the collection item source type for single records so clients get an object and a real 404, and let the collection feed paginate rather than returning everything. Define it all in a PL/SQL script, because that is what you can review and rerun in the next environment. AutoREST publishes a table in one call, which suits internal use and prototypes, but it exposes every column, so partners deserve a module with handlers that select only what they may see. Protect both with privileges and roles, issue a client credentials client where a system is calling and a JWT profile where an identity provider already exists, and store the generated secret the one time it is shown. Then point a REST data source back at it, and your own applications consume the same API as everybody else.
