8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Assumptions
- Create a Test Database User
- Enable ORDS
- Enable REST Enabled SQL (ORDS Version 22.1 Onward)
- Enable REST Enabled SQL (ORDS Versions 17.4 to 21.4)
- Basic Call Explained
- Run a Script
- Run Query
- Run DDL
- Run DML
- PL/SQL
- JSON Documents
- First Party Authentication (Basic Authentication)
- Comments
Related articles.
Assumptions
This article assumes the following.
- You already have a functioning installation of ORDS 17.4 or higher, using an application server or standalone mode.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database unless otherwise stated.
- You have a way to call the web services. I used "curl" and the "Advanced REST client" extension for Chrome.
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
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".
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;
/
Web services from the schema can now be referenced using the following base URLs.
http://localhost:8080/ords/hr/ https://localhost:8443/ords/hr/
We are now ready to start.
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 restEnabledSql.active attribute. This is the on/off switch for this functionality.
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
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".
ords --config ${ORDS_CONFIG} config set security.verifySSL false
The following option can be used to stop us from killing the system by pulling out too many rows.
ords --config ${ORDS_CONFIG} config set misc.pagination.maxRows 1000
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 TESTUSER1 user with the following URL.
http://localhost:8080/ords/hr/_/sql https://localhost:8443/ords/hr/_/sql
A direct call to this URL from a browser will result in a "405 Method Not Allowed" message.
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.
<entry key="restEnabledSql.active">true</entry>
This can be done manually, or with the following command.
cd /u01/ords $JAVA_HOME/bin/java -jar ords.war set-property restEnabledSql.active true
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="misc.pagination.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://localhost:8080/ords/hr/_/sql https://localhost:8443/ords/hr/_/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 : https://localhost:8443/ords/hr/_/sql Method : POST Header : Content-Type: application/sql Credentials: USERNAME:password Payload : <<Your SQL goes here>>
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 curl would look like either of these, depending on which forms of the flags you prefer to use.
$ 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;'
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" "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.
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 -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.
$ 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"
}
]
}
$
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 -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.
$ 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.
$ 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 DBMS_STATS package to gather statistics for the schema.
$ 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"
}
]
}
$
The following example uses EXEC (or EXECUTE) to call the DBMS_STATS package to gather statistics for the schema.
$ 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 DBMS_OUTPUT package. Provided the script enables server output, the response will contain the output.
$ 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 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 -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.
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 -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.
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 -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"
}
]
}
$
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 -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.
$ 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 -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...