Lab 1: Installing and Administering REST Data Services with SQL Developer


Before You Begin


This Hands-On-Lab series will show you how to use the RESTful Services feature in Oracle SQL Developer 4.1. You will be installing Oracle REST Data Services bundled in SQL Developer. Once installed, you will be creating, editing, validating, uploading and testing the RESTful Service.

The series include the following labs. These labs build on one another so it must be done in the following order.

  1. Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer
  2. AutoREST Enable a Schema
  3. REST Development
    • Define Resource Module, Resource Template and Resource Handler
    • Adding a Query to Resource Handler
    • Uploading a Resource Module
    • Testing a RESTful Service
  4. Insert, Update and Delete a Restful Service

An Oracle VM Virtual Box virtual machine is set up with the required software and files to execute this Hands-On-Lab

Installed Software :

  • Oracle Database
  • Oracle SQL Developer 4.1.3
  • Browser Support :
    • Mozilla Firefox with JSONView extension (to view the JSON formatted in the browser)
  • RESTful Services Testing Tool :
    • RESTClient release 3.5

Time to Complete

45 min


What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created. Oracle REST Data Services (ORDS) is bundled with SQL Developer. You can use SQL Developer to install the ORDS version that is bundled in SQL Developer or a standalone version.

About RESTful Services

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web. A service is described as RESTful when it conforms to the tenets of REST. Although a full discussion of REST is outside the scope of this lab, a RESTful Service has the following characteristics:

  • Data is modeled as a set of resources. Resources are identified by URIs.
  • A small, uniform set of operations are used to manipulate resources (for example, GET, POST, PUT, DELETE).
  • A resource can have multiple representations (for example, a blog might have a HTML representation and a RSS representation).
  • Services are stateless and since it is likely that the client will want to access related resources, these should be identified in the representation returned, typically by providing hypertext links.

The REST architecture has become popular in part due to its lightweight implementation, with less coding overhead than using other web service architectures such as SOAP.

RESTful Services Terminology

Common terms that are used throughout this lab:

  • RESTful Service : An HTTP web service that conforms to the tenets of the RESTful Architectural Style, as described in "About RESTful Services" above.
  • Resource Module : An organizational unit that is used to group related Resource Templates together.
  • Resource Template : An individual RESTful ervice that is able to service requests for some set of URIs (Universal Resource Identifiers). The set of URIs is defined by the URI Template of the Resource Template.
  • Route Patterns: A simple grammar that defines the particular patterns of URIs that a given Resource Template can handle. For example, the pattern, employees/, will match any URI whose path begins with employees/.
    Example: http://localhost:8888/ords/hr/demo/employees/
  • Resource Handler : Provides the logic required to service a particular HTTP method, for a specific Resource Template. For example the logic of the GET HTTP method for the above Resource Template might be:
    select empno, ename, dept from emp where empno = :id
  • HTTP Operation : HTTP (HyperText Transport Protocol) defines a number of standard methods that can be performed on resources:
    • GET : Retrieve the resource contents.
    • POST : Store a new resource.
    • PUT : Update an existing resource.
    • DELETE : Remove a resource.

What Do You Need?

Before starting this tutorial, you should have:

  • Know the basics of using SQL and PL/SQL to access data in an Oracle Database.
  • Access to Oracle VM Virtual Box virtual machine in which all of the software and relevant files for this Hands-On-Lab are made available.

Installing ORDS

With Oracle SQL Developer, you can quickly install ORDS by performing the following steps:

  1. On your Oracle VM Virtual Box desktop, open Oracle SQL Developer by clicking the SQL Developer icon.

    Description of this image
  2. The Oracle SQL Developer window is displayed.

    Description of this image
  3. Click Tools menu and select REST Data Services > Install .

    Description of this image
  4. The ORDS Installation Wizard will open. You can select to install the ords.war that is included with SQL Developer or a standalone version. Select the version that is included with SQL Developer. Specify a location where you would like the configuration files to be stored. If you are in a class, use Location: ords-config and click Next.

    Description of this image
  5. Click Yes to confirm creating new folder ords-config.

    Description of this image
  6. Provide the following values in the next step of the wizard:

    Host loclahost
    Port 1521
    Service Name ords

    Password: oracle <All passwords on the virtual image and in the class are lowercase 'oracle'.>

  7. Description of this image

  8. Specify a user that has SYSDBA privileges to install the ORDS schema.
    Enter Username: sys as sysdba Password: oracle .

    Description of this image
  9. Click Next to keep the default Tablespace.

    Description of this image
  10. The PL/SQL Gateway user is responsible for executing PL/SQL stored packages and procedures. You will not need it for this lab.
    Uncheck Use PL/SQL Gateway and click Next.

    Description of this image
  11. Select Run in Standalone Mode when installation completes
    Enter 9090 for Http Port and click Next.

    Description of this image
  12. You will create an ORDS Administrator user to manage configurations and an ORDS RESTful Services User to create/edit RESTful Services.
    Providing the following:

    ORDS Administrator:
    Username: ords_admin
    Password: oracle

    ORDS RESTful Services User:
    Username: ords_dev
    Password: oracle
    Requires Secure Socket Layer: Uncheck

    Description of this image
  13. Review the Install Summary and click Finish.

    Description of this image
  14. The log will show ORDS has been installed and is running.
    The Log can be opened by going to the View menu and selecting Log or by pressing Ctrl+Shift-L.

    Description of this image
  15. Go to View menu and select Processes. This will also show that ORDS is currently running. You have successfully installed ORDS..

    Description of this image

Administering ORDS

With Oracle SQL Developer, you can administer an installed ORDS by performing the following steps:

  1. Click Rest Data Services > Administration from View menu.
    The Administration is where you manage your ORDS Global and Database settings.

    Description of this image
  2. This will open the ORDS Administration. Right click REST Data Services and select Connect.

    Description of this image
  3. Click the + sign to create a new REST Data Services Connection.

    Description of this image
  4. Select http and enter the following details for the New REST Data Services Connection and click OK.

    Connection Name ords_admin
    Username ords_admin
    Hostname localhost
    Port 9090
    Server Path /ords
    Description of this image
  5. Select ords_admin for Connection and click OK.

    Description of this image
  6. Enter the following and click Ok.

    Username: ords_admin
    Password: oracle
    Description of this image
  7. You are connected to ords_admin and ORDS Administration navigator opens.
    Expand REST Data Services to view the ORDS configuration settings. The Global settings are the default values.
    Expand the Connections, Security and Cache Files .You can update any of these settings and use the toolbar to test and upload your changes to the ORDS server.

    Description of this image
  8. Close the ORDS Administration navigator.

    Description of this image
  9. Let us now check if ORDS is connecting through the browser. Open the browser and enter the following URI in the address bar:

    If you see ORACLE REST DATA SERVICES 404 Not Found, it means that ORDS is connected.

    Description of this image


Congratulations! You have successfully installed ORDS. Please go to the next lab AutoREST Enable Database Objects to learn more.

Want to Learn More?

  • Lab 2: AutoREST Enable Database Objects

  • Lab 3: REST Development

  • Lab 4: Insert, Update, and Delete Data Using RESTful Services


  • Curriculum Developer: Anjani Pothula

  • Other Contributors:Ashley Chen