Thursday, July 9, 2015

making https (webservice) requests from PL/SQL without a wallet

While developing the application for my kscope15 presentation I discovered that my Oracle XE database is unable to handle the certificate for Linked-in. This has to do with the algorithm used to sign the Linked-in certificate. Oracle XE 11 has a bug that prevents it from handling the certificate correctly.

The solution lies in creating a proxy in between your database and the final API endpoint.
Like this:

  1. your pl/sql program uses UTL_HTTP or APEX_WEB_SERVICE.MAKE_REST_REQUEST to do the API-request to your Apache proxy.
  2. The proxy uses its configuration to forward the request to the final endpoint
  3. The endpoint replies to your proxy
  4. Your proxy reverse proxies it to the requesting code inside your database
In this drawing the green numbers are regular HTTP requests and the orange numbers represent HTTPS requests and responses.

This all sounds very promising. Getting rid of the Oracle wallet seems like a good idea. However you can set the greens to be HTTPS as well, in which case you only need to have one certificate in your wallet.


  • My Oracle database server uses a private IP range.
  • My Apache server is accessible from the internet, but has a second network interface connected to the private IP range.

Setting up your proxy

Setting up the proxy consists of creating a new (virtual) site on your server. I chose to use the domain "revprox.local" because this domain will never get resolved into a real IP-number:

I now need to edit the httpd.conf for my apache server:

RewriteEngine On
ProxyVia On

## proxy for linkedin
ProxyRequests Off
SSLProxyEngine On

  Order deny,allow
  Allow from all

ProxyPass        /
ProxyPassReverse /
ProxyPass        /
ProxyPassReverse /

As you see in the linked-in API documentation, all API endpoint either are on or on It is therefore sufficient to define these two in my httpd.conf.

Setting up your database

As I mentioned before, the url http://revprox.local will never resolve into anything useful. We must tell the database what to do when a request for revprox.local comes around.
We now only need to alter the /etc/hosts file as thus:     revprox.local

The trick lies in the last line:
  • is the private IP number of my apache-proxy
  • In the example I removed extra lines that are not relevant for my story

Making a web-request

Now we set up the proxy and made changes to the /etc/hosts file we can actually start using them.

For example, when getting an oauth2 token from linked-in, the documentation tells us to make a request to:

Instead we will be stuborn and use:


as the API endpoint.

That's all folks.

Friday, July 11, 2014

New meetup group for Dutch speaking Apex developers

Inspired by Dan McGhan, today I launched a new meetup: ORCLAPEX-NL

Everybody speaking enthousiastic about Apex and speaking Dutch is welcome.

Eventhough the home-location is Tilburg, I think most of our meetups will end up to be more in the center of the country.

Friday, March 15, 2013

SQL developer won't connect to Apex listener 2.x

Wow, I broke my brains over this for the last three days.

I installed the apex listener as mentioned in the manual. It mentions you can use Java 6 Update 20 JDK or later.

However, what they mean is that you should use Java 6 JDK, and you can use update 20 or later.

What I did in my stupidity is to use Java 7. That's later then Java 6 upd 20 right?


If you use Java 7 your listener might work (which was the case in my situation) but connecting to it using SQL Developer is a "no go" :-(

Be warned.

regards, Richard

Wednesday, August 8, 2012

Setting an apex-item from PL/SQL

Got it from Andy's Blog:

Within ApEx PL/SQL Processes, regions and items you can use the bind variable syntax (:PX_MY_ITEM) both to read and set the value of that item held in session state. Like so:
:PX_MY_ITEM := 'wibble';
l_my_local_variable := :PX_MY_ITEM;

However, in stored PL/SQL packages, procedures and functions you cannot use the bind variable syntax. Rather, you must use the v('PX_MY_ITEM') syntax. But this is read only.

So how do you set the value held in session state for a given page item from within a stored package, procedure or function?

The answer lies in the set_session_state procedure found in the APEX_UTIL package. E.g.
p_name => 'PX_MY_ITEM'
, p_value => 'wibble');

Saturday, June 23, 2012

update Oracle Linux without CSI

In my ongoing quest for freeware in combination with Oracle Apex I gave Oracle Unbreakable linux another try when I heard that you _are_ able to update/upgrade it using yum, without a support identifier.

First we install Oracle Unbreakable Linux here

I opted for the 64 bit version and installed a minimal version in my VirtualBox environment.

  • 2048 Mb memory

  • 32 Gb Harddisk

  • 2 CPU's

  • 2 Network cards

    • Adapter 1: "NAT"

    • Adapter 2: "Host Only"

When the installer asks for the computername you should make the networkcards connect automatically:

On the bottom left you can select "Configure Network"

Select each of the network cards (eth0 and eth1) and click "Edit"

The trick is in selecting the "Connect automatically" checkbox. check it for both cards.

Continue the rest of the installer normally. Choose the "Minumal" install

After the installation log in as root.
mount /dev/dvd /mnt
cd /mnt/Packages
rpm -ivh wget #(and press the tab-key)
rpm -ivh nano #(and press the tab-key)
cd /etc/yum.repos.d
nano /etc/yum.conf

We now need to enable yum by adding an extra line to the configuration file:

That should do the trick. Test it out by issueing
yum update

Friday, February 24, 2012

Unable to login or run an application after applying APEX 4.1.1 patch set?

Patrick Wolf blogged about this, but I'd like to have it available here as well:

If you get the error Workspace “[workspace name]” is inactive. Contact your administrator. when you try to login into your workspace or when you run an application, then you should run the following script to fix the status of your workspaces.

  • Connect as SYS, SYSTEM, APEX_040100 or any user who has the APEX_ADMINISTRATOR_ROLE role

  • Run the following PL/SQL script:
    for l_workspace in ( select short_name
    from apex_040100.wwv_flow_companies
    where account_status='AVAILABLE' )
    end loop;

Note: This problem only occurs for workspaces which have been created on the command line with the apex_instance_admin.add_workspace procedure. Workspaces which have been created through the UI should be fine.

The problem has been filed as bug# 13769526 and will get fixed in the next version of APEX. Thanks to Dimitri who notified us about the problem.

Monday, February 13, 2012

APEX listener startup/shutdown script

Watching our visitors and how they arrive on I cam across this page:

definately worthwile caching... here we go:

Kris gives a script, which I altered a little bit.
# chkconfig: 2345 80 05
# description: This is a program that is responsible for taking care of
# starting the apex-listener as a service.
# processname: apxlistener
# Red Hat or SuSE config: /etc/sysconfig/apxlistener
# Debian or Ubuntu config: /etc/default/apxlistener
. /etc/rc.d/init.d/functions
NAME="Oracle Application Express Listener"

start() {
echo -n "Starting $NAME: "
if [ -f $PIDFILE ]; then
echo APEX Listener already running: $PID
exit 2;
nohup $JAVA -Dapex.home="$APEX_LISTENER_HOME/tmp.apxlistener" -Dapex.images="$APEX_IMAGES" -Dapex.port=8080 -jar "$APEX_LISTENER_HOME/apex.war" 2>&1 > $LOGFILE &
echo Started PID: $RETVAL
return $RETVAL


status() {
echo -n "Status $NAME: "
if [ -f $PIDFILE ]; then
echo APEX Listener already running: $PID
ps -ef | grep $PID
echo APEX Listener not running

stop() {
if [ -f $PIDFILE ]; then
echo -n "Shutting down $NAME PID:$PID"
kill $PID
rm -f $PIDFILE
echo APEX Listener not running
return 0

log() {
tail -f $LOGFILE

info() {
echo Before first use you must run the command below manually to initiate the service.
echo $JAVA -Dapex.home="$APEX_LISTENER_HOME/tmp.apxlistener" -Dapex.images="$APEX_IMAGES" -Dapex.port=8080 -jar "$APEX_LISTENER_HOME/apex.war"

case "$1" in
echo "Usage: {start|stop|status|restart|log|info}"
exit 1
exit $?

You should edit highlighted lines:
JAVA: the location of your JDK. be advised the minimum version your listener needs
APEX_LISTENER_HOME: the location the listener uses to store its config files
APEX_IMAGES: the location of the apex images directory

Create the file as /etc/init.d/apxlistener

Now set it as executable
chmod a+x /etc/init.d/apxlistener

add it to the startup-list:
chkcondig --add apxlistener
chkconfig apxlistener on

Now we're allmost there. The listener needs a console to do the initial configuration. If you would start the service as is, it will not have a console.

I added an option "info" that echoes the commandline the service will use:

so.. execute:
/etc/init.d/apxlistener info

will give you something like this:
Before first use you must run the command below manually to initiate the service.

/usr/java/jdk1.7.0_04/bin/java -Dapex.home=/u01/app/oracle/product/1.1.3/apex-listener/tmp.apxlistener -Dapex.images=/u01/app/oracle/product/4.1/apex/images/ -Dapex.port=8080 -jar /u01/app/oracle/product/1.1.3/apex-listener/apex.war

execute that line in your console, it will ask for two usernames ( I used "admin" in both cases) and passwords.

Enter them as requested after which you can go to http://yourserver:8080/apex/listernerConfigure

Here you can enter your details as described in the apex listener.

after a reboot the listener should start automatically.