Since version 4.2 it's possible to create database-driven RESTful services in Oracle APEX. But it may be somewhat bumpy to set up these services. So this is an overview to accomplish the provided Example RESTful Service Module run properly.
Oracle APEX postinstallation task
Did you run the configuration script "apex_rest_config.sql" (on 12c: "apex_rest_config_con.sql") after installing Oracle APEX? You can verify this by checking if the users "APEX_LISTENER" and "APEX_REST_PUBLIC_USER" exist in your database. If not, execute this script from your apex installation directory. See also Oracle APEX Installation Guide.
Tip: This script has recent modifications, but it's not part of the standard APEX upgrade procedure. So if you executed this script for example in apex 4.2.1, but you're are now on apex 4.2.3 - then it would be a good idea to run this script again.
Configuring Oracle APEX Listener
This is well-documented in the guide Installing Oracle APEX Listener, so I won't go in detail here. If your APEX Listener is already configured and you just want to add the connection information for the RESTful database users, then use the setup-option:
java -jar apex.war setup
This could also be a good reason to upgrade your APEX listener. Check if a newer version is already released (most presumably it will be the case :).
Tip: You're having trouble to find out the version number of your current APEX Listener installation? Curiously this is not available in command-line interface, it's slightly hidden in APEX itself: login to your workspace, open the pulldown-menue "Administration" and select the item "About".
Example RESTful Service Module
This example module provides a good introduction in RESTful services. Open your APEX workspace and navigate to "SQL Workshop" - "RESTful Services". If no RESTful services are defined yet, then click on "Reset Sample Data" (right side of the screen under "Tasks").This function will create the example module named "oracle.example.hr".
Now your module is installed, but when you want to test a resource handler, you will receive the error message "Service unavailable". Just read on, the problem will be fixed in the last section.
Additional administration tasks
While developing RESTful services in Oracle APEX, it's essential to get further information about the error details when something should fail. Per default the logging and debugging functionalities are turned off.
One option is to enable the logging via the "Debug Tracing"-setting. Please consider that the APEX Listener hasn't got an own logging module, it uses the one from the application server. In case of glassfish application server the logs are located in the file
[glassfish installation directory]/glassfish/domains/[domain name]/logs/server.log
The second option is to enable the "PrintDebugToScreen"-setting. It displays the error message directly on your screen, this is of course very helpful during the development process.
In APEX Listener Troubleshooting is described how to activate both parameters. But keep in mind that both parameters should not be enabled on production systems. The "Debug Tracing"-setting because of generating large amounts of data, the "PrintDebugToScreen"-setting due to security issues.
Back to our problem with the Example Module. After enabling the debugging-setting we have a concrete error message now. It points out, that there are some user privileges missing: the REST Public User needs a proxy authentification to the APEX Workspace Owner ("parsing schema"). Certainly quite a basic task, when using RESTful services with Oracle APEX, but (as far as i know) it's still undocumented? Whatever the case, the grant-sql should look like this one:
alter user [workspace owner] grant connect through apex_rest_public_user;
Now you should be ready to use the RESTful services with Oracle APEX. Besides the OTN resources, don't forget to have a look at Kris Rice's Blog to discover further possibilities with RESTful services.
