APEX Listener as a replacement for modplsql ? - ORDS, SODA & JSON in the Database

Will APEX Listener ever be, or is it able to be now, a replacement for modplsql driven applications that are not APEX. It seems that it already takes care of everything needed to be a full scale modplsql replacement... In all honesty looking to see if it is possible to reduce the future licensing load on some of my clients if they wanted to build more redundancy into their front-end web applications. They wouldn't be reducing what they pay, as they are still in for the Forms installs in app server, but they are inhibited on their redundancy growth outward since adding new licenses to cover these new servers would be cost prohibitive. This is mainly a third-party product (SGHE's Self-Service Banner) that is driven off of modplsql, so rewrite isn't an option.
Thanks for your input,

As of the last EA version, it depends on which modplsql features your application uses. The main modplsql features that are not yet supported are:
Basic database authentication - The listener must be configured with a single username and password and cannot dynamically change the database connection at run time.
Multiple DADS - Each listener is configured for one and only one database connection. However, there is a way to install multiple copies of the listener, and configure each for a different database connection.
Dynamic parameter passing - There must be a one to one relationship between fields on an HTML form (or query string variables in the URL) and the non-defaulted parameters of the called procedure. Name and Value arrays, with the "!" before the procedure name in the URL are not supported.
CGI environment variables in the DAD.
Oracle MAY support some or all of these features in the production version, or in some future version. We can probably influence this by telling Oracle what we want - but no guarantees. Kris Rice tells me that the APEX Listener was written to be extendable, so once Oracle gives us some documentation of how to extend it, some enterprising programmer may add some of these features as an extension. 

rwendel wrote:
Will APEX Listener ever be, or is it able to be now, a replacement for modplsql driven applications that are not APEX.Richard,
If the Apex Listener does not cover your requirements, you might want to look into the Thoth Gateway, a free mod_plsql replacement running on Microsoft IIS.
Like the Apex Listener, it does not (yet) support basic authentication in combination with database users (ie, the Oracle user is fixed in the DAD config), and you can not set/override the CGI variables in the DAD.
However, it supports multiple DADs (as different nodes in web.config), dynamic parameter passing (using the ! syntax), and a few unique features as well, such as CLOB support, the ability to expose your PL/SQL packages as SOAP web services by simply flipping on a switch in the config file, and XDB integration.
Of course, it's not supported by Oracle in any way, but the Thoth Gateway does not interfere with your existing mod_plsql (or Apex Listener) setup in any way, and can be used in combination with those (for example to build redundancy, as you mention).
- Morten


mod_plsql to ORDS 3.0.5 significant performance degradation

Thanks to good old mod_plsql being dropped in OHS 12.2.1, which is needed for newer "strong" cipher suites, I was forced to try ORDS as a replacement.   I had tried APEX Listener 1.1 before which was fine for APEX (very minor degradation), but not for PL/SQL Web Toolkit applications.   ORDS 3.0 added the missing functions such as multiple DADS and security authorization verification function.  The configuration consistency and debugging are not mature, but it can be made to functional work.  The URL mapping documentation is totally wrong, old APEX REST is best left disabled, and defaults.xml parameters are really for all "DADS" unless they start with jdbc or db.   So I thought the worse was over just getting it to work.   Then came performance testing.   It ranges from 6 to 10 times worse.   Pages taking 0.5 to 1.5 seconds now run about 3.5 to 7 seconds. Yes it is less than 10 seconds, but a very noticeable degradation to end users (even enterprise SSO is faster).   Tracing down the degradation, wwv_flow_file_mgr.get_file and APEX ajax calls are the biggest offenders.   With work, I could move the image files to Apache directories, but I cannot move ajax calls.  I also isolated that it is NOT the interface between OHS and WebLogic and NOT WebLogic.   The same issue occurs with direct access to the standalone ORDS.   I made sure there were 3+ simultaneous database connections that were not disconnecting.   I also setup the ORDS caching which is generating cache files, but no help.  So it is something with how ORDS and JDBC interface.   Very disappointing. I am going to reverse proxy OHS 12.2.1 (front-end with TLS 1.2 and strong encryption) to OHS 11.1.1.x with mod_plsql (much faster) until ORDS matures and performs better.   OHS 11g is supported until Dec. 2018. Any ideas what is wrong with ORDS 3.0.5?   Others have mentioned a performance degradation from 2.0.x
to achieve stronger cipher suites you can terminate your TLS at vanilla Apache and recede proxy back to OHS you don't need to reverse different versions of OHS to OHS.  APex just sits on top of OWA. you say you have performance issues, it would be nice to get a breakdown of modPLSQL and ORDS can you provide a timeline from chrome Dev tools in both cases please?
Thanks.  Yes, it was 50/50 whether to user OHS 12.2.1 or vanilla Apache 2.4, but I have to configure Oracle Fusion Middleware 12.2.1 anyway for BI Publisher.   I cannot paste in the Chrome inspect Network images, but here are some exact numbers: Simple web page with some small images and one form checkbox:   422ms via mod_plsql  and 3.41s via ORDS  Four ajax calls via radio button click:  625ms via mod_plsql and 6.54s via ORDS  (the final straw on ORDS) I am seeing that calls via ORDS are doing extra logons/logoffs to APEX_REST_PUBLIC_USER which correspond with the number of get_files and ajax calls.  Mod_plsql only uses the pooled APEX_PUBLIC_USER connections which ORDS uses only partially for each web page.  This is despite having APEX rest services turned off (partly because APEX did not work otherwise and partly because I don't need it for backward compatibility purposes).    Seems like the configuration needs to mature some.  The extra connects/disconnects are a performance killer. plsql.gateway.add=truerest.services.apex.add=falserest.services.ords.add=true
Hi, Ok, so a vanilla hello world application works fine and is even faster than mod-plsql - thats good. Now, what is strange and maybe I do not fully get your set up; ORDS from an OWA perspective  (not rest services) but purely from mod-plsql migration to ORDS only requires one schema account and thats the APEX_PUBLIC_USER. in your example you are mentioning APEX_REST_PUBLIC_USER - in your mod-plsql application where are you using this schema, if you are not then you have set up differently. You can make an AJAX request to the APEX_PUBLIC_USER. Ajax is a client concept, the server side does not care this was an AJAX request. your example says mod-plsql uses the pooled APEX_PUBLIC_USER account, ORDS can do the exact same. Compare the exact URL request in mod-plsql and ORDS they should be the exact same, I fail to understand how you have set up your environment and ORDS is now using a different schema and as you say is using two schema's; seems its your set up is slightly wrong. I am migrating a very large application (15million lines of code + ) from mod-plsql to ORDS and I do not have the issue you mentioned.
Hi, I have PL/SQL Web Toolkit applications too so I need multiple users, APEX_PUBLIC_USER and other schemas not related to APEX or ORDS.   For APEX purposes, I only need APEX_PUBLIC_USER as done via mod_plsql  ORDS is for some reason deciding to do multiple connections to APEX_REST_PUBLIC_USER per APEX web page (not by my choice) in addition to initially using the pooled APEX_PUBLIC _USER connections which is causing the performance problems.  A few other users are reporting performance problems too that worked fine with APEX Listener 2.x.   REST is disable in ORDS and in my workspace.   #WORKSPACE_IMAGES# URLS and ajax calls via dynamic actions appear to be causing the extra connects/disconnnects to APEX_REST_PUBLIC_USER.   
P.S.  My PL/SQL Web Toolkit applications are performing very well so the problem is limited to APEX.   That is good news for anyone with just PL/SQL Web Toolkit usage.

18.3 pre_hook and multiple schemas / teams

Hi, I truly love the introduction of the pre_hook, and have already installed it on our test environment, can't wait to test it. I just want to confirm a few assumptions. (EBS team is nervous about it) Multiple schemas / teams that run through the same application server (Tomcat f.i.) must agree to use the same naming system of the pre_hook functionOnce activated, it runs for all defined modules and handlers in all ORDS enabled schemas using that particular tomcat instanceTeams that don't want to use pre_hooks still need to create that function with a simple "return true;"Teams that use a different application server (Weblogic) can activate pre_hook and use a completely different naming system (since its a different defaults.xml)If they do not specify "procedure.rest.preHook" in their defaults.xml it's not activated.Separate ORDS installations (different path) on the same application server can do similar scenarios since different defaults.xmlords.war and ./ords/defaults.xml that doesn't specify it will not activate it for any schemas  using URI /ords/*ords.war renamed to ords_prehook and ./ords_prehook/defaults.xml with is specified will activate it for all schemas using URI /ords_prehook/*If using same DB where talking about the same schemas in both.Just confirming it's not something that will be activated in the ORDS schema, but rather controlled by the deployment on the application server. RegardsOli

Any Key benefits in upgrading to new listener?

I have Oracle HTTP Webserver running...
Would I get any key benefits in getting my IT Dept in switching to teh new listener?
Hello Dean,
I think that depends on your current infrastructure and your requirements for the web server that hosts your APEX:
+ If your OHS is running exclusively for APEX and have a J2EE container (e.g. a OC4J or WLS) you could consolidate your services.
+ Most parts of the administrative work can be done browser-based, so you don't need shell-access.
+ Another advantage is the fact that you don't need to restart the whole OHS just to reconfigure APEX.
-On the other hand, the current version of the Listener does not provide all features you'll find in OHS. For example, if you depend on certain DAD-configuration aspects such as setting up special CGI-Environment variables, you'll have to either stick with OHS or find other ways (e.g. a HTTP-Proxy) to provide the required features.
Just my personal point of view...

APEX Listener and Multiple APEX Instances

I have got APEX 4.x.x and Apex Listener 2.0.5 and I am really interested in configuring multiple instances of APEX to run through the same listener.  I found information on how you create your multiple connections but I am really confused by the portions on how you have to setup your URL Maps.  In my case I am insterested in something along the lines of https://apex.example.com/prod/, https://apex.example.com/dev/, https://apex.example.com/pprd/ where each of them is APEX running on a different database.  I am amendable to actually having the prod one run straight on https://apex.example.com/ as long as I could setup some kind of a default homepage app instead of directly offering the login page for that instance.  Can anyone offer any guidance in this type of a setup?  I am ok if need be to have the urls be along the lines of https://apex.example.com/apex/, https://apex.example.com/apex/dev/, https://apex.example.com/apex/pprod/ if they must be.  Note my deployment is on Weblogic 12c and I am using SQL Developer to admin the listener.
The first thing I did was fully configure the Listener so I can manage it via SQL Developer and this part is working.  I have added the DB Connections but the part that getting murky is setting up the routing in the listener so that it goes to the correct DB.  How does the terms used in SQL Developer translate over to the ones in the URL-Map information in the manual as it uses different names for each type of URL map.  I am thinking I will need to use a Request type of URL-Map.
Listeners are so light-weight, I have one for each (in virtual boxes)I prefer to keep listeners on separate servers as it is easier for me to verify that I am not about to run 'truncate table ...' on production.(my $0.02 worth)  For dev, I have multiple DB connections.The primary one (http://host:port/apex) points to my true development 11g box.The secondary one (http://host:port/apex/a12) points to my "playground" 12c box.  For the 12c, the URL Mappings are: (SQL Developer 3.2 and 4.0)Rule Type        = URL PathRouting Rule     = http://host:port/apex/a12Schema for REST. = (blank)  After I got that to work, I haven't played with any other options/ideas. MK
Hi David, I am in a similar situation to you:Multiple db connections with RESTful Services However I have got the Listener to talk to multiple databases.The following works for me across three different databases - I have three separate entries under the Database settings node under the SQL Developer Listener configuration GUI. dev - this is the connection that was set up by the Listener by default.URL mapping simply has a "Request Path" entry with routing Rule of /dev and schema name of /dev <RESTful service name - in my case the same name of the APEX workspace>(NB: This is only to facilitate REST - by default this will connect to the dev instance of APEX as this was the default credentials used to set up the Listener)Therefore the URL is http://servername/apex by default. uat - new entry created manually through SQL Developersetup Connections node to point to your database as you would for any standard type of database connectionsURL mapping simply has a "Request Path" entry with Routing Rule of /acc and RESTful Services schema of blankThe URL is then http://servername/apex/uat prd - same as uat but with request path entry and Routing Rule of /prdThe URL is then http://servername/apex/prd I like the request path setting rather than the full URL path as it is more flexible. cheers,k
I like the idea of running separate listeners as well as long as you are looking at one listener, one instance of APEX, one webtier server, and one db server.  In our case, the reason I am looking at multi-tenant listeners is because we plan to run our APEX on a Weblogic cluster of four VM Servers with a hardware load balancer in front of it.  By leveraging the same front ends we get to minimize the footprint on our physical hardware (ie. need fewer VMs for the entire infrastructure to support APEX) and we gain the resiliency of the clustered front end for our development and pre-production instances as well (seen as a bonus but not required).  Eventually our hopes are to implement Oracle RAC and this all seems to work together nicely to that end.

Single Sign On via APEX Listener

Hi, my customer does SSO in his APEX applications via NTLM (see Oracle_APEX_ProofOfConceptNTLMPLSQL.pdf). Now he wants to switch from Apache webserver to APEX Listener on Apache Tomcat. Is it there possible to get the remote user via OWA_UTIL.GET_CGI_ENV('REMOTE_USER'). If yes i think we can do SSO. What do you mean? Many thanks in advance. Regards,Martin
Hi, has nobody an idea? Regards,Martin
I'd like to know how to do this too.. I've not found any documentation about it though.. the only way I could get the remote_user name was by using Internet Explorer (8) with ActiveX automation object support being enabled (Initialize and script ActiveX controls not marked as safe for scripting) which then makes the username accessible by javascript, which can then be sent to APEX.. I've never figured out how to use the HTTP_HEADER (authentication) feature in apex in this configuration.
Hi Jason, i think Oracle is not interested to solve this task because they want to sell big solutions like Oracle Access Manager. But this is overloaded and too exepensive in APEX environments. Regards,Martin
I am not certain why you want to remove Apache in the first place.  Running ORDS on J2EE server (like Tomcat) BEHIND an HTTP server (like Apache) is the suggested configuration, mainly because an HTTP server can perform a lot of functionality for which J2EE servers aren't designed.  We have implemented SSO using an Apache module (Shibboleth) that outputs http header variables (can be configured to do so) that become accessible to APEX and the process becomes trivial using the "HTTP Header Variable" type Authentication Scheme.
Hi, thanks for your reply.Do you have a proof of concept paper? Regards,Martin
Martin1 wrote:
thanks for your reply.
Do you have a proof of concept paper?
I don't find any white paper, but you can just simply use the HTTP_HEADER (authentication) feature in apex to meet your requirement. You can reference https://community.oracle.com/thread/2501009?tstart=0
First readed sso so think about spengo and REMOTE_USER but since this is ntlm there is no light way to do this.