📋Steps in this guide1/18

Oracle REST Data Services (ORDS) : REST Enabled SQL

REST Enabled SQL allows REST client to send DML, DDL and scripts to any REST enabled schema without additional web service configuration.

oracle miscconfigurationintermediate

by OracleDba

13 views

Assumptions

This article assumes the following.

Create a Test Database User

We need a new database user for our testing.

Code/Command (click line numbers to comment):

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

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. In this example we've used a schema alias of "hr". Web services from the schema can now be referenced using the following base URLs. We are now ready to start.

Code/Command (click line numbers to comment):

CONN testuser1/testuser1@pdb1

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

http://localhost:8080/ords/hr/
https://localhost:8443/ords/hr/

Enable REST Enabled SQL (ORDS Version 22.1 Onward)

So far we have enabled the conventional functionality available in previous versions. To enable the REST Enabled SQL functionality we must set the attribute. This is the on/off switch for this functionality. 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 we will see an error message saying, "This resource must only be accessed over HTTPS. It was incorrectly accessed using HTTP". The following option can be used to stop us from killing the system by pulling out too many rows. We 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 user with the following URL. A direct call to this URL from a browser will result in a "405 Method Not Allowed" message.

Code/Command (click line numbers to comment):

export ORDS_HOME=/u01/ords
export ORDS_CONFIG=/u01/config/ords
export PATH=${ORDS_HOME}/bin:${PATH}

ords --config ${ORDS_CONFIG} config set restEnabledSql.active true

ords --config ${ORDS_CONFIG} config set security.verifySSL false

ords --config ${ORDS_CONFIG} config set misc.pagination.maxRows 1000

http://localhost:8080/ords/hr/_/sql
https://localhost:8443/ords/hr/_/sql

Enable REST Enabled SQL (ORDS Versions 17.4 to 21.4)

So far we have enabled the conventional functionality available in previous versions. To enable the REST Enabled SQL functionality we must amend the "defaults.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. This can be done manually, or with the following command. 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". The following option can be used to stop us from killing the system by pulling out too many rows. 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 user with the following URL. A direct call to this URL from a browser will result in a "405 Method Not Allowed" message.

Code/Command (click line numbers to comment):

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

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war set-property restEnabledSql.active true

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

<entry key="misc.pagination.maxRows">1000</entry>

http://localhost:8080/ords/hr/_/sql
https://localhost:8443/ords/hr/_/sql

Basic Call Explained

In its simplest form a call to the REST Enabled SQL requires the following information. In the early releases, when using schema authentication the username for the credential had to be in uppercase or the authentication will fail. This is no longer the case in later releases. The password is case sensitive. So a basic call using would look like either of these, depending on which forms of the flags you prefer to use. I will add the "-s" option in the call to 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.

Code/Command (click line numbers to comment):

URL        : https://localhost:8443/ords/hr/_/sql
Method     : POST
Header     : Content-Type: application/sql
Credentials: USERNAME:password
Payload    : <<Your SQL goes here>>

$ curl -X "POST" "https://localhost:8443/ords/hr/_/sql" \
       -H "Content-Type: application/sql" \
       -u TESTUSER1:testuser1 \
       -d $'SELECT * FROM dual;'

$ curl --request "POST" "https://localhost:8443/ords/hr/_/sql" \
       --header "Content-Type: application/sql" \
       --user TESTUSER1:testuser1 \
       --data $'SELECT * FROM dual;'

$ curl -i -k \
       -X "POST" "https://localhost:8443/ords/hr/_/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. 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.

Code/Command (click line numbers to comment):

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;

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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. 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.

Code/Command (click line numbers to comment):

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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"
        }
    ]
}
$

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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.

Code/Command (click line numbers to comment):

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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.

Code/Command (click line numbers to comment):

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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 package to gather statistics for the schema. The following example uses (or ) to call the package to gather statistics for the schema.

Code/Command (click line numbers to comment):

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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"
        }
    ]
}
$

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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 package. Provided the script enables server output, the response will contain the output.

Code/Command (click line numbers to comment):

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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 package. If we execute the procedure it results in an error.

Code/Command (click line numbers to comment):

CONN testuser1/testuser1@pdb1

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

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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. When the procedure is executed the result set is displayed in the response.

Code/Command (click line numbers to comment):

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;
/

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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. Create a file called "/tmp/test1.json" with the following contents. We can now post this JSON document to the service using the "Content-Type: application/json" header. For queries the and elements can used to page through data. Create a file called "/tmp/test2.json" with the following contents. Make the call using the new JSON document.

Code/Command (click line numbers to comment):

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;
/

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

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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"
        }
    ]
}
$

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

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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. 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.

Code/Command (click line numbers to comment):

$
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
$

$
curl -s -k -X "POST" "https://localhost:8443/ords/hr/_/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. - As a PL/SQL programmer I would always recommend presenting proper PL/SQL APIs as web services, rather than letting applications call SQL over a web service. Web services should present logical units of work. Having said that, if you need this functionality it's good that it now exists. - In its current form this functionality is enabled/disabled for all REST enabled schemas in the database. Hopefully future versions will allow this to be more granular, with the switch possible at schema and object level. - The call requires either schema (database) authentication or basic authentication using an ORDS user with the SQL Developer role, so just because a schema is enabled, it doesn't mean everyone will have access to it. - The switch to enabled/disabled this functionality is a server setting. It would be nice if this were part of the ORDS package, so developers could control it, rather than needing intervention on the infrastructure. For more information see: - Oracle REST Data Services Beta Documentation Release 17.4 - About REST Enabled SQL Service - Oracle REST Data Services (ORDS) : REST Enabled SQL - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!