1. Developer's Guide
  2. REST-Enabled SQL Service

12 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 SQL*Net (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, SQL*Plus, and SQLcl statements against the REST-enabled Oracle Database schema.

Topics:

12.1 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.

  • SQL*Net (or Net8): SQL*Net is the networking software of Oracle that enables remote data access between programs and Oracle Database.

Parent topic: REST-Enabled SQL Service

12.2 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.

Parent topic: REST-Enabled SQL Service

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

Parent topic: REST-Enabled SQL Service

12.4 Getting Started with the REST-Enabled SQL Service

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

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.

Topics:

Parent topic: REST-Enabled SQL Service

12.4.1 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

Parent topic: Getting Started with the REST-Enabled SQL Service

12.4.2 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.

Parent topic: Getting Started with the REST-Enabled SQL Service

12.4.3 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

Parent topic: Getting Started with the REST-Enabled SQL Service

12.5 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:

Parent topic: REST-Enabled SQL Service

12.5.1 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

    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"

Parent topic: REST-Enabled SQL Service Examples

12.5.1.1 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.

12.5.1.2 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
            }
        }
    ]
}
12.5.1.3 Using Multiple SQL Statements

You can run one or more statements in each POST request. Statements are separated similar to Oracle Database SQL*Plus script syntax, such as, end of line for SQL*Plus 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
        }
    ]
}

12.5.2 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:

Parent topic: REST-Enabled SQL Service Examples

12.5.2.1 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
            }
        }
    ]
}
12.5.2.2 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
            }
        }
    ]
}
12.5.2.3 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
            }
        }
    ]
}
12.5.2.4 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 12-2 Binds in POST Request

File: binds.json
{
 "statementText": "CREATE PROCEDURE TEST_OUT_PARAMETER (V_PARAM_IN INT IN, V_PARAM_OUT INT OUT) 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 12-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
                    ]
                }
            ]
        }
    ]
}
12.5.2.5 Specifying Batch Statements in a POST Request

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

Example 12-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 12-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"
                    ]
                }
            ]
        }
    ]
}

12.5.3 Example POST Request with DATE and TIMESTAMP Format

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

Parent topic: REST-Enabled SQL Service Examples

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

}

Parent topic: REST-Enabled SQL Service Examples

12.6 REST-Enabled SQL Request and Response Specifications

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

Parent topic: REST-Enabled SQL Service

12.6.1 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

Parent topic: REST-Enabled SQL Request and Response Specifications

12.6.2 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[*].<columnname>

  

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

Parent topic: REST-Enabled SQL Request and Response Specifications

12.7 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

Parent topic: REST-Enabled SQL Service

12.7.1 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

Parent topic: Supported SQL, SQL*Plus, and SQLcl Statements

12.7.2 Supported PL/SQL Statements

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

Example 12-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

Parent topic: Supported SQL, SQL*Plus, and SQLcl Statements

12.7.3 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 SQL*Plus statements except those statements that are related to formatting. The specific Oracle Database schema must have the appropriate privileges to run the SQL*Plus 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]]

Related Topics

Parent topic: Supported SQL, SQL*Plus, and SQLcl Statements

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

12.7.3.2 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

Parent topic: Supported SQL*Plus Statements

12.7.4 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

Parent topic: Supported SQL, SQL*Plus, and SQLcl Statements

12.8 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.

Parent topic: REST-Enabled SQL Service

12.8.1 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 12-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 12-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 12-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"}}'

Parent topic: REST-Enabled SQL Service and MySQL Database