1. REST API Reference for Oracle Mobile Cloud Enterprise - Platform APIs
  2. Tasks
  3. Database Access

Execute SQL (POST)

post

/mobile/platform/database/sql

Executes the SQL statement that you pass in the Oracle-Mobile-SQL header. You can call this operation only from custom code.

You can use this operation for any type of SQL statement, such as INSERT, UPDATE, MERGE, DELETE, or SELECT.

If the SQL statement takes parameters, such as SELECT * FROM MOVIES where TITLE = :title and YEAR = :year, then put the parameters and their values in the request body, for example {"title" : "The Imitation Game", "year" : "2014"}.

You can't use this operation to implicitly create a table. It must already exist. When you use this operation to insert a row into a table, the request body can't have any columns that aren't in the table already.

For security reasons, you can call this operation only from custom API implementations by using the custom code SDK. You can't make direct requests from client applications. This API is included in this reference merely to describe the request and response bodies for the custom code SDK calls.

Request

Supported Media Types
Header Parameters

  • The URL-encoded SQL statement to execute. For example:

    SELECT SUM("totalGross") "salesByGenre", "genre" FROM "Movies" GROUP BY "genre"

Body ()
Root Schema : parmList
Type: array
Minimum Number of Items: 0
Unique Items Required: true
Show Source
Nested Schema : parmData
Type: object
Parameter name and value. For example `{ "title" : "The Imitation Game"}`
Example Request (application/json)
{
    "year":"2014",
    "title":"The Imitation Game"
}

Response

Supported Media Types

200 Response

The structure of the response JSON object depends on the SQL verb and whether the table has a primary key.

SELECT example:

{"items":[{ "title" : "The Imitation Game"}] }
INSERT, DELETE, UPDATE, MERGE example when the row has a primary key:
{ "rowCount" : 2 }
INSERT, DELETE, UPDATE, MERGE example when the row key is the id column:
{"items":[{"id":42},{"id":43}]}

Body ()
The response body for the `POST /sql` method.
Root Schema : SQLResponse
Type: object
The response body for the `POST /sql` method.
Match One
Show Source
Nested Schema : rowCount
Type: object
Show Source
Nested Schema : idItems
Type: object
Show Source
Nested Schema : rowItems
Type: object
Show Source
Nested Schema : idRowData
Type: array
Minimum Number of Items: 0
Unique Items Required: true
Show Source
Nested Schema : idData
Type: object
Show Source
Nested Schema : tableData
Type: array
Minimum Number of Items: 1
Unique Items Required: true
Show Source
  • rowData
    Additional Properties Allowed: additionalProperties
    Column names and values. For example `{ "title" : "The Imitation Game"}`. String values can't exceed 4000 characters. Binary types aren't supported.
Nested Schema : rowData
Type: object
Additional Properties Allowed
Show Source
Column names and values. For example `{ "title" : "The Imitation Game"}`. String values can't exceed 4000 characters. Binary types aren't supported.

400 Response

The request failed. Typical reasons are:

  • The number of request bodyparameters doesn't match the number of bind parameters in the SQL statement
  • Unique constraint violation
  • Invalid SQL
  • Invalid table or column name
  • The SQL verb is not valid for this operation

Body ()
Root Schema : Error
Type: object
Title: Error
The error JSON object returned by the service.
Show Source
Nested Schema : o:errorDetails
Type: array
Minimum Number of Items: 0
List of the issues that cause the error. Included when the error is caused by multiple issues.
Show Source
Nested Schema : Error Detail
Type: object
Title: Error Detail
Show Source