Enabling a Schema for ORDS 3.0.0 RESTful Services

Overview

Before a database schema can be used to define RESTful Services you need to enable the schema so that ORDS can access it. It’s really simple and only takes a second to do.

Prerequisites

Before you go any further you’ll need to download and install ORDS 3.0.0 EA2. You can follow the instructions here, it’ll take just a couple of minutes.

Let’s try it out

The example below uses Oracle SQLCL ¹, but you can use SQL Plus, SQL Developer or whatever your favorite SQL editor is.

Create & enable a schema

In a command prompt fire up sqlcl as a user who has rights to create schemas:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

$ sql sys/****** as sysdba

SQLcl: Release 4.1.0 Beta on Tue Mar 10 18:07:51 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL>

Create a schema to enable for use with ORDS, we’re going to call it ordstest:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SQL> create user ordstest identified by ******;

User ORDSTEST created.

SQL> grant connect to ORDSTEST;

Grant succeeded.

SQL> grant resource to ORDSTEST;

Grant succeeded.

SQL>

Switch to the ordstest schema and enable the schema for use with ORDS:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

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

SQL> exec ords.enable_schema;
anonymous block completed
SQL> commit;

Commit complete.

SQL>

Verify the schema is enabled

Query the USER_ORDS_SCHEMAS view to confirm the schema has been enabled:

1
2
3
4
5
6
7

SQL> select id, parsing_schema from user_ords_schemas;

        ID PARSING_SCHEMA
---------- ------------------------------
     10001 ORDSTEST

SQL>

What’s happening under the covers

When you call ords.enable_schema, a few things happen:

1. The schema is configured so that ords_public_user can proxy to the schema. In the above example the following alter user command is issued:

   1	alter user ordstest grant connect through ords_public_user

2. A ‘base path’ url mapping is created that maps the schema to the lowercase form of it’s name. In the above example, assuming ORDS is listening on http://localhost:8080/ords/, then resources in the ordstest schema can be accessed under:

   1	http://localhost:8080/ords/ordstest/

3. Some internal house keeping occurs, like the creation of some stock privilege mappings to protect the JSON Document Store resources.

Footnotes