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.


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 1, 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:

$ 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 - 64bit Production


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

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

User ORDSTEST created.

SQL> grant connect to ORDSTEST;

Grant succeeded.

SQL> grant resource to ORDSTEST;

Grant succeeded.


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

SQL> connect ordstest/*****;

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

Commit complete.


Verify the schema is enabled

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

SQL> select id, parsing_schema from user_ords_schemas;

---------- ------------------------------
     10001 ORDSTEST


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:

    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:

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

  1. Oracle SQLCL is the new command line SQL editor from the SQL Developer team. It’s like a modern SQL Plus, you should try it out, especially if you are on a Mac, since it’s Java based it doesn’t require any Oracle Database driver install.