Create an ORDS RESTful Service using PL/SQL

Overview

This article will get you started with using ORDS 3.0.0 to create RESTful Services using the new PL/SQL API.

Prerequisites

Install ORDS

Before you go any further you’ll need to download and install Oracle REST Data Services. Click here for instructions on how to get ORDS up and running in under 5 minutes.

Enable database schema

You’ll also need to enable a database schema to try out the steps in this article. Click here for instructions on how to enable a schema for use with ORDS, it’ll only take a minute to do.

Create a RESTful Service

Connect to the ORDS enabled database schema, in this article we’ll assume you are using the ordstest schema created by following the instructions mentioned above

1
2

SQL> connect ordstest/******;
Connected

Let’s create a Hello World example using the PL/SQL API:

1
2
3
4
5
6
7
8
9

begin
 ords.create_service(
      p_module_name => 'examples.routes' ,
      p_base_path   => '/examples/routes/',
      p_pattern     => 'greeting/:name',
      p_source => 'select ''Hello '' || :name || '' from '' || nvl(:who,sys_context(''USERENV'',''CURRENT_USER'')) "greeting" from dual');
 commit;
end;
/

This call does the following:

• Create a Resource Module called examples.routes
• Set the base path (aka uri prefix) of the module to /examples/routes
• Create a Resource Template in the module, with the route pattern: greeting/:name
• Create a GET handler and set it’s source as an SQL query that forms a short greeting.
  • GET is the default value for the p_method argument, and is used here, because the p_method argument was omitted.
  • COLLECTION_FEED is the default value for the p_source_type argument, and is used here, because the p_source_type argument was omitted.
• Note the use of an optional parameter named who.

Test the RESTful Service

Start up ORDS:

1

java -jar ords.war

visit the URI of the RESTful Service we just created in a browser:

http://localhost:8080/ords/ordstest/examples/routes/greeting/joe

• The above URL assumes ORDS is running on localhost and listening on port 8080, adjust these values if your configuration differs to these values.

If you have a JSON viewing extension installed in your browser you’ll see something like the following:

 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

{
 "items": [
  {
   "greeting": "Hello joe from ORDSTEST"
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 1,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/examples/routes/greeting/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/joe"
  }
 ]
}

Note how the URL does not include a who query parameter. Therefore the :who bind parameter is bound to the null value, which causes the query to use the value of the current database user (sys_context(''USERENV'',''CURRENT_USER'')) instead.

Let’s try another URL, this time supplying a who query parameter:

http://localhost:8080/ords/ordstest/examples/routes/greeting/joe?who=jane

This time the result will look like the following:

 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

{
 "items": [
  {
   "greeting": "Hello joe from jane"
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 1,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/examples/routes/greeting/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/joe"
  }
 ]
}

Note how the ORDSTEST value has been replaced by the jane value, as this time the :who bind parameter was bound to the jane value.