REST-Enabled SQL Service

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.

Statically defined RESTful services use predefined SQL statements that are useful when you need a fixed and repeatable service. The REST- Enabled SQL service enables you to define SQL statements dynamically and run them against the database without predefined SQL statements. This makes your data more accessible over REST.

Typical Use Case: Your Oracle Database is in the cloud and you want to make it available through a REST API over HTTPS.

Predefined REST APIs provide common operations such as returning the results of reports and providing an API for updating common tables in your database. There is a need for client developers to run their own queries or queries that can only be written at run time. In these cases, a REST- Enabled SQL service is useful.

Note: If you have Oracle REST Data Services installed and if you do not have SQLNet (JDBC, OCI) to establish an network connection to Oracle Database, then a REST-Enabled SQL service provides an easy mechanism to query and run SQL, SQLPlus, and SQLcl statements against the REST-enabled Oracle Database schema.

REST-Enabled SQL Service Terminology This section introduces some common terms that are used throughout this document.
Configuring the REST-Enabled SQL Service
Using cURL with REST-Enabled SQL Service This section explains how to use cURL commands to access the REST-Enabled SQL service.
Getting Started with the REST-Enabled SQL Service
REST-Enabled SQL Service Examples This section provides different HTTPS POST request examples that use Oracle REST Data Services standalone setup with secure HTTPS access.
REST-Enabled SQL Request and Response Specifications
[Supported SQL, SQLPlus, and SQLcl Statements](rest-enabled-sql-service.html#GUID-AE7F4291-02B1-4608-B5B2-F783A15D8AEB) This section lists all the supported SQL, SQLPlus and SQLcl statements for REST-Enabled SQL service.
REST-Enabled SQL Service and MySQL Database This section describes an ORDS feature that is supported only with MySQL databases running on Oracle Cloud Infrastructure.

REST-Enabled SQL Service Terminology

This section introduces some common terms that are used throughout this document.

REST- Enabled SQL service: A HTTPS web service that provides SQL access to the database. SQL statements can be posted to the service, and the results are returned in a JSON format to the client.
HTTPS: Hyper Text Transfer Protocol Secure (HTTPS) is the secure version of HTTP, the protocol over which data is sent between your browser and the website to which you are connected. The ‘S’ stands for secure. It means that all communications between your browser and Oracle REST Data Services are encrypted.
cURL: cURL is a command-line tool used to transfer data. It is free and open source software that can be downloaded from the following location: curl_haxx.
*SQLNet (or Net8)*: SQLNet is the networking software of Oracle that enables remote data access between programs and Oracle Database.

Configuring the REST-Enabled SQL Service

By default, the REST- Enabled SQL service is turned off. To configure the REST- Enabled SQL service settings, see Configuring REST Enabled SQL Service Settings.

Using cURL with REST-Enabled SQL Service

This section explains how to use cURL commands to access the REST-Enabled SQL service.

You can use the HTTPS POST method to access the REST-Enabled SQL service. To access the REST-Enabled SQL service, you can use the command-line tool named cURL. This powerful tool is available for most platforms, and enables you to connect and control the data that you send to and receive from a REST-Enabled SQL service.

Example 15-1 Example cURL Command

Request: curl -i -X POST --user ORDSTEST:ordstest --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://localhost:8088/ords/ordstest/_/sql

Where:

The-i option displays the HTTP headers returned by the server.
The -k option enables cURL to proceed and operate even for server connections that are otherwise considered to be insecure.

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":2
            },
            "statementText":"select sysdate from dual",
            "response":[
            ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"SYSDATE",
                        "jsonColumnName":"sysdate",
                        "columnTypeName":"DATE",
                        "precision":0,
                        "scale":0,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "sysdate":"2017-07-21T08:06:44Z"
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                "count":1
            }
        }
    ]
}

Getting Started with the REST-Enabled SQL Service

The REST- Enabled SQL service is provided only through HTTPS POST method.

  <div class="infoboxnote" markdown="1">
  **Note:**

  In ORDS, a RESTful service is stateless. In a stateless environment, each HTTPS request from a client maps to a new database session. Therefore, a session begins and ends with every SQL statement or script execution, that is, the worksheet is auto-committed.

  As the session state is not maintained, session attributes do not persist and commands such as ROLLBACK and COMMIT do not apply. If a SQL statement or script executes successfully, an implicit commit is performed. If it executes with an error, an implicit rollback is performed. Therefore, when you need, include the ROLLBACK and COMMIT commands or session attributes in the PL/SQL code block that is sent to the database for a session.

  </div>

Topics:

REST-Enabling the Oracle Database Schema
REST-Enabled SQL Authentication
REST-Enabled SQL Endpoint
REST-Enabling the Oracle Database Schema
REST-Enabled SQL Authentication This section explains how to authenticate the schema on which you want to use the REST-Enabled SQL service.
REST-Enabled SQL Endpoint This section shows the format or pattern used to access the REST- Enabled SQL service.

REST-Enabling the Oracle Database Schema

You must REST-enable the Oracle database schema on which you want to use the REST- Enabled SQL service. To REST-enable the Oracle Database schema, you can use SQL Developer or the PL/SQL API.

The following code snippet shows how to REST-enable the Oracle Database schema ORDSTEST:

SQL> CONNECT ORDSTEST/*****;
Connected
SQL> exec ords.enable_schema;
anonymous block completed
SQL> commit;
Commit complete.
SQL>

Related Topics

Auto-Enabling Using the PL/SQL API

REST-Enabled SQL Authentication

This section explains how to authenticate the schema on which you want to use the REST-Enabled SQL service.

Before using the REST-Enabled SQL service, you must authenticate using the SQL Developer role.

The Following are the different types of authentications available:

First Party Authentication (Basic Authentication): For this authentication, create a user in Oracle REST Data Services with the SQL Developer role. This Oracle REST Data Services user will be able to run SQL for any Oracle database schema that is REST-enabled.
Schema Authentication: For this authentication, use the Oracle Database schema name in uppercase and the Oracle database schema password (for example, HR and HRPassword). This type of user will be able to run SQL for the specified schema. It will be given the SQL Developer role by Oracle REST Data Services.
OAuth 2 Client Credentials: For this authentication, perform the following steps to grant the SQL Developer role to the client in Oracle REST Data Services:
1. Create a client using OAUTH.create_client.
2. Grant the SQL Developer role to the client.
3. Acquire the access token using the client_id and client_secret of the client.
4. Specify the access token in subsequent REST-Enabled SQL requests.

REST-Enabled SQL Endpoint

This section shows the format or pattern used to access the REST- Enabled SQL service.

If Oracle REST Data Services is running in a Java EE Application Server, then the REST-Enabled SQL service is only accessible through HTTPS. If Oracle REST Data Services is running in standalone mode, then Oracle REST Data Services can be configured to use HTTPS. The examples in this document use this configuration.

The following example URL locates the REST-Enabled SQL service for the specified schema alias:

Pattern: https://<HOST>/ords/<SchemaAlias>/_/sql

Example: https://host/ords/ordstest/_/sql

Where: The default port is 443

Content Type and Payload Data Type Supported

The HTTPS POST request consists of the following:

Header Content-Type
- application/sql: for SQL statements
- application/json: for JSON documents
Payload data type
- SQL: SQL, PL/SQL, SQL*Plus, SQLcl statements
- JSON document: A JSON document with SQL statements and other options such as bind variables

REST-Enabled SQL Service Examples

This section provides different HTTPS POST request examples that use Oracle REST Data Services standalone setup with secure HTTPS access.

The payload data of the HTTPS POST request message can be in one of the following formats:

POST Requests Using application/sql Content-Type

For POST requests with Content-Type as application/sql , the payload is specified using SQL, SQL*Plus, and SQLcl statements. The payload can be a single line statement, multiple line statements, or a file that consists of multiline statements as shown in the following examples:

Using a Single SQL Statement
Using Multiple SQL Statements

Using a File with cURL

  <div class="infoboxnote" markdown="1">

  **Note:**
  While evaluating your SQL/PLSQL statements, if you see an error message 555 with the following message, then ensure that you have correctly formed your SQL/PLSQL statement:
  " 555 User Defined Resource Error

  The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource.Please check the SQL statement is correctly formed and executes without error"
  </div>

Using a Single SQL Statement
Using a File with cURL
Using Multiple SQL Statements

Using a Single SQL Statement

The following example uses Schema Authentication to run a single SQL statement against the demo Oracle Database schema:

Request:

curl -i -X POST --user DEMO:demo --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked

{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":2
            },
            "statementText":"select sysdate from dual",
            "response":[

            ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"SYSDATE",
                        "jsonColumnName":"sysdate",
                        "columnTypeName":"DATE",
                        "precision":0,
                        "scale":0,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "sysdate":"2017-07-21T08:06:44Z"
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                "count":1
            }
        }
    ]
}

Where:

DEMO is the Oracle Database schema name.
demo is the Oracle Database schema password.
select sysdate from dual is the SQL statement that will run in the DEMO Oracle Database schema.
Content-Type: application/sql is the content type. Only application/sql and application/json are supported.
https://localhost:8088/ords/demo/_/sql is the location of the REST- Enabled SQL service for the demo Oracle Database schema.

Using a File with cURL

For multiline SQL statements, using a file as payload data in requests is useful.

File: simple_query.sql

SELECT 10
FROM dual;

Request:

curl -i -X POST --user DEMO:demo --data-binary "@simple_query.sql" -H "Content-Type: application/sql" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked

{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":1
            },
            "statementText":"SELECT 10 FROM dual",
            "response":[

            ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"10",
                        "jsonColumnName":"10",
                        "columnTypeName":"NUMBER",
                        "precision":0,
                        "scale":-127,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "10":10
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                "count":1
            }
        }
    ]
}

Using Multiple SQL Statements

You can run one or more statements in each POST request. Statements are separated similar to Oracle Database SQLPlus script syntax, such as, end of line for SQLPlus statements, a semi colon for SQL statements, and forward slash for PL/SQL statements.

File: **script.sql**:

CREATE TABLE T1 (col1 INT);
DESC T1
INSERT INTO T1 VALUES(1);
SELECT * FROM T1;
BEGIN
INSERT INTO T1 VALUES(2);
END;
/
SELECT * FROM T1;

Request:curl -i -X POST --user DEMO:demo --data-binary "@script.sql" -H "Content-Type: application/sql" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked

{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"ddl",
            "statementPos":{
                "startLine":1,
                "endLine":1
            },
            "statementText":"CREATE TABLE T_EXAMPLE1 (col1 INT)",
            "response":[
                "\nTable T_EXAMPLE1 created.\n\n"
            ],
            "result":0
        },
        {
            "statementId":2,
            "statementType":"sqlplus",
            "statementPos":{
                "startLine":2,
                "endLine":2
            },
            "statementText":"DESC T_EXAMPLE1",
            "response":[
                "Name Null\n Type \n---- ----- ---------- \nCOL1 NUMBER(38) \n"
            ],
            "result":0
        },
        {
            "statementId":3,
            "statementType":"dml",
            "statementPos":{
                "startLine":3,
                "endLine":3
            },
            "statementText":"INSERT INTO T_EXAMPLE1 VALUES(1)",
            "response":[
                "\n1 row inserted.\n\n"
            ],
            "result":1
        },
        {
            "statementId":4,
            "statementType":"query",
            "statementPos":{
                "startLine":4,
                "endLine":4
            },
            "statementText":"SELECT * FROM T_EXAMPLE1",
            "response":[

            ],
            "result":1,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"COL1",
                        "jsonColumnName":"col1",
                        "columnTypeName":"NUMBER",
                        "precision":38,
                        "scale":0,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "col1":1
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                " count":1
            }
        },
        {
            "statementId":5,
            "statementType":"plsql",
            "statementPos":{
                "startLine":5,
                "endLine":8
            },
            "statementText":"BEGIN\n INSERT INTO T_EXAMPLE1 VALUES(2);\nEND;",
            "response":[
                "\nPL\/SQL procedure successfully completed.\n\n"
            ],
            "result":1
        },
        {
            "statementId":6,
            "statementType":"query",
            "statementPos":{
                "startLine":9,
                "endLine":9
            },
            "statementText":"SELECT * FROM T_EXAMPLE1",
            "response":[

            ],
            "result":1,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"COL1",
                        "jsonColumnName":"col1",
                        "columnTypeName":"NUMBER",
                        "precision":38,
                        "scale":0,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "col1":1
                    },
                    {
                        "col1":2
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                "count":2
            }
        },
        {
            "statementId":7,
            "statementType":"ddl",
            "statementPos":{
                "startLine":10,
                "endLine":10
            },
            "statementText":"DROP TABLE T_EXAMPLE1",
            "response":[
                "\nTable T_EXAMPLE1 dropped.\n\n"
            ],
            "result":1
        }
    ]
}

POST Requests Using application/json Content-Type

Using a JSON document as the payload enables you to define more complex requests as shown in the following sections:

Using a File with cURL
Specifying the Limit Value in a POST Request for Pagination
Specifying the Offset Value in a POST Request for Pagination
Defining Binds in a POST Request
Using a File with cURL
Specifying the Limit Value in a POST Request for Pagination You can specify the limit value in a POST JSON request for the pagination of a large result set returned from a query.
Specifying the Offset Value in a POST Request for Pagination You can specify the offset value in a POST JSON request. This value specifies the first row that must be returned and is used for pagination of the result set returned from a query.
Defining Binds in a POST Request You can define binds in JSON format. This functionality is useful when calling procedures and functions that use binds as the parameters.
Specifying Batch Statements in a POST Request This section shows the examples with batch statements and batch bind values in a POST request.

Using a File with cURL

The following example posts a JSON document (within the simple_query.json file) to the REST-Enabled SQL service.

File: simple_query.json

{ "statementText":"SELECT TO_DATE('01-01-1976','dd-mm-yyyy') FROM dual;"}

Request: curl -i -X POST --user DEMO:demo --data-binary "@simple_query.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Where:

The statementText holds the SQL statement or statements.
The Content-Type is application/json.

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":1
            },
            "statementText":"SELECT TO_DATE('01-01-1976','dd-mm-yyyy') FROM dual",
            "response":[
             ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"TO_DATE('01-01-1976','DD-MM-YYYY')",
                        "jsonColumnName":"to_date('01-01-1976','dd-mm-yyyy')",
                        "columnTypeName":"DATE",
                        "precision":0,
                        "scale":0,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "to_date('01-01-1976','dd-mm-yyyy')":"1976-01-01T00:00:00Z"
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                "count":1
            }
        }
    ]
}

Specifying the Limit Value in a POST Request for Pagination

You can specify the limit value in a POST JSON request for the pagination of a large result set returned from a query.

File: limit.json

{
 "statementText": "
 WITH data(r) AS (
 SELECT 1 r FROM dual
 UNION ALL
 SELECT r+1 FROM data WHERE r < 100
 )
 SELECT r FROM data;",
 "limit": 5
}

Request: curl -i -X POST --user DEMO:demo --data-binary "@limit.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Where: The limit is the maximum number of rows returned from a query.

Note: The maximum number of rows returned from a query is based on the misc.pagination.maxRows value set in defaults.xml file.

Response:

HTTP/
1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
 {
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":1
            },
            "statementText":" WITH data(r) AS ( SELECT 1 r FROM dual UNION ALL SELECT r+1 FROM data WHERE r < 100 ) SELECT r FROM data",
            "response":[
             ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"R",
                        "jsonColumnName":"r",
                        "columnTypeName":"NUMBER",
                        "precision":0,
                        "scale":-127,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "r":1
                    },
                    {
                        "r":2
                    },
                    {
                        "r":3
                    },
                    {
                        "r":4
                    },
                    {
                        "r":5
                    }
                ],
                "hasMore":true,
                "limit":5,
                "offset":0,
                "count":5
            }
        }
    ]
}

Specifying the Offset Value in a POST Request for Pagination

You can specify the offset value in a POST JSON request. This value specifies the first row that must be returned and is used for pagination of the result set returned from a query.

File: offset_limit.json

{
 "statementText": "
 WITH data(r) AS (
 SELECT 1 r FROM dual
 UNION ALL
 SELECT r+1 FROM data WHERE r < 100
 )
 SELECT r FROM data;",
 "offset": 25,
 "limit": 5
}

Request: curl -i -X POST --user DEMO:demo --data-binary "@offset_limit.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Where: offset is the first row to be returned in the result set. Typically, this is used to provide the pagination for a large result set that returns the next page of rows in the result set. Note: Each request made to the REST-Enabled SQL service is performed in its own transaction, which means that you cannot ensure that the rows returned will match the previous request. To avoid these risks, queries that need pagination should use the ORDER BY clause on a primary key.

Response:

HTTP/
1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
 {
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":1
            },
            "statementText":" WITH data(r) AS ( SELECT 1 r FROM dual UNION ALL SELECT r+1 FROM data WHERE r < 100 ) SELECT r FROM data",
            "response":[
            ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"R",
                        "jsonColumnName":"r",
                        "columnTypeName":"NUMBER",
                        "precision":0,
                        "scale":-127,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "r":26
                    },
                    {
                        "r":27
                    },
                    {
                        "r":28
                    },
                    {
                        "r":29
                    }
                    {
                        "r":30
                    }
                ],
                "hasMore":true,
                "limit":5,
                "offset":25,
                "count":5
            }
        }
    ]
}

Defining Binds in a POST Request

You can define binds in JSON format. This functionality is useful when calling procedures and functions that use binds as the parameters.

Example 15-2 Binds in POST Request

File: binds.json

"statementText": "CREATE PROCEDURE TEST_OUT_PARAMETER
(V_PARAM_IN IN INT, V_PARAM_OUT OUT INT) AS BEGIN V_PARAM_OUT := V_PARAM_IN + 10; END;
/
EXEC TEST_OUT_PARAMETER(:var1, :var2)",
 "binds":[
 {"name":"var1","data_type":"NUMBER","value":10},
 {"name":"var2","data_type":"NUMBER","mode":"out"}
 ]
}

Request: curl -i -X POST --user DEMO:demo --data-binary "@binds.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
 {
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"plsql",
            "statementPos":{
                "startLine":1,
                "endLine":2
            },
            "statementText":"CREATE PROCEDURE TEST_OUT_PARAMETER (V_PARAM_IN IN INT, V_PARAM_OUT OUT INT) AS BEGIN V_PARAM_OUT := V_PARAM_IN + 10; END;",
            "response":[
                "\nProcedure TEST_OUT_PARAMETER compiled\n\n"
            ],
            "result":0,
            "binds":[
                {
                    "name":"var1",
                    "data_type":"NUMBER",
                    "value":10
                },
                {
                    "name":"var2",
                    "data_type":"NUMBER",
                    "mode":"out",
                    "result":null
                }
            ]
        },
        {
            "statementId":2,
            "statementType":"sqlplus",
            "statementPos":{
                "startLine":3,
                "endLine":3
            },
            "statementText":"EXEC TEST_OUT_PARAMETER(:var1, :var2)",
            "response":[
                "\nPL\/SQL procedure successfully completed.\n\n"
            ],
            "result":0,
            "binds":[
                {
                    "name":"var1",
                    "data_type":"NUMBER",
                    "value":10
                },
                {
                    "name":"var2",
                    "data_type":"NUMBER",
                    "mode":"out",
                    "result":20
                }
            ]
        }
    ]
}

Example 15-3 Complex Bind in POST Request

File: complex_bind_example.json

{
    "statementText":"
declare
 type t is table of number index by binary_integer;
 l_in  t    := :IN;
 l_out t;
 begin
   for i in 1..l_in.count loop
  l_out(i) := l_in(i) * 2;
   end loop;
   :L_OUT := l_out;
 end;
",
    "binds":[
        {
            "name":"IN",
            "data_type":"PL/SQL TABLE",
            "type_name":"",
            "type_subname":"",
            "type_components":[
                {
                    "data_type":"NUMBER"
                }
            ],
            "value":[
                2,
                4,
                7
            ]
        },
        {
            "name":"L_OUT",
            "data_type":"PL/SQL TABLE",
            "type_name":"",
            "type_subname":"",
            "type_components":[
                {
                    "data_type":"NUMBER"
                }
            ],
            "mode":"out"
        }
    ]
}

Request: curl -i -X POST --user DEMO:demo --data-binary "@complex_bind_example.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"plsql",
            "statementPos":{
                "startLine":2,
                "endLine":12
            },
            "statementText":"declare \n type t is table of number index by binary_integer;   \n l_in  t    := :IN;   \n l_out t;  \n begin   \n   for i in 1..l_in.count loop   \n  l_out(i) := l_in(i) * 2;  \n   end loop;   \n   :L_OUT := l_out;   \n end;",
            "response":[
            ],
            "result":1,
            "binds":[
                {
                    "name":"IN",
                    "data_type":"PL/SQL TABLE",
                    "type_components":[
                        {
                            "data_type":"NUMBER"
                        }
                    ],
                    "type_name":"",
                    "type_subname":"",
                    "value":[
                        2,
                        4,
                        7
                    ]
                },
                {
                    "name":"L_OUT",
                    "data_type":"PL/SQL TABLE",
                    "mode":"out",
                    "type_components":[
                        {
                            "data_type":"NUMBER"
                        }
                    ],
                    "type_name":"",
                    "type_subname":"",
                    "result":[
                        4,
                        8,
                        14
                    ]
                }
            ]
        }
    ]
}

Specifying Batch Statements in a POST Request

This section shows the examples with batch statements and batch bind values in a POST request.

Example 15-4 Batch statements

File: batch_example.json

{
    "statementText":[
        "insert into adhoc_table_simple values(1)",
        "insert into adhoc_table_simple values(2)",
        "delete from adhoc_table_simple"
    ]
}

Request :curl -i -X POST --user DEMO:demo --data-binary "@batch_example.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"dml",
            "statementPos":{
                "startLine":0,
                "endLine":0
            },
            "statementText":[
                "insert into adhoc_table_simple values(1)",
                "insert into adhoc_table_simple values(2)",
                "delete from adhoc_table_simple"
            ],
            "response":[
                "\n1 row inserted.\n\n",
                "\n1 row inserted.\n\n",
                "\n2 rows inserted.\n\n"
            ],
            "result":[
                1,
                1,
                2
            ]
        }
    ]
}

Example 15-5 Batch bind values

File: batch_bind_example.json

{
    "statementText":"INSERT INTO ADHOC_TABLE_DATE VALUES(?,?)",

    "binds":[
        {
            "index":1,
            "data_type":"NUMBER",
            "batch":true,
            "value":[
                3,
                6,
                9,
                13,
                17
            ]
        },
        {
            "index":2,
            "data_type":"DATE",
            "batch":true,
            "value":[
                "2017-02-21T06:12:20Z",
                "2017-02-21T06:12:20Z",
                "2017-02-21T06:12:20Z",
                "2017-02-21T06:12:20Z",
                "2017-02-21T06:12:20Z"
            ]
        }
    ]
}

Request: curl -i -X POST --user DEMO:demo --data-binary "@batch_bind_example.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Response:

HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"dml",
            "statementPos":{
                "startLine":1,
                "endLine":2
            },
            "statementText":"INSERT INTO ADHOC_TABLE_DATE VALUES(?,?)",
            "response":[
                "\n1 row inserted.\n\n",
                "\n1 row inserted.\n\n",
                "\n1 row inserted.\n\n",
                "\n1 row inserted.\n\n",
                "\n1 row inserted.\n\n"
            ],
            "result":[
                1,
                1,
                1,
                1,
                1
            ],
            "binds":[
                {
                    "index":1,
                    "data_type":"NUMBER",
                    "batch":true,
                    "value":[
                        3,
                        6,
                        9,
                        13,
                        17
                    ]
                },
                {
                    "index":2,
                    "data_type":"DATE",
                    "batch":true,
                    "value":[
                        "2017-02-21T06:12:20Z",
                        "2017-02-21T06:12:20Z",
                        "2017-02-21T06:12:20Z",
                        "2017-02-21T06:12:20Z",
                        "2017-02-21T06:12:20Z"
                    ]
                }
            ]
        }
    ]
}

Example POST Request with DATE and TIMESTAMP Format

Example 15-6 Oracle REST Data services Time Zone Set as Europe/London

Oracle Database DATE and TIMESTAMP data types do not have a time zone associated with them. The DATE and TIMESTAMP values are associated with the time zone of the application. Oracle REST Data Services and the REST- Enabled SQL service return values in a JSON format. The standard for JSON is to return date and timestamp values using the UTC Zulu format. Oracle REST Data Services and the REST- Enabled SQL service return Oracle Database DATE and TIMESTAMP values in the Zulu format using the time zone in which Oracle REST Data Services is running.

Oracle recommends running Oracle REST Data Services using the UTC time zone to make this process easier.

File: date.json

{
    "statementText":"SELECT TO_DATE('2016-01-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) winter, TO_DATE('2016-07-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) summer FROM dual;"
}

Request: curl -i -X POST --user DEMO:demo --data-binary "@date.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql

Response: Note: In this example, both DATE values are specified as 10 a.m. The "summer" value is returned as 9 a.m. Zulu time. This is due to British Summer Time.

HTTP/
1.1 200 OK
Date: Wed, 26 Jul 2017 14:59:27 GMT
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
Server: Jetty(9.2.21.v20170120)
{
    "env":{
        "defaultTimeZone":"Europe/London"
    },
    "items":[
        {
            "statementId":1,
            "statementType":"query",
            "statementPos":{
                "startLine":1,
                "endLine":1
            },
            "statementText":"SELECT TO_DATE('2016-01-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) winter, TO_DATE('2016-07-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) summer FROM dual",
            "response":[
             ],
            "result":0,
            "resultSet":{
                "metadata":[
                    {
                        "columnName":"WINTER",
                        "jsonColumnName":"winter",
                        "columnTypeName":"DATE",
                        "precision":0,
                        "scale":0,
                        "isNullable":1
                    },
                    {
                        "columnName":"SUMMER",
                        "jsonColumnName":"summer",
                        "columnTypeName":"DATE",
                        "precision":0,
                        "scale":0,
                        "isNullable":1
                    }
                ],
                "items":[
                    {
                        "winter":"2016-01-01T10:00:03Z",
                        "summer":"2016-07-01T09:00:03Z"
                    }
                ],
                "hasMore":false,
                "limit":1500,
                "offset":0,
                "count":1
            }
        }
    ]
}

Data Types and Formats Supported

The following code snippet shows the different data types and the formats supported:

{
    "statementText":"SELECT ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? FROM dual",
    "binds":[
        {
            "index":1,
            "data_type":"NUMBER",
            "value":1233
        },
        {
            "index":2,
            "data_type":"NUMERIC",
            "value":123
        },
        {
            "index":3,
            "data_type":"DECIMAL",
            "value":123
        },
        {
            "index":4,
            "data_type":"DEC",
            "value":123
        },
        {
            "index":5,
            "data_type":"NUMBER",
            "value":123
        },
        {
            "index":6,
            "data_type":"INTEGER",
            "value":123
        },
        {
            "index":7,
            "data_type":"INT",
            "value":123
        },
        {
            "index":8,
            "data_type":"SMALLINT",
            "value":123
        },
        {
            "index":9,
            "data_type":"FLOAT",
            "value":123
        },
        {
            "index":10,
            "data_type":"DOUBLE PRECISION",
            "value":123
        },
        {
            "index":11,
            "data_type":"REAL",
            "value":123
        },
        {
            "index":12,
            "data_type":"BINARY_FLOAT",
            "value":123
        },
        {
            "index":13,
            "data_type":"BINARY_DOUBLE",
            "value":123
        },
        {
            "index":14,
            "data_type":"CHAR",
            "value":"abc"
        },
        {
            "index":15,
            "data_type":"CHARACTER",
            "value":"abc"
        },
        {
            "index":16,
            "data_type":"VARCHAR",
            "value":"abc"
        },
        {
            "index":17,
            "data_type":"VARCHAR2",
            "value":"abc"
        },
        {
            "index":18,
            "data_type":"CHAR VARYING",
            "value":"abc"
        },
        {
            "index":19,
            "data_type":"CHARACTER VARYING",
            "value":"abc"
        },
        {
            "index":20,
            "data_type":"NCHAR",
            "value":"abc"
        },
        {
            "index":21,
            "data_type":"NATIONAL CHAR",
            "value":"abc"
        },
        {
            "index":22,
            "data_type":"NATIONAL CHARACTER",
            "value":"abc"
        },
        {
            "index":23,
            "data_type":"NVARCHAR",
            "value":"abc"
        },
        {
            "index":24,
            "data_type":"NVARCHAR2",
            "value":"abc"
        },
        {
            "index":25,
            "data_type":"NCHAR VARYING",
            "value":"abc"
        },
        {
            "index":26,
            "data_type":"NATIONAL CHAR VARYING",
            "value":"abc"
        },
        {
            "index":27,
            "data_type":"NATIONAL CHARACTER VARYING",
            "value":"abc"
        },
        {
            "index":28,
            "data_type":"DATE",
            "value":"01-Jan-2016"
        },
        {
            "index":29,
            "data_type":"TIMESTAMP",
            "value":"1976-02-01T00:00:00Z"
        },
        {
            "index":30,
            "data_type":"TIMESTAMP",
            "value":"1976-02-01T00:00:00Z"
        },
        {
            "index":31,
            "data_type":"TIMESTAMP WITH LOCAL TIME ZONE",
            "value":"1976-02-01T00:00:00Z"
        },
        {
            "index":32,
            "data_type":"TIMESTAMP WITH TIME ZONE",
            "value":"1976-02-01T00:00:00Z"
        },
        {
            "index":33,
            "data_type":"INTERVALYM",
            "value":"P10Y10M"
        },
        {
            "index":34,
            "data_type":"INTERVAL YEAR TO MONTH",
            "value":"P10Y10M"
        },
        {
            "index":35,
            "data_type":"INTERVAL YEAR(2) TO MONTH",
            "value":"P10Y10M"
        },
        {
            "index":36,
            "data_type":"INTERVALDS",
            "value":"P11DT10H10M10S"
        },
        {
            "index":37,
            "data_type":"INTERVAL DAY TO SECOND",
            "value":"P11DT10H10M10S"
        },
        {
            "index":38,
            "data_type":"INTERVAL DAY(2) TO SECOND(6)",
            "value":"P11DT10H10M10S"
        },
        {
            "index":39,
            "data_type":"ROWID",
            "value":1
        },
        {
            "index":40,
            "data_type":"RAW",
            "value":"AB"
        },
        {
            "index":41,
            "data_type":"LONG RAW",
            "value":"AB"
        },
        {
            "index":42,
            "data_type":"CLOB",
            "value":"clobvalue"
        },
        {
            "index":43,
            "data_type":"NCLOB",
            "value":"clobvalue"
        },
        {
            "index":45,
            "data_type":"LONG",
            "value":"A"
        }
    ]

}

REST-Enabled SQL Request and Response Specifications

The following sections provide REST-Enabled SQL request and response specifications:

Request Specification

Request Specification for application/sql

The body of the request is in plain UTF8 text. Statements can be separated by their usual SQL*Plus terminator.

Specification for application/json

JSONPath	Type	Description	Example	Default Value	Possible Values
`$.statementText`	String	Specifies the SQL statements to execute.	`"select 1 from dual"`	Not applicable	Not applicable
`$.statementText`	Array	Specifies batch DML statements using an array. One DML statement is specified per string in an array.	`[ "insert into test1 values(1)","update test1 set col1=2" ]`	Not applicable	Not applicable
`$.offset`	Number	Specifies the number of rows to offset the query result. This is used for pagination of the result set returned from a query.	`25`	`0`	Between 0 to `misc.pagination.maxRows`.
`$.limit`	Number	Specifies the maximum number of rows returned from a query. Values greater than the value of the `misc.pagination.maxRows` property, specified in the `defaults.xml`, is ignored.	`500`	`misc.pagination.maxRows`	Between 0 to `misc.pagination.maxRows`.
`$.binds`	Array	Specifies an array of objects specifying the bind information.	`"binds":[ { "name":"mybind1", "data_type":"NUMBER", "mode":"out" }, { "name":"mybind2", "data_type":"NUMBER", "value":7 } ]`	Not applicable	Not applicable
`$.binds[*].name`	String	Specifies the name of the bind, when you are using named notation.	`"mybind"`	Not applicable	Not applicable
`$.binds[*].index`	Number	Specifies the index of bind, when you are using positional notation.	`1`	Not applicable	`Between 1 to n`
`$.binds[*].data_type`	String	Specifies Oracle data type of the bind.	`"NUMBER"`	Not applicable	For more information, refer to Oracle Built-in Types
`$.binds[*].value`	Any value	Specifies the value of the bind.	`"value to insert"`	`null`	Can be one of the following data-types: Number String Array For more information, refer to Oracle Built-in Types
`$.binds[*].mode`	String	Specifies the mode in which the bind is used.	`"out"`	`"in"`	`[ "in" , "inout", "out" ]`
`$.binds[*].batch`	Boolean	Specifies whether or not you want to perform a batch bind. If you want to perform a batch bind, then set the value to `true`. If the value is set to `true`, then `$binds[*]` must consist of an array of values.	`true`	`false`	`[ true, false ]`
`$.binds[*].type_name`	String	Required when you are using `$binds[*].data_type = "PL/SQL TABLE"` Currently, only an empty string is accepted as the value.	`""`	Not applicable	Not applicable
`$.binds[*].type_subname`	String	Required when you are using `$binds[*].data_type = "PL/SQL TABLE"` Currently, only an empty string is accepted as the value.	`""`	Not applicable	Not applicable
`$.binds[*].type_components`	Array	Specifies an array of data types in the PL/SQL TABLE Required when you are using `$binds[*].data_type = "PL/SQL TABLE"`	`[{"data_type":"NUMBER"}]`	Not applicable	Not applicable
`$.binds[].type_components[].data_type`	String	Specifies Oracle data type of a column in the PL/SQL TABLE. Required when you are using `$binds[*].data_type = "PL/SQL TABLE"`	`"NUMBER"`	Not applicable	For more information, refer to Oracle Built-in Types

Response Specification

JSONPath	Data type	Description	Example Values	Possible values
`$.env`	Object	Specifies the information about the Oracle REST Data Services environment.	Not applicable	Not applicable
`$.env.defaultTimeZone`	String	Specifies the timezone in which Oracle REST Data Services server is running on.	`"Europe/London"`	Not applicable
`$.items`	Array	Specifies that there is one item for each statement executed.	Not applicable	Not applicable
`$.items[*].statementId`	Number	Specifies the sequence number of the statement.	`1`	Not applicable
`$.items[*].statementType`	String	Specifies the type of statement.	`"query"`	[ “query” , “dml”, “ddl”, “plsql” , “sqlplus” , “ignore”, “transaction-control”, “session-control”, “system-control”, “jdbc”, “other” ]
`$.items[*].statementPos`	Object	Specifies information about the position of a specified statement.	Not applicable	Not applicable
`$.items[*].statementPos.startLine`	Number	Specifies start line of the statement.	Not applicable	Not applicable
`$.items[*].statementPos.endLine`	Number	Specifies end line of the statement.	Not applicable	Not applicable
`$items[*].statementText`	String	Specifies the SQL statement to be executed.	`"select 1 from dual"`	Not applicable
`$items[*].statementText`	Array	Specifies batch DML statements can be specified using an array. One DML statement specified per string in an array.	`[ "insert into test1 values(1)","update test1 set col1=2" ]`	Not applicable
`$.items[*].response`	Array	Specifies array of Strings. The response generated when running the statement.	`[ "\n1 row inserted.\n\n" ]`	Not applicable
`$.items[*].result`	Number	Specifies the result generated when running the statement. For DML statements, this will be the number of rows affected.	`5`	Not applicable
`$.items[*].result`	Array	Specifies the result generated when running each of the batch statements. For DML statements, this will be the number of rows affected.	`[ 1, 1, 2 ]`	Not applicable
`$.items[*].resultSet`	Object	Specifies information about the result set generated from a query.	Not applicable	Not applicable
`$.items[*].resultSet.metadata`	Array	Specifies each object in the array provides information about the metadata of a column.	Not applicable	Not applicable
`$.items[].resultSet.metadata[].columnName`	String	Specifies the name of the column used in the Oracle Database.	Not applicable	Not applicable
`$.items[].resultSet.metadata[].jsonColumnName`	String	Specifies the name of the column used in `$.items[].resultSet.items[].`	Not applicable	Not applicable
`$.items[].resultSet.metadata[].columnTypeName`	String	Specifies the Oracle Database data type of the column.	Not applicable	Not applicable
`$.items[].resultSet.metadata[].precision`	Number	Specifies the precision of the column.	Not applicable	Not applicable
`$.items[].resultSet.metadata[].scale`	Number	Specifies the scale of the column.	Not applicable	Not applicable
`$.items[].resultSet.metadata[].isNullable`	Number	Specifies whether the column is nullable or not. 0, if the column is not nullable. 1, if the column is nullable.	Not applicable	Not applicable
`$.items[*].resultSet.items`	Array	Specifies the list of all rows returned in the result set.	Not applicable	Not applicable
`$.items[].resultSet.items[].<columnname>`	Any type	Specifies the value of a particular column and row in the result set.	Not applicable	Not applicable
`$.items[*].resultSet.hasMore`	Boolean	Specifies whether result set has more rows. Value is set to `true` if the result set has more rows, otherwise set to `false`. The rows in the result set depend on `misc.pagination.maxRows` value configured in `defaults.xml` file or as specified in the request.	`false`	[ true , false ]
`$.items[*].resultSet.count`	Number	Specifies the number of rows returned.	Not applicable	Not applicable
`$.items[*].resultSet.offset`	Number	Specifies the number of rows to offset the query result. This is used for pagination of the result set returned from a query.	`25`	Between 0 to `misc.pagination.maxRows`
`$.items[*].resultSet.limit`	Number	Specifies the maximum number of rows returned from a query. Values greater than `misc.pagination.maxRows` value specified in `defaults.xml` file are ignored.	`500`	Between 0 to `misc.pagination.maxRows`
`$.items[*].binds`	Array	Specifies an array of objects specifying the bind information.	`"binds":[ { "name":"mybind1", "data_type":"NUMBER", "mode":"out" }, { "name":"mybind2", "data_type":"NUMBER", "value":7 } ]`	Not applicable
`$.items[].binds[].name`	String	Specifies the name of the bind, when you are using named notation.	`"mybind"`	Not applicable
`$.items[].binds[].index`	Number	specifies iIndex of bind, when you are using positional notation.	`1`	`1 - n`
`$.items[].binds[].data_type`	String	Specifies the Oracle data type of the bind.	`"NUMBER"`	For more information, refer to Oracle Built-in Types
`$.items[].binds[].value`	Any type	Specifies the value of the bind.	`"value to insert"`	Can be one of the following data types: Number String Array For more information, refer to Oracle Built-in Types
`$.items[].binds[].result`	Any type	Specifies the result of an OUT bind.	Not applicable	Not applicable
`$.items[].binds[].mode`	String	Specifies the mode in which the bind is used.	`"out"`	`[ "in" , "inout", "out" ]`
`$.items[].binds[].batch`	Boolean	Specifies whether or not you want to perform a batch bind. If you want to perform a batch bind, then set the value to `true`. If a batch bind is to be performed, then the value is set to `true`. If the value is set to `true`, then `$binds[*]` value must be an array of values.	`true`	`[ true, false ]`
`$.items[].binds[].type_name`	String	Required when using `$binds[*].data_type = "PL/SQL TABLE"`. Currently, only an empty string is accepted as the value.	`""`	Not applicable
`$.items[].binds[].type_subname`	String	Required when using `$binds[*].data_type = "PL/SQL TABLE"`. Currently, only an empty string is accepted as the value.	`""`	Not applicable
`$.items[].binds[].type_components`	Array	Array of data types in the PL/SQL TABLE Required when using `$binds[*].data_type = "PL/SQL TABLE"`.	`[{"data_type":"NUMBER"}]`	Not applicable
`$.items[].binds[].type_components[*].data_type`	String	The Oracle data type of a column in the PL/SQL TABLE. Required when using `$binds[*].data_type = "PL/SQL TABLE"`	`"NUMBER"`	For more information, refer to Oracle Built-in Types

Supported SQL, SQL*Plus, and SQLcl Statements

This section lists all the supported SQL, SQL*Plus and SQLcl statements for REST-Enabled SQL service.

Topics

Supported SQL Statements
Supported PL/SQL Statements
Supported SQL*Plus Statements
Supported SQLcl Statements
Supported SQL Statements This section describes the SQL statements that the REST- Enabled SQL service supports.
Supported PL/SQL Statements The REST- Enabled SQL service supports PL/SQL statements and blocks.
[Supported SQLPlus Statements](rest-enabled-sql-service.html#GUID-CBD3C248-44F9-4C26-9319-87BF412D1CDD) This section lists all the SQLPlus statements that the REST- Enabled SQL service supports.
Supported SQLcl Statements This section lists the SQLcl statements that the REST- Enabled SQL service supports.

Supported SQL Statements

This section describes the SQL statements that the REST- Enabled SQL service supports.

REST- Enabled SQL service supports all SQL commands. If the specified Oracle Database schema has the appropriate privileges, then you can run them. Oracle REST Data Services makes all queries into in-line views before execution to provide pagination support. Queries are made in-line irrespective of the format in which you provide the query. All the other nonquery SQL statements are executed as they are.

In-line views have the following limitations:

All column names in a query must be unique because the views and in-line views cannot have ambiguous column names.
Cursor expressions are not displayed in view or in-line views.
WITH FUNCTION clause is not supported in in-line views.

Related Topics

SQL_statements_ref

Supported PL/SQL Statements

The REST- Enabled SQL service supports PL/SQL statements and blocks.

Example 15-7 PL/SQL Statement

DECLARE v_message VARCHAR2(100) := 'Hello World';
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (v_message);
  END LOOP;
END;
/

Related Topics

plsql_block

Supported SQL*Plus Statements

This section lists all the SQL*Plus statements that the REST- Enabled SQL service supports.

REST- Enabled SQL service supports most of the SQLPlus statements except those statements that are related to formatting. The specific Oracle Database schema must have the appropriate privileges to run the SQLPlus statemments.

The following is a list of supported SQL*Plus statements:

SET system_variable value Note: system_variable and value represent one of the clauses described in Set System Variables section.

/ (slash)
DEF[INE] [variable] | [variable = text]
DESC[RIBE] {[schema.]object[@connect_identifier]}
EXEC[UTE] statement
HELP | ? [topic]
PRINT [variable ...]
PRO[MPT] [text]
REM[ARK]
SHO[W] [option]
TIMI[NG] [START text | SHOW | STOP]
UNDEF[INE] variable ...
VAR[IABLE] [variable [type][=value]]
Set System Variables
Show System Variables This section lists the possible values for option which is either a term or a clause used in the SHO[W] option command.

Related Topics

sqlplus_commands

Set System Variables

The following is a list of possible values for system_variable and value:

Note: The command SET CMDS[EP] {; | c | ON | OFF} is obsolete.

SET APPI[NFO]{ON | OFF | text}
SET AUTOP[RINT] {ON | OFF}
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
SET CMDS[EP] {; | c | ON | OFF}
SET COLINVI[SIBLE] [ON | OFF]
SET CON[CAT] {. | c | ON | OFF}
SET COPYC[OMMIT] {0 | n}
SET DEF[INE] {& | c | ON | OFF}
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
SET ECHO {ON | OFF}
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
SET ESC[APE] {\ | c | ON | OFF}
SET FEED[BACK] {6 | n | ON | OFF | ONLY}]
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
SET SHOW[MODE] {ON | OFF}
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLP[ROMPT] {SQL> | text}
SET TI[ME] {ON | OFF}
SET TIMI[NG] {ON | OFF}
SET VER[IFY] {ON | OFF}

Related Topics

set-system_var_summary

Show System Variables

This section lists the possible values for option which is either a term or a clause used in the SHO[W] option command.

The following is a list of possible values for the option variable:

Note: The commands SHOW CMDSEP and SHOW DESCR[IBE] are obsolete.

SHOW system_variable
SHOW EDITION
SHOW ERR[ORS] [ { ANALYTIC VIEW | ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]
SHOW PDBS
SHOW SGA
SHOW SQLCODE
SHOW COLINVI[SIBLE]
SHOW APPIN[FO]
SHOW AUTOT[RACE]
SHOW BINDS
SHOW BLO[CK TERMINATOR]
SHOW CMDSEP
SHOW COPYTYPECHECK
SHOW COPYCOMMIT
SHOW DEFINE
SHOW DEFINES
SHOW DESCR[IBE]
SHOW ECHO
SHOW EDITION
SHOW ERRORL[OGGING]
SHOW ESC[APE]
SHOW FEEDBACK
SHOW CONCAT
SHOW SHOW[MODE]
SHOW RECYC[LEBIN]
SHOW RELEASE
SHOW SQLBL[ANKLINES]
SHOW SCAN
SHOW SERVEROUT[PUT]
SHOW SPACE
SHOW TABLES
SHOW TIMI[NG]
SHOW USER
SHOW VER[IFY]
SHOW XQUERY

Related Topics

show_command

Supported SQLcl Statements

This section lists the SQLcl statements that the REST- Enabled SQL service supports.

REST- Enabled SQL service supports some of the SQLcl statements. The specific Oracle Database schema must have the appropriate privileges to run the SQLcl statements.

The following is a list of supported SQLcl statements:

CTAS
DDL
SET DDL

REST-Enabled SQL Service and MySQL Database

This section describes an ORDS feature that is supported only with MySQL databases running on Oracle Cloud Infrastructure.

You can use the REST-Enabled SQL Service with MySQL database 8.0 or later, hosted in Oracle Cloud infrastructure. For MySQL database, you do not need to install any ORDS-specific software, but must specify the configuration details about how to connect to the database over JDBC through a connection pool. The ORDS distribution includes the MySQL connector/J JDBC driver.

The endpoints for REST-Enabled SQL Service and the corresponding export service end with /_/sql and /_/sql/export respectively.

ORDS returns data in a well-formed JSON structure. The MySQL data types JSON and GEOMETRY are returned as a JSON object in the response. Any binary data, such as BLOB data types, is returned as a BASE64 encoded string. The supported export format types are CSV, HTML, JSON, and XML.

Examples This section describes how to configure a sample MySQL database and perform a few common operations.

Examples

This section describes how to configure a sample MySQL database and perform a few common operations.

The examples described in this section refers to the MySQL sakila sample database. The connection pool called mysql is configured to connect to the MySQL database instance with db.credentials. The source is set to REQUEST and MySQL database user in this example is francis and the password is set as frank.

Example 15-8 Script

This example shows how to list the schemas in the database instance.

Request

curl --user francis:frank --request POST 'http://localhost:8080/ords/mysql/_/sql' \
--header 'Content-Type: application/sql' \
--data 'show databases'

Response

{
   "env" : {
      "defaultTimeZone" : "UTC"
   },
   "items" : [
      {
         "response" : [
            "Database                                                         ",
            "\n",
            "---------------------------------------------------------------- ",
            "\n",
            "information_schema                                               ",
            "\n",
            "mysql                                                            ",
            "\n",
            "performance_schema                                               ",
            "\n",
            "sakila                                                           ",
            "\n",
            "sys                                                              ",
            "\n"
         ],
         "result" : 0,
         "statementId" : 1,
         "statementPos" : {
            "endLine" : 1,
            "startLine" : 1
         },
         "statementText" : "show databases",
         "statementType" : "sqlplus"
      }
   ]
}

Example 15-9 Query

This example shows how to query the film table in the sakila schema, using bind variables and limit in the query. Note: All bind variables are VARCHAR data type and are mapped to the appropriate data type for the referenced column.

Request

curl --user francis:frank --request POST 'http://localhost:8080/ords/mysql/_/sql' \
--header 'Content-Type: application/json' \
--data-raw '{
    "statementText": "select film.title, film.release_year from sakila.film film where film.rating = :var1 and film.release_year between :lowDate and :highDate order by release_year",
    "offset": 0,
    "limit": 5,
    "binds": [
        {
            "name": "var1",
            "data_type": "VARCHAR",
            "value": "G"
        },
        {
            "name": "highDate",
            "data_type": "VARCHAR",
            "value": "2006-01-01T00:00:00Z"
        },
        {
            "name": "lowDate",
            "data_type": "VARCHAR",
            "value": "2005-01-01T00:00:00Z"
        }
    ]
}'

Response

{
   "env" : {
      "defaultTimeZone" : "Europe/Dublin"
   },
   "items" : [
      {
         "binds" : [
            {
               "data_type" : "VARCHAR",
               "name" : "var1",
               "value" : "G"
            },
            {
               "data_type" : "VARCHAR",
               "name" : "highDate",
               "value" : "2006-01-01T00:00:00Z"
            },
            {
               "data_type" : "VARCHAR",
               "name" : "lowDate",
               "value" : "2005-01-01T00:00:00Z"
            }
         ],
         "response" : [],
         "result" : 0,
         "resultSet" : {
            "count" : 5,
            "hasMore" : true,
            "items" : [
               {
                  "release_year" : "2006-01-01T00:00:00Z",
                  "title" : "ACE GOLDFINGER"
               },
               {
                  "release_year" : "2006-01-01T00:00:00Z",
                  "title" : "AFFAIR PREJUDICE"
               },
               {
                  "release_year" : "2006-01-01T00:00:00Z",
                  "title" : "AFRICAN EGG"
               },
               {
                  "release_year" : "2006-01-01T00:00:00Z",
                  "title" : "ALAMO VIDEOTAPE"
               },
               {
                  "release_year" : "2006-01-01T00:00:00Z",
                  "title" : "AMISTAD MIDSUMMER"
               }
            ],
            "limit" : 5,
            "metadata" : [
               {
                  "columnClassName" : "java.lang.String",
                  "columnName" : "title",
                  "columnTypeName" : "VARCHAR",
                  "isNullable" : 0,
                  "jsonColumnName" : "title",
                  "precision" : 128,
                  "scale" : 0
               },
               {
                  "columnClassName" : "java.sql.Date",
                  "columnName" : "release_year",
                  "columnTypeName" : "YEAR",
                  "isNullable" : 1,
                  "jsonColumnName" : "release_year",
                  "precision" : 4,
                  "scale" : 0
               }
            ],
            "offset" : 0
         },
         "statementId" : 1,
         "statementPos" : {
            "endLine" : 2,
            "startLine" : 1
         },
         "statementText" : "select film.title, film.release_year from sakila.film film where film.rating = :var1 and film.release_year between :lowDate and :highDate order by release_year",
         "statementType" : "query"
      }
   ]
}

Example 15-10 Export

This example shows how to export the rows from the film table in CSV format to a file film.csv.

Request

curl --user francis:frank --location --output film.csv --request
        POST 'http://localhost:8080/ords/mysql/_/sql/export' --header 'Content-Type: application/x-www-form-urlencoded' --data-urlencode 'data={"statementText":"select * from sakila.film",
        "formatDetails":{"format":"CSV", "header": true, "lineTerminator":
        "\n"}}'