Running a standalone apex listener on a 11gr2


This is just a cookbook version of a get to go of the oracle apex listener on a (windows) oracle 11gR2. Necessary resources comprise the (otn authenticated) installation and developer guide for apex listener (pdf) as well as the download area for apex listener (zip). Note that there is another installation and developer guide for apex listener that comes as html pages with the downloadable package. It does contain release notes as of august 2011 such that it should be more recent than the pdf version (as of February 2011). There also is an apex listener forum hosted by oracle, see the APEX Listener OTN Forum for any user driven discussion threads.

Me, i tested the standalone version of apex listener to be employed in some rapid prototyping developments, establishing an easy backend data connection for nowadays ajax-based web ui’s. Iff you go for some apex listener evaluations pointing to productive environments, do consider deploying the code with a standard j2ee container host.

The installation (and developer) guide is, as mostly with oracle, a well done step-by-step manual just to follow and this is the way i choosed to go as follows: Get the downloadable package from the resource above. It’s an archive versioned as apex_listener.1.1.3.243.11.40.zip to this date and contains a singleton war file along with some documentation.

I already have apex installed with this database instance and decided to extract the archive to E:\oracle\product\11.2.0\dbhome_1\apex-listener, pointed out as the “temp” directory, for whatever reason, in the installation guide. From this root directory i launched the war file along with some documented parameters to depict the deployment directory, the apex image directory, the http port to use, and so on:

E:\oracle\product\11.2.0\dbhome_1\apex-listener\temp>java -Dapex.home=E:\oracle\product\11.2.0\dbhome_1\apex-listener\runtime -Dapex.images=E:\oracle\product\11.2.0\dbhome_1\apex\images -Dapex.port=8585 -jar apex.war

The deployment directory, actually, is the place where the war file will be extracted to and where runtime information as well as configuration data will be written. Do not use -Dapex.erase=true iff you decide to follow my installation hierarchie, because doing so will erase all configuration data too.

With the first launch of the war file, apex listener attempts to catch the credentials for two logins, namely the APEX Listener Administrator and the APEX Listener Manager like this:

Enter a username for the APEX Listener Administrator [adminlistener]: apxltr_admin
Enter a password for apxltr_admin:
Confirm password for apxltr_admin:
Enter a username for the APEX Listener Manager [managerlistener]: apxltr_man
Enter a password for apxltr_man:
Confirm password for apxltr_man:

After entering this information, a web browser (firefox here) automatically pops up for http://localhost:8080/apex/listenerConfigure to complete the configuration, most importantly the connection settings to the database, in a graphical environment.

I configured the connection (to tns) and clicked apply. This will, suprisingly to me at first, load the apex login page … Forget about that, it has no other meaning than posting the request and logging you out of the initial configuration.

Try http://localhost:8080/apex/listenerConfigure one more time and the page will tell you that the apex listener has already been configured.

Any other configuration based on the given database connection will now employ http://localhost:8080/apex/listenerAdmin and after logging in as APEX Listener Administrator you’ll see the database connection tab data being greyed out because this data is fixed from now on.

Also, the first usage statistics is available now for inspection.

So far the apex listner is running. Do note that running the war file and playing around with the admin ui creates the apex listener configuration file E:\oracle\product\11.2.0\dbhome_1\apex-listener\runtime\apex-config.xml and the apex listener login data in E:\oracle\product\11.2.0\dbhome_1\apex-listener\credentials, see my advice concerningĀ -Dapex.erase=true above.

After shutting down the apex listener shell process using ctrl+c and starting it up i was unable to login to http://localhost:8080/apex/listenerAdmin again. The browser states …

… and the shell output reads …

27.04.2012 17:26:59 com.sun.grizzly.http.servlet.ServletAdapter doService
SCHWERWIEGEND: service exception:
java.lang.IllegalStateException: Not yet initialised
        at oracle.dbtools.apex.config.ApexConfig.notInitialized(ApexConfig.java:249)
...

Actually waitung a jar full of seconds between shutting down and starting up does not show this issue. this is most probably a timing problem when loading the jdbc code into the apex listener runtime. Also, firstly asking for http://localhost:8080/apex and then changing to http://localhost:8080/apex/listenerAdmin worked well. Keep in mind that the apex listener standalone is nothing to consider for productive environments.

Playing around with some of the examples in the developer guide turned out to be a mere trial and error. There is lot lot of functionality available with apex listener such tha one is also able to do a lot of things wrong. Note also that the error handling, that is, the expressiveness of the error messages, is just poor. For example, on one test, I missed to close a string right away and the console output read:

30.04.2012 16:15:19 com.sun.grizzly.http.servlet.ServletAdapter doService
SCHWERWIEGEND: service exception:
java.lang.StringIndexOutOfBoundsException: String index out of range: 471
at java.lang.String.substring(String.java:1934)
...

However, this is early adopters stuff and I finally got all my tests up and running. The first ones being a multi row select from the well know dept-, emp-tables of scott

… as well as a parametrized select into scott.emp by column emp.ename using the auto allocation of uri parameter ename.

Further on i tried to execute some pl/sql anonymous block returning a json representation of the output parameters. This is where you have to manually specify parameters with the resource template because the get request can not derive the parameters from the incoming html.

The last thing to check out so far was serving a media resource, html here, from an sql select using the predefined two-column structure on return. Because this is another standard use case of apex listener, no output parameters have to be given here as well.

Quirkmodes

Quirk: Beware that the login window obviously asks for the credentials of the APEX Listener Manager but this will not work, APEX Listener Administrator credentials is nevessary here. The only page that APEX Listener Manager will be able to open is http://localhost:8080/apex/listenerAdmin, as far as I know up to now.

Quirk: Trying to set an out parameter of type java.sql.Timestamp raised an exception on the console telling me that this mapping is not (yet?) supported. I expect to see this mapping in some never version.

30.04.2012 16:57:28 com.sun.grizzly.http.servlet.ServletContextImpl log
INFO: null: java.sql.Timestamp is not a supported primitive datatype
java.lang.IllegalArgumentException: java.sql.Timestamp is not a supported primitive datatype
        at oracle.dbtools.rt.util.PrimitiveTypes.classOf(PrimitiveTypes.java:39)
...

Have fun.

Advertisements

2 comments

    1. You’re welcome. Do check back later, the article is to be continued, including a discussion of serving json from pl/json. p

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s