ORDS REST-Enabled SQL Service: How to Customize Response Format (Complete Guide)

Hello fellas!! 👋
Today's topic: Understanding the REST-Enabled SQL Service in ORDS and How to Customize its Response Format.

REST-Enabled SQL Service in ORDS

What is it? The REST-Enabled SQL service is an HTTPS web service that provides access to the Oracle Database SQL engine. You can POST SQL statements to the service. The service then runs the SQL statements against Oracle Database and returns the result to the client in a JSON format.

Example: Making a Request

When making a request to the REST-Enabled SQL Service, you typically get a response by default that looks like this:

Sample Request

curl --location 'http://localhost:8086/ords/<schema_alias>/_/sql' \
--header 'Content-Type: application/json' \
-u "user:dummySecret" \
--data '{
  "statementText": "select sysdate from dual"
}'

Default Response Format

This is typically how the response would look like:

{
    "env": {
        "defaultTimeZone": "UTC"
    },
    "items": [
        {
            "statementId": 1,
            "statementType": "query",
            "statementPos": {
                "startLine": 1,
                "endLine": 1
            },
            "statementText": "select sysdate from dual",
            "resultSet": {
                "metadata": [
                    {
                        "columnName": "SYSDATE",
                        "jsonColumnName": "sysdate",
                        "columnTypeName": "DATE",
                        "columnClassName": "java.sql.Timestamp",
                        "precision": 7,
                        "scale": 0,
                        "isNullable": 1
                    }
                ],
                "items": [
                    {
                        "sysdate": "2025-04-21T16:40:07Z"
                    }
                ],
                "hasMore": false,
                "limit": 10000,
                "offset": 0,
                "count": 1
            },
            "response": [],
            "result": 0
        }
    ]
}

Notice something? You can see that there's a lot of information in the response. Sometimes, based on your requirements, you would want to limit what's in the response. Well, responseFormat is the answer!

Understanding responseFormat Fields

Here are all the responseFormat fields that you can use to customize your response:

Field Name	Description
responseFormat.resultSetMetaData	Set to true if you want resultSetMetaData to be returned in the response
responseFormat.statementInformation	Set to true if you want statementInformation to be returned in the response
responseFormat.statementText	Set to true if you want statementText to be returned in the response
responseFormat.binds	Set to true if you want the "items.binds" node to be returned in the response
responseFormat.result	Set to true if the "items.result" node is to be returned in the response
responseFormat.response	Set to true if the response is to be returned in the response items.response
responseFormat.numberAsString	Determines whether numeric values in resultSet.item node should be returned as strings

How to Use responseFormat

Example: If you only want the data without all the metadata and statement information, you can customize your request like this:

{
  "statementText": "select sysdate from dual",
  "responseFormat": {
    "resultSetMetaData": false,
    "statementInformation": false,
    "statementText": false
  }
}

This will give you a much cleaner, minimal response with just the data you need!

Summary

The REST-Enabled SQL service in ORDS is incredibly flexible. By using the responseFormat parameter, you can tailor the JSON response to include only the information you need, making your API responses cleaner, smaller, and efficient. T