Response Format and REST-Enabled SQL service in ORDS

By

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:

The REST-Enabled SQL service is a 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: 

When making a request to the REST ENABLED SQL SERVICE, you typically by default get a response as the following:       


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

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
        }
    ]
}


You can see that there’s a lot of insights in the response, sometimes based on your requirements you would want to limit what’s in the response, well ResponseFormat is the answer! 


Here are all the responseFormat fields that you can use!


Filed 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

Comments

Post a Comment

Popular posts from this blog

Introduction to Oracle Rest Data Services (ORDS) – A Beginner’s Guide

By
Image
Hey folks! Today, I want to introduce you to a tool that’s been part of my daily grind: Oracle REST Data Services, or ORDS. So, What’s ORDS? Basically, ORDS is a tool that lets you expose your Oracle database data as RESTful APIs . Instead of wrestling with complex backend code, ORDS makes it super easy to build and deploy APIs. With ORDS, your data becomes accessible from anywhere with just a few HTTP calls. Think of it as giving your database a direct, web-friendly voice. Why ORDS? 🌟 Imagine this: You’ve got an Oracle database packed with valuable data, and you want to make it accessible to an app, a web service, or another system. Usually, you’d need a whole backend to handle data retrieval, authentication, response formatting… you name it. But with ORDS? You just set it up, map it to your database tables or procedures, and voilà—you’ve got an API that you can call from anywhere. Here’s why ORDS is a game-changer: It’s Fast ⚡: You don’t have to build a backend from scratch. It’s...
Read more

ORDS Auto REST vs C# with Entity Framework.

By
Introduction  If you've ever built REST APIs using common used programming languages, you know the drill: create models, wire up a controller, connect to a database, map DTOs, validate, inject dependencies… and that’s before you even touch   security aspect of the api. 😅 Being a past c# lover, I started questioning how easy was it to build REST apis in ORDS compared to c#. Spoiler : ORDS is crazy simple for the basics. Let me show you a side-by-side comparison using the same use case — managing a Country table — and without diving into security (we’ll ignore auth and tokens, and we'll just focus on the   REST experience). Use Case We want to expose the following operations via REST: GET /countries — list all countries GET /countries/{id} — get one country POST /countries — create a new country PUT /countries/{id} — update a country DELETE /countries/{id} — remove a country Let’s see how much effort it takes.  Option 1:  C# with Entity Framework Core 
Read more