4 SQL Developer: Oracle REST Data Services Support

SQL Developer provides a convenient platform for installing and configuring Oracle REST Data Services, and for developing RESTful services.

Topics:

• Installing Oracle REST Data Services

• Automatically Enabling REST Access to a Schema, Table, or View (AutoREST)

• REST Development

4.1 Installing Oracle REST Data Services

To install Oracle REST Data Services in a traditional (non-CDB) database or a multitenant container database (CDB), click Tools, then REST Data Services, then Install. Follow the instructions for the Install/Run Oracle REST Data Services wizard.

To run Oracle REST Data Services in standalone mode, click Tools, then REST Data Services, then Run. Follow the instructions for Run Standalone Mode in the Install/Run Oracle REST Data Services wizard

To uninstall Oracle REST Data Services from a non-CDB or a CDB, click Tools, then REST Data Services, then Uninstall. Follow the instructions in Uninstall Oracle REST Data Services.

To reinstall Oracle REST Data Services, you must first uninstall the current installation, and then follow the instructions for the Install/Run Oracle REST Data Services wizard.

4.2 Automatically Enabling REST Access to a Schema, Table, or View (AutoREST)

If Oracle REST Data Services has been installed on the system associated with a database connection, and if the connection is open in SQL Developer, you can use the AutoREST feature to conveniently enable or disable Oracle REST Data Services access for specified tables and views in the schema associated with that database connection. Enabling REST access to a table or view allows it to be accessed through RESTful services.

AutoREST is a quick and easy way to expose database tables as REST resources. You sacrifice some flexibility and customizability to gain ease of effort. AutoRest lets you quickly expose data but (metaphorically) keeps you on a set of guide rails. For example, you cannot customize the output formats or the input formats, or do extra validation.

On the other hand, manually created resource modules require you to specify the SQL and PL/SQL to support the REST resources. Using resource modules requires more effort, but offers more flexibility; for example, you can customize what fields are included, do joins across multiple tables, and validate the incoming data using PL/SQL.

So, as an application developer you must make a choice: use the "guide rails" of AutoREST, or create a resource module to do exactly what you need. If you choose AutoREST, you can just enable a table (or set of tables) within a schema.

Note that enabling a schema is not equivalent to enabling all tables and views in the schema. It just means making Oracle REST Data Services aware that the schema exists and that it may have zero or more resources to expose to HTTP. Those resources may be AutioREST resources or resource module resources.

To enable Oracle REST Data Services access to one or more specified tables or views, you must do the following:

1. Enable the schema (the one associated with the connection) for REST access.

   Schema level: To enable Oracle REST Data Services access to selected objects (that you specify in the next step) in the schema associated with a connection, right-click its name in the Connections navigator and select REST Services, then Enable REST Services.

   (To drop support for Oracle REST Data Services access to objects in the schema associated with a connection, right-click its name in the Connections navigator and select REST Services, then Drop REST Services.)

2. Individually enable REST access for the desired objects.

   Table or view level: To enable Oracle REST Data Services access to a specified table or view, right-click its name in the Connections navigator and select Enable REST Services.

Follow the instructions in the RESTful Services Wizard (Auto-Enable REST Access).

4.3 REST Development

You can use SQL Developer to create, maintain, and use RESTful services. See the following topics for more information:

• About RESTful Web Services

• RESTful Services Terminology

• RESTful Services Requirements and Setup

SQL Developer provides the following options to create and manage RESTful services:

• REST Development Pane: Use REST Data Services in the Connections navigator to save your RESTful services to the database directly.

• REST Data Services in the Connections Navigator: Use REST Development using a running Oracle REST Data Services (ORDS), which will save your RESTful services to the database.

4.3.1 About RESTful Web 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. 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 an HTML representation and a RSS representation).

• Services are stateless and because 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.

4.3.2 RESTful Services Terminology

The following are some major terms related to RESTful services:

• RESTful service: An HTTP web service that conforms to the tenets of the RESTful architectural style.

• Resource module: An organizational unit that is used to group related resource templates.

• Resource template: An individual RESTful service that is able to service requests for some set of URIs (Universal Resource Identifiers). The set of URIs is defined by the URI Pattern of the Resource Template

• URI pattern: A pattern for the resource template. Can be either a route pattern or a URI template, although you are encouraged to use route patterns.

• Route pattern: A pattern that focuses on decomposing the path portion of a URI into its component parts. For example, a pattern of /objects/:object/:id? will match /objects/emp/101 (matches a request for the item in the emp resource with id of 101) and will also match /objects/emp/ (matches a request for the emp resource, because the :id parameter is annotated with the ? modifier, which indicates that the id parameter is optional).

  For a detailed explanation of route patterns, see docs\javadoc\plugin-api\route-patterns.html, under <sqldeveloper-install>\ords and under the location (if any) where you manually installed Oracle REST Data Services.

• URI template: A simple grammar that defines the specific patterns of URIs that a given resource template can handle. For example, the pattern employees/{id} will match any URI whose path begins with employees/, such as employees/2560.

• Resource handler: Provides the logic required to service a specific HTTP method for a specific resource template. For example, the logic of the GET HTTP method for the preceding resource template might be:

  select empno, ename, dept from emp where empno = :id
  

• HTTP operation: HTTP (HyperText Transport Protocol) defines standard methods that can be performed on resources: GET (retrieve the resource contents), POST (store a new resource), PUT (update an existing resource), and DELETE (remove a resource).

4.3.3 RESTful Services Requirements and Setup

To use the RESTful Services features in SQL Developer, you must install Oracle REST Data Services release 3.0.x or later.

You can use the Oracle REST Data Services Install wizard to install and run Oracle REST Data Services in standalone mode. See the information for the Install/Run Oracle REST Data Services wizard.

You can run the Oracle REST Data Services installer from the command line. For information about using the command-line interface, see Oracle REST Data Services Installation, Configuration, and Development Guide.

Note the following about Oracle REST Data Services RESTful services and Oracle Application Express RESTful services:

• To use Oracle REST Data Services RESTful services, you will need to enable a schema for RESTful services. Right-click the connection name in Connections Navigator, select REST Services, and then select Enable REST Services. The RESTful Services Wizard is displayed. See RESTful Services Wizard (Auto-Enable REST Access) for details.

• To use Oracle Application Express RESTful services, see Oracle APEX Administration Guide for details to set up Application RESTful services.

4.3.4 REST Data Services in the Connections Navigator

You can create and edit RESTful Services by using the REST Data Services node in the Connections navigator. This is only applicable for Oracle REST Data Services release 3.0.5 and later.

Note:

If you have an Oracle REST Data Services release 3.0.4 or earlier, you can upgrade to a later release.

In the Connections navigator, you can:

• Create, edit, and delete resource modules, resource templates, resource handlers, privileges, and roles saving them directly to the database.

• Export REST definitions for resource modules, privileges, and roles to a SQL file.

• Use the Save REST Handler icon in the SQL Worksheet toolbar to save the REST Handler definitions directly to the database.

4.3.5 REST Development Pane

The REST Development pane (click View, then REST Data Services, then Development) enables you to:

• Specify connection information for your RESTful services

  When you create a connection, specify the schema alias in the Schema/Workspace field. The schema alias is the Schema Alias name that you provided when you enabled a schema for RESTful Services.

• Retrieve all RESTful services (includes all resource modules and privileges), or retrieve a selected resource module or privilege

• Create, edit, and delete resource modules, resource templates, resource handlers, and privileges

• Upload a resource module or privilege and have the results saved on the server

• Open a .zip file that contain the RESTful services definitions

• Save the RESTful services definitions to a .zip file

The REST Development pane has icons for the following operations:

• New RESTful Services: Creates a new RESTful Service that can contain the resource modules and privileges.

• Retrieve RESTful Services: Downloads the resource modules and privileges from the user’s schema or workspace. (Enabled if you are connected to a RESTful Services connection.)

• Validate RESTful Services: Performs validation of all the resource modules' templates and handlers; displays any warning and error messages. (Enabled if the navigator tree contains at least one module or one privilege.)

• Connect: Connects to the enabled RESTful Services schema or Application Express workspace based on the user's credentials.

You can right-click on the RESTful Services node to perform the operations with associated icons, plus the following operations:

• Connect: Connects to the enabled RESTful Services schema or Application Express workspace based on the user's credentials.

• Disconnect: Disconnects from the user's schema or workspace.

• New RESTful Services: Creates a new RESTful Service that contains the resource modules and privileges.

• Retrieve RESTful Services: Downloads the resource modules and privileges from the user's schema or workspace. (Enabled if you are connected to a RESTful Services connection.)

• Open File: Opens a .zip file that contains the resource modules and privileges, and loads them into RESTful Services Administration.

• Save As: Creates a .zip file that contains the resource modules and privileges. (Enabled if the navigator tree contains at least one module or one privilege.)