Here are some conceptual things that I wish I had understood prior to trying to install and configure ORDS.

modplsql Data Access Descriptor (DAD)

This is a basic unit of configuration in modplsql. Among other things, you can establish a method of connecting to your database (e.g. a username, password, and database connect string), as well as a URL path by which to execute PL/SQL commands for your application.

Apache Location

This is a basic unit of configuration in Apache and for mod_owa. With mod_owa, you use an Apache Location to map the OWA service to a URL within Apache. This is also the place where you set up your database connection information.

ORDS "database"

This seems to configure a database connection pool within ORDS. When you perform the setup --database command, you are configuring a pool of connections. This corresponds to the database half of a modplsql DAD or mod_owa Location.

ORDS URL mapping

This is equivalent to the URL mapping half of the modplsql DAD or mod_owa Location. You associate a URL mapping with your database connection.

JDBC Proxy Connections

The REST part of ORDS is able to use so-called proxy connections to the RDBMS. This allows ORDS to have a single connection pool with all the connections to a public user account. When a request comes in that needs to be run against another database user/schema, it's able to leverage one of these connections to proxy into the desired user/schema, run the command, and then exit the proxy. In this way, ORDS need not have separate connection pools for every possible database user/schema that has a REST service.

If I had understood that I needed to create both a database and a URL mapping in order to run my PL/SQL application, I would have saved myself a lot of grief! Also, it appears that while REST operations use the proxy connection feature, PL/SQL gateway operations don't or can't - this is another thing that I wish I'd understood before attempting to get ORDS running as a PL/SQL gateway!

ORDS is delivered as a .zip file. As of this writing, you can download it from here: http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html.

Unzip it to a working directory. This is where you will run some installation and configuration steps. The unzipped image will include ords.war, which is the main deployable body of ORDS code and resources. It will also include some sub-directories, in particular params/. I will refer to this directory as the install directory.

Important: make copies of ords.war and params/ords_params.properties before doing anything else! If you make any mistakes during the subsequent steps, some steps become unrepeateable and you can end up stuck. During the steps that follow, the ORDS setup may/will:

• Make modifications to resource files inside the ords.war file itself.
• Write out or modify files in the install directory, in particular to params/ords_params.properties.
• Write subdirectories and files to a configuration directory.
• Modify the state of your database.

Because of these changes, ORDS installation/setup steps can exhibit a "memory effect", making it hard to correct mistakes. If need be, your best hope of recovery is typically to start over with the pristine ords.war file, params, and a completely empty configuration directory.

In the steps that follow you will run ORDS operations from the command line. You can see a list of commands this way:

java -jar ords.war help

You are running Java code within the ords.war file for these commands.

Run an ORDS command to establish a directory on your file system where ORDS will store all its configuration information. I planned to eventually deploy my ords.war to my Tomcat server, so I created a directory under my Tomcat installation area:

mkdir /usr/local/tomcat/conf/ords

This directory should exist prior to running the configdir command, which is as follows:

java -jar ords.war configdir /usr/local/tomcat/conf/ords

Note: this command modifies the ords.war file! It is now going to look in this directory whenever you run commands from it. So, if you make a mistake later, you may end up stuck, and wondering why you can't redo something, because ORDS is "remembering" your previous mistakes. If necessary, remove everything from this directory and start over again with a clean, original copy of the ords.war file!

Conceptually, this directory is the place where ORDS will store all of the information it needs to connect to your database and manage your URL mappings. That is as distinct from the installation directory, which is where you can run command-line actions against the ords.war file and where some parameters that appear to be particular to the command-line operations are stored. (It's unclear to me if any of the files in the install directory, particularly the file params/ords_params.properties, are still relevant once you've deployed ORDS to your web application server.)

Note: you may need to run some commands in sqlplus before performing the ORDS installation! Please read the gotcha notes at the bottom of this section before doing anything else. Prior to the ORDS installation described here, you may need to run some sqlplus commands and/or do some non-ORDS-specific setup on your database.

Note: after doing this, your params/ords_params.properties file will be changed, with information captured from your answers to the installation script. You may want to pre-edit this file prior to running the install - this will default some of the answers for you. In particular, one of the properties (the default tablespace name) is not prompted for in the script, and if you do not have a tablespace with the exact name ORDS uses (USERS), you'll get stuck. So, at least for that one parameter, you must edit the file in advance if you want an alternative. Here's what mine looked like:

db.hostname=dmcmahon-pc.us.oracle.com db.port=1521 db.servicename= db.sid=ORCL db.username=APEX_PUBLIC_USER migrate.apex.rest=false rest.services.apex.add= rest.services.ords.add=true schema.tablespace.default=SYSAUX schema.tablespace.temp=TEMP standalone.http.port=8888 standalone.static.images=/usr/local/tomcat/webapps/ROOT/i user.tablespace.default=USERS2 user.tablespace.temp=TEMP

You then run the installation:

java -jar ords.war install

Answer the scripted questions. If you did not edit the properties file, you'll have to enter your database machine name, port number, and either the SID or service name for the database. You will need the password for the DBA account. At some point you'll be prompted to set up the ORDS public user, which will run SQL commands against your database to set up that account. At some point you will be prompted to set up the PL/SQL Gateway, or not. You want to answer [1] to set this up! You'll be asked to enter the password for the APEX_PUBLIC_USER. After this, you may answer [2] to skip setting up RESTful services if you don't need them. Then answer [2] to finish the installation.

Important: possible "gotcha" issues!

• You may need to unlock the account APEX_PUBLIC_USER from sqlplus before you run the ORDS installation, if this hasn't been done already! You'd do this from sqlplus as the DBA, with a command such as:
  alter user APEX_PUBLIC_USER identified by YOUR_PASSWORD account unlock;
• You need to be sure that your DBA account is accessible by ORDS! In particular it needs to be possible to connect to it via JDBC (sqlnet), and it needs to have a password. This is something that tripped me up because my development database allows me to connect to the DBA account using OS authentication, e.g.:
  sqlplus / as sysdba This won't work for ORDS, you need to be sure you can connect with a password and database name, e.g.:
  sqlplus sys@ORCL/manager as sysdba

Note that this step may or may not modify the ords.war file. It definitely writes data into params/ords_params.properties. It also definitely populates some files in the configuration directory. And, obviously, if you have it do the ORDS SQL setup, it modifies your database.

This step is extremely important for ORDS to dispatch RESTful calls to your application. (Although it's unclear if this is necessary for the PL/SQL gateway - still, it can't hurt.)

Log in to your database as the user/schema where your PL/SQL application runs from. You can use sqlplus or SQL Developer. I used the scott/tiger account so I logged in with:

sqlplus scott/tiger@ORCL

Once connected, run this script:

begin
  ords.enable_schema;
  commit;
end;
/

This registers your user/schema (in my case, scott) as a dispatchable account for ORDS, and, critically, it allows the ORDS public user to proxy-connect to your schema.

This isn't necessary if, instead of setting up the APEX_PUBLIC_USER earlier, you just set up your own user/schema instead. Basically you get one database connection pool out-of-the-box. I kept the APEX_PUBLIC_USER as-is, so this meant that I needed to set up another ORDS "database" (really, connection pool) for my scott user/schema. That's what this step does:

java -jar ords.war setup --database scott

In response to the scripted prompts, you give (or accept as defaults) the same machine name, listener port number, SID or service name, etc. This time enter [2] when prompted to skip verifying/installing ORDS again. Then when prompted enter [1] to use the PL/SQL Gateway. Here is where you can enter the username (scott in my case) as the gateway username, and also enter the password (which is case-sensitive, another gotcha, so I made sure to enter lower case "tiger"). Enter [2] to skip RESTful services again.