I wrote this informal how-to guide for the benefit of users who want to migrate off of mod_owa to the ORDS listener, as well as for users who are using Oracle's modplsql and facing the deprecation of that facility. These are just my own notes and not an official migration guide, something I'm hoping Oracle will produce. I had some difficulty getting things working, and as part of that process I looked at blog posts and discussion threads, and I realized that there is a general lack of knowledge about how to do this. I wrote this guide while using ORDS version 188.8.131.521.13.24, so be aware that changes to the scripted parts may have taken place since this was written.
ORDS stands for Oracle REST Data Services. The ORDS listener is a Java servlet that is capable of functioning as a PL/SQL gateway listener. It was formerly called the Apex listener - basically it's the same code, extended for REST and refactored so it can be used separately from Apex for pure REST applications. When used as a PL/SQL gateway, the ORDS listener does exactly what modplsql and mod_owa do: it receives HTTP requests, parses the URIs and ingests other header values, and forms a call to a PL/SQL package/procedure to execute the request. It then uses the standard OWA infrastructure to receive the response, which it returns to the client. modplsql and mod_owa rely on an Apache server to handle the actual HTTP socket operations - they are plug-ins. ORDS can similarly be used within a Java Web Application Server, where it runs as a Java servlet. ORDS also comes with a built-in app server, Jetty, which allows it to be run in a stand-alone mode.
Here are some conceptual things that I wish I had understood prior to trying to install and configure ORDS.
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:
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  to set this up! You'll be asked to enter the password for the APEX_PUBLIC_USER. After this, you may answer  to skip setting up RESTful services if you don't need them. Then answer  to finish the installation.
Important: possible "gotcha" issues!
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:
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  when prompted to skip verifying/installing ORDS again. Then when prompted enter  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  to skip RESTful services again.
The effect of this is to set up another connection pool for the scott/tiger user. Note that you can also use this to connect to other databases, although in my case the connections are to the same database, but just to another account. This is something I wish I'd understood before attempting to get ORDS running the first time. Also, note that while I chose to name my connection pool "scott", I didn't have to. The name of the pool and the name of the database schema/user to connect to need not be the same.
If you go to the config directory you should see an XML file for this pool in the conf sub-directory, e.g. conf/scott.xml.
There may be some way to avoid this step and have ORDS proxy over to the scott user, but I can't figure out how to get that to work. Since this is what modplsql does, I though that ORDS would work this way, and I wasted my time trying in vain to get my application to run without setting up a separate connection pool because I did not understand that this apparently isn't allowed except for RESTful calls.
This is part two of the two-part setup for the equivalent of a modplsql DAD. Here's what I did:
java -jar ords.war map-url --type base-path /scott scott
What this does is create a URL mapping, which should now show up in the configuration directory in file url-mapping.xml. The trailing string "scott" is the name of the database connection pool to which this path (/scott) is bound. Once again note that the pool name is the one you set up in the earlier step, and need not be the database user/schema name.
For reasons I don't understand, it's extremely important not to specify an Apex workspace or database schema name at this step!
Note that you can pick any name for the URL portion of the mapping. I chose /scott but I could have chosen /pls or really almost anything else I needed.
In the configuration directory there is a file defaults.xml which you can hand-edit to enable debugging. I always do this because no matter how careful I am, I always make some mistake, and am then frustrated because ORDS throws up 404 or 400 error pages with no information about what I did wrong. Edit the file and change these two lines from false to true:
<entry key="debug.debugger">true</entry> <entry key="debug.printDebugToScreen">true</entry>
Note that if you've done just about anything wrong, you'll get 404 or 400 error pages from ORDS. Even with the debugging information switched on, the pages contain less-than-helpful messages such as "...unable to dispatch...". This includes little mistakes like giving the password for scott in upper case instead of lower case! Unfortunately it seems quite sensitive to minor mistakes and won't give you much of a clue as to what you've done wrong.
After the installation, the ORDS defaults.xml file may contain these lines:
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry> <entry key="security.validationFunctionType">plsql</entry>
Gotcha! This function almost certainly doesn't include your custom functions on a "whitelist" of allowed functions, and so you will get 403 errors from ORDS and you'll be wondering why. As a temporary testing measure, you can just comment those lines out. For production use, you'll have to investigate doing an actual implementation. For my test situation, I set this to call MODOWA_TEST_PKG.AUTHORIZE(PROC_NAME in varchar2) which returns TRUE for all procedures in the test package.
You can run ORDS in the stand-alone mode, which runs the built-in Jetty application server. This is the best way to debug problems because the log output goes straight to your terminal window. (For example, when I used upper case "TIGER" as the password, I got messages here about how it was failing to start up the connection pool for scott, which was just about the only way I was told that I'd made that mistake.)
java -jar ords.war standalone
Note that the first time you run it, you may need to give a port number available on your machine. I used 8888 because 8080 (the default) is in use on my machine by Tomcat. It was one of the things I hand-edited in the params/ords_params.proprties file.
After you run this, a new file will appear in the configuration directory, standalone/standalone.properties
If all has been done properly, your PL/SQL application should now be accessible. I had previously installed the MODOWA_TEST_PKG to the scott user, and so from firefox I entered this URL:
This worked! It showed me the timestamp from my database as expected!
Gotcha! Notice that the URL you use for ORDS stand-alone has an extra step, /ords, immediately following the host:port portion, and before the part I thought I had configured (/scott). This step appears to be "hard-wired" because it's the point where ORDS itself is mounted into the built-in JETTY listener. I wasted countless hours trying to figure out why I wasn't getting any response to the URL that just started with /scott after the host:port; ORDS doesn't even give an error message about the apparently unmapped URL, it just produces no response and writes nothing to the output logs. It may be that this mapping point can be renamed, but it's tricky - see below.
You can kill ORDS with control-C (on Unix, anyway). When it's down, you may carefully edit the standalone/standalone.properties if necessary. I changed the document root property to match Tomcat's (though I'm not sure this was really necessary):
The point of all the previous steps was to make sure ORDS's flat-files are all written to the file system correctly. Also note that several of the steps modified the ords.war file itself.
I was deploying to Tomcat, which is installed on my machine at /usr/local/tomcat. This is why, earlier, I used /usr/local/tomcat/conf/ords as a configuration directory. I want to have my entire Tomcat setup in a single file-system tree so I can back it up easily, move it to another machine, etc.
I shut down Tomcat with the usual (for Unix) command:
I copied my ords.war file to the webapps folder:
cp ords.war /usr/local/tomcat/webapps
Note that the ords.war has been modified by the previous steps, in particular to store the location of the configuration directory. Most other information is now in flat-files within the configuration directory. Warning: the names of the sub-directories within the configuration directory appear to be identical to the name of the war file.
Then I restarted Tomcat with the usual (for Unix) command:
By default, Tomcat will expand the ords.war into a sub-directory with the same name, e.g. /usr/local/tomcat/webapps/ords on my machine. (In fact, it seems I can delete ords.war after the first-time use and it still works, because everything that was in ords.war is now expanded.) Tomcat will map ords to /ords in the URL space.
Gotcha: you can't change the name of ords.war to, say, apex.war, pls.war, or any other name at this point in order to change where the application server maps it - you need to have done that at an earlier step! More on this below, along with a possible work-around.
Now, I can access my application with:
Note that the first time access may be slow because it has to load the servlet and spin up the connection pools.
If for some reason you need to change the mapping point, then, at least in Tomcat, you can rename the ords.war to the other mapping point before the first-time deployment and expansion. For example, you can change the name to pls.war. This would then mean that the URLs to access your application would look like this:
If you've already expanded to webapps/ords and removed the war file, you can rename the directory to change the mapping point, e.g. change ords to pls.
The problem is, ORDS will look in the wrong places in the configuration directory for flat-files! I believe that to do the above properly, you should rename ords.war before the very first step, i.e. before running the configdir command. All other commands would then substitute your xxx.war file where the ORDS war file would normally appear. I believe the issue is that ORDS uses the name of the servlet mapping point as the name of the sub-directory it looks for in the configuration directory. Because of this, it may be possible to rename the sub-directory to work around the problem. For example on my machine:
cd /usr/local/tomcat/webapps mv ords.war pls.war cd /usr/local/tomcat/conf/ords mv ords pls
This worked for me, but I can't guarantee that all possible references (stored in the war file, stored in the xml files, etc.) are relative and would pivot to this change.
For ORDS stand-alone, it does appear as if the mapping point can be changed by renaming, but you have to change all of the following:
One last question that I can't answer: is the file params/ords_params.properties still relevant after deployment? It seems from the usage that this file is only used when you run ORDS installation steps from the command line, although it also may be relevant when you run it in stand-alone mode.
modplsql has the ability to do HTTP Basic authentication by connecting to the database as the username specified in the Basic challenge box, using the password given in the Basic challenge box. It could then run your PL/SQL request as that database user. Some modplsql and mod_owa customers run in this manner, with each user given a distinct database account. It's not clear how to make ORDS work in this manner.
ORDS can run a single shared connection pool and, by proxying, run RESTful services against different schemas, provided they are configured to allow ORDS to proxy (as described earlier). The problem is that I can't seem to get ORDS to do this for PL/SQL Gateway requests. Is it even possible?
ORDS has a parameter for setting the staging table that is roughly equivalent to PlsqlDocumentTablename. It's apex.docTable. The documentation is a bit vague as to whether this would be put in defaults.xml (a file found in the configuration directory) or in one of the connection-pool files (e.g. conf/scott.xml).
ORDS doesn't support an equivalent of PlsqlDocumentProcedure. If your application needs this feature, you'll have to work around the issue by coding a REST service endpoint. That's beyond the scope of this simple guide. A future version of ORDS will add this functionality.
Gotcha: ORDS looks to see if APEX_UTIL.FILE_UPLOAD is installed in your database. This happens at run-time. If this procedure is available, it's used to upload files instead of the staging table. Newer versions of Apex expect this (and mod_owa doesn't support it). If your legacy non-Apex application relies on a staging table, having Apex installed in your database will trigger this new behavior and file upload/download will appear not to work for your application. As a work-around, you might be able to hide this package by manually revoking execute permission on APEX_UTIL from your application's run-time user. For example, since I was testing as user SCOTT, it was sufficient for me to revoke execute on that package from user SCOTT, making it invisible to ORDS and causing ORDS to use the staging table instead.
The PL/SQL gateway has two calling conventions for flexible argument passing, both triggered by prepending an exclamation mark (!) ahead of the package and procedure name in the URL. The two-argument form passed two PL/SQL index-by tables of VARCHAR2 values, one with names and the other with values. The four-argument form passes a count of arguments, and then three index-by tables, one for names, one for values, and the last reserved (and unused).
Gotcha: ORDS only seems to support the 2-argument form.
Moreover, OAS was able to automatically rewrite calls that lacked the required (!) prefix into flexible argument calls by doing describes on packages and procedures and then invoking the procedure using the flexible argument calling method. ORDS is not able to do this.
The only solution is to ensure that your flexbile argument procedures use the 2-argument form, and that your URLs conform to the specification with the (!) prefix. If necessary, you can overload a procedure with a 2-argument form that calls your existing 4-argument version using code such as this:
procedure YOUR_FUNCTION(NAME_ARRAY in OWA.VC_ARR, VALUE_ARRAY in OWA.VC_ARR) is begin YOUR_FUNCTION(least(NAME_ARRAY.count(),VALUE_ARRAY.count()), NAME_ARRAY, VALUE_ARRAY, VALUE_ARRAY); end; /
Another behavior that OAS apparently supported was parameter promotion and demotion. This was done by describing the procedure being called, and if necessary treating a single-value parameter as if it were an array of one value (promotion to array) or by ignoring all but the first of a multi-value parameter (demotion to a string). This behavior was never documented in modplsql. ORDS appears to handle these situations.
I'm not clear on whether any files from the install directory, particularly
params/ords_params.properites, are used when it's deployed
to an application server. These properties appear to be relevant whenever
you run a command-line operation such as the installation steps above.
But what about when running ORDS stand-alone from the command line, do they
still matter? All the database information (for the connection pools) appears
to be present in the files within the configuration directory. Even
the ORDS stand-alone properties (such as the port number for Jetty to use)
appear to be present in the configuration directory (in