Lab 2: AutoREST Enable Database Objects

Purpose

The AutoREST Enable feature introduced in SQL Developer 4.1 allows you to quickly set up REST services on your ORDS enabled database by running a simple wizard. The ORDS service will then query the definitions set up by the Auto REST wizard and displays the data on the browser. This feature allows users wanting to quickly enable database objects and run basic queries. Users wanting more complex operations will need the full REST development setup.

In this lab, you will be going through the following:

1. AutoREST Enable a Schema and Table
2. Retrieve Schema Metadata
3. Get Object Data
4. Get Table Data Using a Query

Time to Complete

30 min

Background

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 REST Client release 3.5

What Do You Need?

Before starting this tutorial, you should have:

• 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.
  
• Completed Lab 1: Installing and Administering REST Data Services with SQL Developer in the series.

To enable AutoREST on a schema and a table, perform the following steps:

1. On the left-side, the Connections navigator is displayed. To make a new connection, click down arrow beside + sign and click New Connection ...

   

2. Enter hr for Connection Name and Username, oracle for Password. Check Save Password. Enter ords for Service name. Click Test to test the connection.

   

3. Once the connection test shows Status:success, click connect to create the connection.

   

4. On the Connections navigator, connect to hr schema by expanding it. Right click hr and select REST Services > Enable REST Services.

   

5. The RESTful Services Wizard will appear. Enter the following and click Next.

   Enable schema	Check	Specifies whether the object is available to the Auto Rest service or not.
   Schema alias	hr	This gives the name that is used in the Auto Rest URL to access this object.
   Authorization required	Uncheck	When set, only authenticated users with the correct role may access this object.

   

6. The RESTful Summary will appear. Click Finish.

   

7. The SQL is processed and success message appears. Click OK.

   

8. Now, to AutoREST Enable a table, expand Tables (Filtered) under hr by clicking + beside it. 

   

9. Right click Employees and select Enable REST Service.

   

10. The RESTful Services Wizard will appear. Enter the following and click Next.

    Enable object	Check	Specifies whether the object is available to the Auto Rest service or not.
    Object alias	employees	This gives the name that is used in the Auto Rest URL to access this object.
    Authorization required	Uncheck	When set, only authenticated users with the correct role may access this object.

    

11. This screen gives a summary of the selected operations. Click the SQL tab.

    

12. Here is the SQL to REST Enable the Employees table. Click Finish.

    

13. The SQL is processed. Click OK.
    The HR schema and the Employees table are now REST enabled.

    

14. You can also review all objects that are REST enabled by viewing the Oracle REST Data Services report. Open the Reports navigator by clicking Reports under View menu.

    

15. Expand Data Dictionary Reports > Oracle REST Data Services and click REST Enabled Objects.

    

16. Select hr connection and click OK.

    

17. The REST Enabled Objects Report will open with a listing of all the REST enabled objects.

    

18. Now, you are ready to test the REST enabled object on a browser. In the next section, you will see how to retrieve a REST enabled object's metadata.

Once the employees table is REST enabled, perform the following steps to retrieve the table's metadata:

1. Open the browser and enter the following URL in the address bar:

   http://localhost:9090/ords/hr/metadata-catalog/employees/

   The URL corresponds to the following:
   http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/table/

   

2. The results will display the metadata for the employees table.

   

You can retrieve the data from a REST enabled object by performing these steps:

1. Open the browser and enter the following URL in the address bar: http://localhost:9090/ords/hr/employees/

   The URL corresponds to the following:
   http://<HOST>:<PORT>/ords/<SchemaAlias>/table/

   

2. The results will display entire data for the employees table. Each item will have a link to its own data page.

   

You can retrieve the data using an identifying key value from a REST enabled object by performing these steps:

1. Open the browser and enter the following URL in the address bar: http://localhost:9090/ords/hr/employees/100

   The URL corresponds to the following:
   http://<HOST>:<PORT>/ords/<SchemaAlias>/<objectAlias>/<KeyValues>

   

2. The results will display the data for the employee whose employee_id = 100 in the employees table. Notice that there are links at the bottom of the page that takes you to different data views.

   

Congratulations! You have successfully completed lab 2 from this series. Please go to the next lab REST Development to learn more.

• Lab 3: REST Development

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

• Curriculum Developer: Anjani Pothula

• Other Contributors:Ashley Chen