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

Purpose

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 12.1.0.2
• 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 :
  • WizTools.org RESTClient release 3.5

Time to Complete

45 min

Background

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.
  

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.

   

2. The Oracle SQL Developer window is displayed.

   

3. Click Tools menu and select REST Data Services > Install .

   

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.

   

5. Click Yes to confirm creating new folder ords-config.

   

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

   Host	loclahost
   Port	1521
   Service Name	ords

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

7. 

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

   

9. Click Next to keep the default Tablespace.

   

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.

    

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

    

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

    

13. Review the Install Summary and click Finish.

    

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.

    

15. Go to View menu and select Processes. This will also show that ORDS is currently running. You have successfully installed 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.

   

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

   

3. Click the + sign to create a new REST Data Services Connection.

   

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

   

5. Select ords_admin for Connection and click OK.

   

6. Enter the following and click Ok.

   Username: ords_admin
   Password: oracle
   

   

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.

   

8. Close the ORDS Administration navigator.

   

9. Let us now check if ORDS is connecting through the browser. Open the browser and enter the following URI in the address bar:
   http://localhost:9090/ords/

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

   

Congratulations! You have successfully installed ORDS. Please go to the next lab AutoREST Enable Database Objects 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