DBA Hub

📋Steps in this guide1/9

Oracle REST Data Services (ORDS) : AutoREST

This article gives an overview of the AutoREST functionality of Oracle REST Data Services (ORDS), which allows you to automatically expose tables and views as RESTful web services.

oracle miscconfigurationintermediate
by OracleDba
48 views
1

Assumptions and Comments

This article assumes the following. The AutoREST functionality targets individual tables and views, effectively providing a table API. Applications that make multiple calls from the application server to the database to perform what should be a single transaction are a bit of a disaster from a performance perspective. In my opinion it is better to code manual handlers that call transactional APIs, which may perform multiple operations in a single call.
2

Create a Test Database User

We need a new database user for our testing. Create and populate a copy of the EMP table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

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

CONN testuser2/testuser2@pdb1

CREATE TABLE EMP (
  EMPNO NUMBER(4,0), 
  ENAME VARCHAR2(10 BYTE), 
  JOB VARCHAR2(9 BYTE), 
  MGR NUMBER(4,0), 
  HIREDATE DATE, 
  SAL NUMBER(7,2), 
  COMM NUMBER(7,2), 
  DEPTNO NUMBER(2,0), 
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
  );
  
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;
3

Enable ORDS and AutoREST

Enable REST web services for the test schema. We use any unique and legal URL mapping pattern for the schema, so we don't expose the schema name. In this case we use "hr" as the schema alias. Web services from the schema can now be referenced using the following base URL. The final step is to enable AutoREST for the EMP table. Notice the object is called EMP, but we want the web service to refer to it as "employees", hence the object alias. To disable AutoREST repeat the call with the parameter set to FALSE. We are now ready to start.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CONN testuser2/testuser2@pdb1

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

http://localhost:8080/ords/hr/

BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE, -- Default  { TRUE | FALSE }
    p_schema       => 'TESTUSER2',
    p_object       => 'EMP',
    p_object_type  => 'TABLE', -- Default  { TABLE | VIEW }
    p_object_alias => 'employees'
  );
    
  COMMIT;
END;
/
4

GET Web Services (READ)

By default browsers use the GET method for HTTP calls, so the following URLs can be called from a browser URL bar. The following URLs return JSON documents containing metadata about the objects in the test schema the specified object structure respectively. There are a variety of ways to query data from an AutoREST enabled table or view. The following URL returns all the data from the EMP table. Remember, the object alias was set to "employees". The data from an individual row is returned using the primary key value. A comma-separated list is used for concatenated keys. It's possible to page through data using the offset and limit parameters. The following URL returns a page of 5 rows of data from the EMP table, starting at row 6. There are a variety of operators that can be used to filter the data returned from the object ( here ). A few examples are shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Available Objects : http://localhost:8080/ords/hr/metadata-catalog/
Object Description: http://localhost:8080/ords/hr/metadata-catalog/employees/

http://localhost:8080/ords/hr/employees/

http://localhost:8080/ords/hr/employees/7521

http://localhost:8080/ords/hr/employees/?offset=5&limit=5

# job = 'MANAGER'
http://localhost:8080/ords/hr/employees/?q={"job":"MANAGER"}

# salary >= 3000
http://localhost:8080/ords/hr/employees/?q={"sal":{"$gte":3000}}

# job = 'MANAGER' AND salary >= 2000
http://localhost:8080/ords/hr/employees/?q={"job":"MANAGER","sal":{"$gte":2000}}

# Top paid manager.
http://localhost:8080/ords/hr/employees/?q={"job":"MANAGER","$orderby":{"sal":"desc"}}&offset=0&limit=1
5

POST Web Services (INSERT)

New records are created using the POST method. The URL, method, header and payload necessary to do this are displayed below. If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will insert a row into the EMP table. In addition to the web service output, we can see the row has been created by querying the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
URL        : http://localhost:8080/ords/hr/employees/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01T00:00:00Z", "sal": 1000, "comm": null, "deptno": 10 }

$
curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/employees/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Location: http://localhost:8080/ords/hr/employees/9999
ETag: "LrUFzrlvUWKDl8yIQWriVCbyGqK7Phzh5S/H/out3bWUcMbQTGmtqFD2TvNGioU/zaYFwgiqE79yj9ygs5U2UQ=="
Location: http://localhost:8080/ords/hr/employees/9999
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:04:49 GMT

{"empno":9999,"ename":"HALL","job":"ANALYST","mgr":7782,"hiredate":"2016-01-01T00:00:00Z","sal":1000,"comm":null,"deptno":10,"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/employees/9999"},{"rel":"edit","href":"http://localhost:8080/ords/hr/employees/9999"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/employees/"}]}
$

SELECT * FROM emp WHERE empno = 9999;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 HALL       ANALYST	      7782 01-JAN-16	   1000 		   10

SQL>
6

PUT Web Services (UPDATE)

Records are updated, or inserted if they are missing, using the PUT method. The URL, method, header and payload necessary to do this are displayed below. Notice the row to be updated is determined by the URL, in a similar way to a GET call using the primary key. Excluding the PK columns, any columns not specified in the payload are set to null. If the payload is placed in a file called "/tmp/update-payload.json", the following "curl" command will update a row into the EMP table. In addition to the web service output, we can see the row has been updated by querying the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
URL        : http://localhost:8080/ords/hr/employees/9999
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "ename": "WOOD", "deptno": 20 }

$
curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/employees/9999
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Location: http://localhost:8080/ords/hr/employees/9999
ETag: "+/HytdM4atnPSuuDDaUrG5ZQNF9DtlWhM3NAalo3vqQ7a0ICHNrscmma+1ktAQVOnD66H+Pz88FahM96Ch9dDw=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:10:55 GMT

{"empno":9999,"ename":"WOOD","job":null,"mgr":null,"hiredate":null,"sal":null,"comm":null,"deptno":20,"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/employees/9999"},{"rel":"edit","href":"http://localhost:8080/ords/hr/employees/9999"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/employees/"}]}
$

SELECT * FROM emp WHERE empno = 9999;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 WOOD 								   20

SQL>
7

DELETE Web Services (DELETE)

Records are deleted using the DELETE method. The URL and method necessary to do this are displayed below. There are two variations shown. The first is similar to the PUT method, adding the primary key value into the URL. The second uses a query string to target the row(s) to delete. If you specify additional header or payload information the web service call may fail. The following "curl" command will delete a row from the EMP table. The URL is an encoded version of the one shown above. In addition to the web service output, we can see the row has been deleted by querying the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
URL        : http://localhost:8080/ords/hr/employees/9999
Method     : DELETE

URL        : http://localhost:8080/ords/hr/employees/?q={"empno":9999}
Method     : DELETE

$
curl -i -X DELETE  http://localhost:8080/ords/hr/employees/?q=%7B%22empno%22%3A9999%7D
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:15:26 GMT

{"rowsDeleted":1}
$

SELECT * FROM emp WHERE empno = 9999;

no rows selected

SQL>
8

Batch Load

In addition to basic DML and queries, it's possible to upload batches of data using AutoREST. The URL, method, header and payload necessary to do this are displayed below. Notice the payload is CSV data. If the payload is placed in a file called "/tmp/data.csv", the following "curl" command will perform a batch load into the EMP table. In addition to the web service output, we can see the rows have been loaded by querying the table. The parameters that can be used to influence the batch load are documented here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
URL        : http://localhost:8080/ords/hr/employees/batchload?dateFormat="DD-MON-YYYY"
Method     : POST
Header     : Content-Type text/csv
Raw Payload:
empno,ename,job,mgr,hiredate,sal,comm,deptno
9990,JONES,CLERK,,24-JUN-2016,1000,,20
9991,SMITH,CLERK,,24-JUN-2016,1000,,20
9992,DAVIS,CLERK,,24-JUN-2016,1000,,20
9993,BROWN,CLERK,,24-JUN-2016,1000,,20
9994,CLARK,CLERK,,24-JUN-2016,1000,,20

$
curl -i -X POST --data-binary @/tmp/data.csv -H "Content-Type: text/csv" http://localhost:8080/ords/hr/employees/batchload?dateFormat="DD-MON-YYYY"
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: text/plain
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:21:27 GMT

#INFO Number of rows processed: 5
#INFO Number of rows in error: 0
0 - SUCCESS: Load processed without errors
$

SELECT * FROM emp WHERE empno > 9000;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9990 JONES      CLERK		   24-JUN-16	   1000 		   20
      9991 SMITH      CLERK		   24-JUN-16	   1000 		   20
      9992 DAVIS      CLERK		   24-JUN-16	   1000 		   20
      9993 BROWN      CLERK		   24-JUN-16	   1000 		   20
      9994 CLARK      CLERK		   24-JUN-16	   1000 		   20

SQL>
9

Display Enabled Objects

The view displays enabled objects. For more information see: - Automatic Enabling of Schema Objects for REST Access (AutoREST) - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Installation on Tomcat - Oracle REST Data Services (ORDS) : Configure Multiple Databases - Oracle REST Data Services (ORDS) : Auto PL/SQL - Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL - Oracle REST Data Services (ORDS) : Authentication Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SET LINESIZE 200
COLUMN parsing_schema FORMAT A20
COLUMN parsing_object FORMAT A20
COLUMN object_alias FORMAT A20
COLUMN type FORMAT A20
COLUMN status FORMAT A10

SELECT parsing_schema,
       parsing_object,
       object_alias,
       type,
       status
FROM   user_ords_enabled_objects
ORDER BY 1, 2;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!