APEX REST Enabled SQL: Instant APIs From Your Database

APIs in 30 Seconds

REST Enabled SQL, a feature of Oracle REST Data Services (ORDS), lets you execute SQL and PL/SQL through REST endpoints without creating explicit REST modules. While it is primarily a development and prototyping tool, it is incredibly useful for quick integrations, ad-hoc data access from external tools, and building lightweight APIs for internal applications.

Enabling REST Enabled SQL

In APEX’s SQL Workshop, navigate to RESTful Services and enable REST Enabled SQL for your schema. This creates an endpoint that accepts SQL statements via POST requests:

-- POST to: https://myserver/ords/myschema/_/sql
-- Body:
{
    "statementText": "SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = :dept_id",
    "binds": [{"name": "dept_id", "data_type": "NUMBER", "value": 20}]
}

The response is a JSON object containing the column metadata and result rows. ORDS handles the database connection, SQL execution, and JSON formatting automatically.

PL/SQL Block Execution

{
    "statementText": "BEGIN update_employee_salary(:emp_id, :new_salary); END;",
    "binds": [
        {"name": "emp_id", "data_type": "NUMBER", "value": 101},
        {"name": "new_salary", "data_type": "NUMBER", "value": 75000}
    ]
}

Use Cases

REST Enabled SQL is ideal for development and testing when you need quick database access from Postman, curl, or frontend prototypes. It works well for lightweight integrations where building a full REST module is overkill, such as a Power BI connection or a simple Python script that needs to query Oracle. It is also useful for mobile app prototypes that need a backend API immediately.

Security Considerations

REST Enabled SQL accepts arbitrary SQL, so it must be protected carefully. Enable it only for schemas that need it. Use ORDS roles and privileges to restrict access. In production, prefer explicit REST modules with predefined queries over REST Enabled SQL, because explicit modules expose only the intended operations and cannot be used to run arbitrary SQL. Think of REST Enabled SQL as a development accelerator, not a production API strategy.

Transitioning to Production APIs

When your prototype matures, convert REST Enabled SQL queries into proper ORDS REST modules. Define explicit URI templates, HTTP methods, and source queries. Add authentication (OAuth2 or custom), pagination, and error handling. The REST module approach gives you full control over the API surface and is suitable for production use.