What is ORDS?#

Oracle REST Data Services (ORDS) is a mid-tier Java application that connects to your Oracle Database. It maps HTTP URLs and verbs (GET, POST, PUT, DELETE) directly to database transactions.

Instead of writing a Node.js server that opens JDBC connections, executes queries, parses results into JSON, and returns them, you simply write PL/SQL. ORDS handles all the JSON serialization and HTTP protocol overhead for you automatically.

Auto-REST Enabling Tables#

The absolute fastest way to build an API is Auto-REST. If you have an EMPLOYEES table, you can generate full CRUD (Create, Read, Update, Delete) APIs against it with a single PL/SQL call.

BEGIN
  ORDS.ENABLE_OBJECT(
    p_enabled      => TRUE,
    p_schema       => 'HR',
    p_object       => 'EMPLOYEES',
    p_object_type  => 'TABLE',
    p_object_alias => 'emps',
    p_auto_rest_auth => FALSE
  );
END;
/

Immediately, the following endpoints are live:

• GET /ords/hr/emps/ (Returns all rows in JSON, with built-in pagination!)
• GET /ords/hr/emps/101 (Returns employee 101)
• POST /ords/hr/emps/ (Inserts a new employee from a JSON body)

Building Custom PL/SQL Endpoints#

Auto-REST is great, but usually, you want to expose complex business logic, not just raw tables. For this, we build custom endpoints.

Step 1: Define the Module and Template

BEGIN
  -- Create the base path: /api/inventory/
  ORDS.DEFINE_MODULE(
    p_module_name => 'inventory_api',
    p_base_path   => '/api/inventory/'
  );

  -- Define the specific endpoint template: /api/inventory/check/:item
  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'inventory_api',
    p_pattern     => 'check/:item'
  );
END;
/

Step 2: Define the Handler (The PL/SQL Logic)

We attach a GET handler to our template. ORDS automatically converts any SYS_REFCURSOR into a perfectly formatted JSON array.

BEGIN
  ORDS.DEFINE_HANDLER(
    p_module_name => 'inventory_api',
    p_pattern     => 'check/:item',
    p_method      => 'GET',
    p_source_type => ORDS.source_type_collection_feed,
    p_source      => 'SELECT loc, stock_on_hand FROM inv_status WHERE item = :item'
  );
END;
/

If a user hits GET /api/inventory/check/12345, ORDS passes 12345 into the :item bind variable, executes the SQL, and returns:

{"items": [{"loc": 100, "stock_on_hand": 50}, {"loc": 200, "stock_on_hand": 10}]}

Handling URI Parameters#

In the example above, :item in the URI automatically bound to :item in the PL/SQL.

For POST requests, if the client sends a JSON body like {"status": "ACTIVE"}, you can access it in your PL/SQL handler by simply referencing the bind variable :status! ORDS parses the incoming JSON body automatically.

Securing APIs with OAuth 2.0#

Never leave your endpoints completely open. ORDS includes a built-in OAuth 2.0 authorization server.

1. Create a Privilege: Group your modules under a privilege.
2. Create a Client App: Generate a Client ID and Client Secret for your third-party consumer.
3. Grant Access: Assign the privilege to the Client App.

The consumer must first POST their ID and Secret to the /oauth/token endpoint to receive a short-lived Bearer Token. They must pass this token in the Authorization header of subsequent API calls.

Common Gotchas#

Key Takeaways#