8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : REST Enabled SQL

The REST Enabled SQL functionality introduced in Oracle REST Data Services (ORDS) 17.4 allows REST calls to send DML, DDL and scripts to any REST enabled schema by exposing the same SQL engine used in SQL Developer and SQLcl.

Related articles.

Assumptions

This article assumes the following.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO testuser1;

Enable ORDS

There are a number of steps that are necessary for ORDS to publish REST web services from a database user.

Installing ORDS configures a default database connection called "apex", available from the base URL "/ords/". We could use this existing database connection, but instead we will create a new database connection. If you already have a connection you are happy to use ignore the following database connection and URL mapping ORDS commands.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Enter the name of the database server [ol7-122.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Retrieving information.
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Sep 09, 2017 10:46:06 AM
INFO: Updated configurations: pdb1_pu
Sep 09, 2017 10:46:06 AM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 17.3.0.248.08.45 is installed.
$

We create a URL mapping to the new database connection.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Sep 09, 2017 10:47:07 AM
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

Web services from the connection can now be referenced using the following base URL.

http://ol7-122.localdomain:8080/ords/pdb1/

The final step is to enable REST web services for the test schema itself. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name as we have done here.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'testuser1',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Web services from the schema can now be referenced using the following base URL.

http://ol7-122.localdomain:8080/ords/pdb1/testuser1/

We are now ready to start.

Enable REST Enabled SQL

So far we have enabled the conventional functionality available in previous versions. To enable the REST Enabled SQL functionality we must amend the "default.xml" file, which in this case is located in the "/u01/ords/conf/ords" directory. This entry is the on/off switch for this functionality.

<entry key="restEnabledSql.active">true</entry>

By default the functionality is only available over HTTPS, which is very sensible since the payload contains credentials, but we can allow HTTP access for demos by using the following setting. Without this you will see an error message saying, "This resource must only be accessed over HTTPS. It was incorrectly accessed using HTTP".

<entry key="security.verifySSL">false</entry>

The following option can be used to stop us from killing the system by pulling out too many rows.

<entry key="jdbc.maxRows">1000</entry>

You will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode.

With the above configuration in place, including the HTTP access, we can make REST Enabled SQL calls to the TESTUSER1 user with the following URL.

http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql

A direct call to this URL from a browser will result in a "405 Method Not Allowed" message.

Basic Call Explained

In its simplest form a call to the REST Enabled SQL requires the following information.

URL        : http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql
Method     : POST
Header     : Content-Type: application/sql
Credentials: USERNAME:password
Payload    : <<Your SQL goes here>>

When using schema authentication the username for the credential must be in uppercase or the authentication will fail. The password is case sensitive.

So a basic call using curl would look like either of these, depending on which forms of the flags you prefer to use.

$ curl -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'SELECT * FROM dual;'

$ curl --request "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       --header "Content-Type: application/sql" \
       --user TESTUSER1:testuser1 \
       --data $'SELECT * FROM dual;'

I will add the "-s" option in the call to curl and append " | python -mjson.tool" to pretty-print the JSON. This isn't necessary for the call to work.

If you are using HTTPS with a self-signed certificate, remember to use the "-k" option. To show the header information, use the "-i" option.

$ curl -i -k \
       -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'SELECT * FROM dual;'

Run a Script

Create a file called "/tmp/tables.sql" with the following contents.

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

We can now make a call passing the file contents as the data using the "-d @" or "--data @" option. The output is minified, which makes it quite difficult to read, so I've formatted it for the sake of this article. You can see each statement is run separately, with its output presented in the "response" element.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d @/tmp/tables.sql | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [
                "\nTable DEPT created.\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "CREATE TABLE DEPT (  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,  DNAME VARCHAR2(14),  LOC VARCHAR2(13))",
            "statementType": "ddl"
        },
        {
            "response": [
                "\nTable EMP created.\n\n"
            ],
            "result": 0,
            "statementId": 2,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "CREATE TABLE EMP (  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,  ENAME VARCHAR2(10),  JOB VARCHAR2(9),  MGR NUMBER(4),  HIREDATE DATE,  SAL NUMBER(7,2),  COMM NUMBER(7,2),  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT)",
            "statementType": "ddl"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 3,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK')",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 4,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS')",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 5,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO DEPT VALUES (30,'SALES','CHICAGO')",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 6,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON')",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 7,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 8,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 9,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 10,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 11,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 12,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 13,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 14,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 15,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 16,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 17,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 18,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 19,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20)",
            "statementType": "dml"
        },
        {
            "response": [
                "\n1 row inserted.\n\n"
            ],
            "result": 1,
            "statementId": 20,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10)",
            "statementType": "dml"
        },
        {
            "response": [
                "\nCommit complete.\n\n"
            ],
            "result": 1,
            "statementId": 21,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "COMMIT",
            "statementType": "transaction-control"
        }
    ]
}
$

Run Query

We have already seen this in the basic call section, but here is an example of a single query. Notice we have had to escape any single quotes.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'SELECT d.dname AS "department",
                   LISTAGG(e.ename, \',\') WITHIN GROUP (ORDER BY e.ename) AS employees
            FROM   dept d
            JOIN   emp e ON e.deptno = d.deptno
            GROUP BY d.dname
            ORDER BY d.dname;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [],
            "result": 0,
            "resultSet": {
                "count": 3,
                "hasMore": false,
                "items": [
                    {
                        "department": "ACCOUNTING",
                        "employees": "CLARK,KING,MILLER"
                    },
                    {
                        "department": "RESEARCH",
                        "employees": "ADAMS,FORD,JONES,SCOTT,SMITH"
                    },
                    {
                        "department": "SALES",
                        "employees": "ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD"
                    }
                ],
                "limit": 1000,
                "metadata": [
                    {
                        "columnName": "department",
                        "columnTypeName": "VARCHAR2",
                        "isNullable": 1,
                        "jsonColumnName": "department",
                        "precision": 14,
                        "scale": 0
                    },
                    {
                        "columnName": "EMPLOYEES",
                        "columnTypeName": "VARCHAR2",
                        "isNullable": 1,
                        "jsonColumnName": "employees",
                        "precision": 4000,
                        "scale": 0
                    }
                ],
                "offset": 0
            },
            "statementId": 1,
            "statementPos": {
                "endLine": 6,
                "startLine": 1
            },
            "statementText": "SELECT d.dname AS \"department\",\n                   LISTAGG(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) AS employees\n            FROM   dept d\n            JOIN   emp e ON e.deptno = d.deptno\n            GROUP BY d.dname\n            ORDER BY d.dname",
            "statementType": "query"
        }
    ]
}
$

It should be pretty obvious from the script example, you can process multiple statements in a single call, with each statement being displayed as a separate element.

Notice we also have access to objects that are not owned by, but accessible by the user.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'SELECT COUNT(*) AS amount FROM all_objects;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [],
            "result": 0,
            "resultSet": {
                "count": 1,
                "hasMore": false,
                "items": [
                    {
                        "amount": 57025
                    }
                ],
                "limit": 1000,
                "metadata": [
                    {
                        "columnName": "AMOUNT",
                        "columnTypeName": "NUMBER",
                        "isNullable": 1,
                        "jsonColumnName": "amount",
                        "precision": 0,
                        "scale": -127
                    }
                ],
                "offset": 0
            },
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "SELECT COUNT(*) AS amount FROM all_objects",
            "statementType": "query"
        }
    ]
}
$

Run DML

We can string together one or more DML statements, each ending with a ";". In the script example we did this using a file, but we can include the commands inline, provided we escape any necessary characters.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'UPDATE emp
            SET    sal = sal + 1
            WHERE  empno > 1000;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [
                "\n14 rows updated.\n\n"
            ],
            "result": 14,
            "statementId": 1,
            "statementPos": {
                "endLine": 3,
                "startLine": 1
            },
            "statementText": "UPDATE emp\n            SET    sal = sal + 1\n            WHERE  empno > 1000",
            "statementType": "dml"
        }
    ]
}
$

Run DDL

We can string together one or more DDL statements, each ending with a ";". In the script example we did this using a file, but we can include the commands inline, provided we escape any necessary characters.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'CREATE TABLE t1 (
              id NUMBER
            );' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [
                "\nTable T1 created.\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 3,
                "startLine": 1
            },
            "statementText": "CREATE TABLE t1 (\n              id NUMBER\n            )",
            "statementType": "ddl"
        }
    ]
}
$

PL/SQL

Since this functionality uses the same SQL engine as SQL Developer and SQLcl, it can perform a subset of the commands available from them, provided it doesn't make reference to the host.

Anonymous Block

The following example uses an anonymous block to call the DBMS_STATS package to gather statistics for the schema.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'BEGIN DBMS_STATS.gather_schema_stats(\'TESTUSER1\'); END;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [
                "\nPL/SQL procedure successfully completed.\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 2,
                "startLine": 1
            },
            "statementText": "BEGIN DBMS_STATS.gather_schema_stats('TESTUSER1'); END;",
            "statementType": "plsql"
        }
    ]
}
$

The following example uses EXEC (or EXECUTE) to call the DBMS_STATS package to gather statistics for the schema.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'EXEC DBMS_STATS.gather_schema_stats(\'TESTUSER1\');' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [
                "\nPL/SQL procedure successfully completed.\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "EXEC DBMS_STATS.gather_schema_stats('TESTUSER1')",
            "statementType": "sqlplus"
        }
    ]
}
$

Procedures Using DBMS_OUTPUT

The following example runs an anonymous block which makes a call to the DBMS_OUTPUT package. Provided the script enables server output, the response will contain the output.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'SET SERVEROUTPUT ON
            DECLARE
              l_date VARCHAR2(20);
            BEGIN
              l_date := TO_CHAR(SYSDATE);

              DBMS_OUTPUT.put_line(\'l_date=\' || l_date);
            END;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "SET SERVEROUTPUT ON",
            "statementType": "sqlplus"
        },
        {
            "dbmsOutput": "l_date=09-SEP-17\n\n",
            "response": [
                "l_date=09-SEP-17\n\n",
                "\nPL/SQL procedure successfully completed.\n\n"
            ],
            "result": 0,
            "statementId": 2,
            "statementPos": {
                "endLine": 9,
                "startLine": 2
            },
            "statementText": "            DECLARE\n              l_date VARCHAR2(20);\n            BEGIN\n              l_date := TO_CHAR(SYSDATE);\n\n              DBMS_OUTPUT.put_line('l_date=' || l_date);\n            END;",
            "statementType": "plsql"
        }
    ]
}
$

Procedures Using the PL/SQL Web Toolkit

The following procedure includes a call to the HTP package.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc4 AS
BEGIN
  HTP.print('I wonder if this will work.');
END;
/

If we execute the procedure it results in an error.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'EXEC proc4;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "errorCode": 6502,
            "errorColumn": 0,
            "errorDetails": "ORA-06502: PL/SQL: numeric or value error\nORA-06512: at \"SYS.OWA_UTIL\", line 359\nORA-06512: at \"SYS.HTP\", line 1418\nORA-06512: at \"SYS.HTP\", line 1494\nORA-06512: at \"SYS.HTP\", line 1809\nORA-06512: at \"TESTUSER1.PROC4\", line 3\nORA-06512: at line 1",
            "errorLine": 1,
            "response": [
                "\nError starting at line : 1 in command -\nBEGIN proc4; END;\nError report -\nORA-06502: PL/SQL: numeric or value error\nORA-06512: at \"SYS.OWA_UTIL\", line 359\nORA-06512: at \"SYS.HTP\", line 1418\nORA-06512: at \"SYS.HTP\", line 1494\nORA-06512: at \"SYS.HTP\", line 1809\nORA-06512: at \"TESTUSER1.PROC4\", line 3\nORA-06512: at line 1\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "EXEC proc4",
            "statementType": "sqlplus"
        }
    ]
}
$

Procedures Using Implicit Statement Results

The following procedure uses the implicit statement results functionality to return a result set.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc5 AS
  l_cursor  SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT *
    FROM   emp;

  DBMS_SQL.RETURN_RESULT(l_cursor);
END;
/

When the procedure is executed the result set is displayed in the response.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'EXEC proc5;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [
                "\nPL/SQL procedure successfully completed.\n\n",
                "ResultSet #1\n\n",
                "\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- ---------- --------- ---------- --------- ---------- ----------\n    DEPTNO\n----------\n      7369 SMITH      CLERK           7902 17-DEC-80        801            \n        20\n\n",
                "      7499 ALLEN      SALESMAN        7698 20-FEB-81       1601        300 \n        30\n\n",
                "      7521 WARD       SALESMAN        7698 22-FEB-81       1251        500 \n        30\n\n",
                "\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- ---------- --------- ---------- --------- ---------- ----------\n    DEPTNO\n----------\n      7566 JONES      MANAGER         7839 02-APR-81       2976            \n        20\n\n",
                "      7654 MARTIN     SALESMAN        7698 28-SEP-81       1251       1400 \n        30\n\n",
                "      7698 BLAKE      MANAGER         7839 01-MAY-81       2851            \n        30\n\n",
                "\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- ---------- --------- ---------- --------- ---------- ----------\n    DEPTNO\n----------\n      7782 CLARK      MANAGER         7839 09-JUN-81       2451            \n        10\n\n",
                "      7788 SCOTT      ANALYST         7566 19-APR-87       3001            \n        20\n\n",
                "      7839 KING       PRESIDENT            17-NOV-81       5001            \n        10\n\n",
                "\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- ---------- --------- ---------- --------- ---------- ----------\n    DEPTNO\n----------\n      7844 TURNER     SALESMAN        7698 08-SEP-81       1501          0 \n        30\n\n",
                "      7876 ADAMS      CLERK           7788 23-MAY-87       1101            \n        20\n\n",
                "      7900 JAMES      CLERK           7698 03-DEC-81        951            \n        30\n\n",
                "\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- ---------- --------- ---------- --------- ---------- ----------\n    DEPTNO\n----------\n      7902 FORD       ANALYST         7566 03-DEC-81       3001            \n        20\n\n",
                "      7934 MILLER     CLERK           7782 23-JAN-82       1301            \n        10\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "EXEC proc5",
            "statementType": "sqlplus"
        }
    ]
}
$

JSON Documents

Rather than a plain SQL statement or script, a JSON document can be sent as a payload to REST Enabled SQL. Create a procedure with some parameters.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc6 (
  p_empno   IN  emp.empno%TYPE,
  p_ename  OUT  emp.ename%TYPE
) AS
BEGIN
  SELECT ename
  INTO   p_ename
  FROM   emp
  WHERE  empno = p_empno;
END;
/

Create a file called "/tmp/test1.json" with the following contents.

{
  "statementText": "EXEC proc6(:p_empno, :p_ename)",
  "binds":[
    {"name":"p_empno","data_type":"NUMBER","value":7900},
    {"name":"p_ename","data_type":"VARCHAR2","mode":"out"}
  ]
}

We can now post this JSON document to the service using the "Content-Type: application/json" header.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/json" \
       -u TESTUSER1:testuser1 \
       -d @/tmp/test1.json | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "binds": [
                {
                    "data_type": "NUMBER",
                    "name": "p_empno",
                    "value": 7900
                },
                {
                    "data_type": "VARCHAR2",
                    "mode": "out",
                    "name": "p_ename",
                    "result": "JAMES"
                }
            ],
            "response": [
                "\nPL/SQL procedure successfully completed.\n\n"
            ],
            "result": 0,
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "EXEC proc6(:p_empno, :p_ename)",
            "statementType": "sqlplus"
        }
    ]
}
$

For queries the offset and limit elements can used to page through data. Create a file called "/tmp/test2.json" with the following contents.

{ 
  "statementText": "SELECT ename FROM emp WHERE deptno = :p_deptno ORDER BY ename;",
  "offset": 2, 
  "limit": 2,
  "binds":[
    {"name":"p_deptno","data_type":"NUMBER","value":20}
  ] 
}

Make the call using the new JSON document.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/json" \
       -u TESTUSER1:testuser1 \
       -d @/tmp/test2.json | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "binds": [
                {
                    "data_type": "NUMBER",
                    "name": "p_deptno",
                    "value": 20
                }
            ],
            "response": [],
            "result": 0,
            "resultSet": {
                "count": 2,
                "hasMore": true,
                "items": [
                    {
                        "ename": "JONES"
                    },
                    {
                        "ename": "SCOTT"
                    }
                ],
                "limit": 2,
                "metadata": [
                    {
                        "columnName": "ENAME",
                        "columnTypeName": "VARCHAR2",
                        "isNullable": 1,
                        "jsonColumnName": "ename",
                        "precision": 10,
                        "scale": 0
                    }
                ],
                "offset": 2
            },
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "SELECT ename FROM emp WHERE deptno = :p_deptno ORDER BY ename",
            "statementType": "query"
        }
    ]
}
$

First Party Authentication (Basic Authentication)

All the examples so far have used schema authentication, but we can use basic authentication to access the services provided the user has the SQL Developer role. Create these with care because these users can access all schemas that are REST enabled.

Create a user with the SQL Developer role.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war user tim_hall "SQL Developer"
Enter a password for user tim_hall:
Confirm password for user tim_hall:
Sep 09, 2017 12:49:29 PM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: tim_hall in file: /u01/ords/conf/ords/credentials
$

We can now make a call using basic authentication rather than the schema credentials. Remember we specify the schema alias in the URL, which maps to the schema, so the REST Enabled SQL functionality knows which schema to run the query under.

$ curl -s -X "POST" "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/_/sql" \
       -H "Content-Type: application/sql" \
       -u tim_hall:OraPasswd1 \
       -d $'SELECT * FROM dual;' | python -mjson.tool
{
    "env": {
        "defaultTimeZone": "Europe/London"
    },
    "items": [
        {
            "response": [],
            "result": 0,
            "resultSet": {
                "count": 1,
                "hasMore": false,
                "items": [
                    {
                        "dummy": "X"
                    }
                ],
                "limit": 1000,
                "metadata": [
                    {
                        "columnName": "DUMMY",
                        "columnTypeName": "VARCHAR2",
                        "isNullable": 1,
                        "jsonColumnName": "dummy",
                        "precision": 1,
                        "scale": 0
                    }
                ],
                "offset": 0
            },
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "SELECT * FROM dual",
            "statementType": "query"
        }
    ]
}
$

Comments

Here are some comments about this functionality.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.