Autonomous Database REST API returns 500 Internal Server Error when executing SQL statements

Cloud Platforms Oracle Cloud
, , , , , , ,
1

I’m encountering persistent 500 Internal Server Error responses when trying to execute SQL statements via the Autonomous Database REST API. The same SQL queries work perfectly fine when executed directly through SQL Developer or SQL*Plus, but fail consistently through the API.

The error occurs with both simple SELECT statements and more complex queries. I’ve verified the authentication is correct (we’re getting 200 OK for other API calls), so it seems specific to SQL execution. I’m wondering if there’s something wrong with our API error handling approach, if the SQL payload validation has specific requirements we’re not meeting, or if we need a different retry strategy for this particular endpoint.


POST /ords/admin/_/sql
{"statementText": "SELECT * FROM employees"}

Anyone experienced similar issues with the Autonomous Database SQL REST API?

2

Yes, SQL payload validation is strict. Certain SQL features aren’t supported via REST API: DDL statements (CREATE, ALTER, DROP), some PL/SQL blocks, and queries with bind variables need special handling. Also, the API has limits on result set size and query complexity. For large result sets, you need to implement pagination. The 500 error might be the API’s way of rejecting an unsupported SQL construct. Try with a very simple query first: SELECT 1 FROM DUAL

3

I’ve corrected the endpoint URL to use the proper ORDS base URL for our Autonomous Database instance. However, I’m still getting 500 errors. The request body now includes offset and limit parameters. Could this be related to SQL payload validation? Are there certain SQL constructs that aren’t allowed via the REST API even though they work in SQL clients?

4

Check the response body of the 500 error carefully. Even though it’s a 500, ORDS usually includes error details in the response JSON. Look for fields like ‘code’, ‘message’, and ‘details’. This will tell you exactly what’s wrong - could be authentication scope, SQL parsing error, or resource limits. Also, make sure you’re using the correct HTTP headers: Content-Type: application/json and Accept: application/json.