Using websockets in APEX for automatic refresh

I visited the OGH APEX day this monday in Zeist. Not only gave Patrick Wolf some insight in the possible upcoming new features of 4.2, but there were also a bunch of inspiring speakers that gave me some new ideas. One of the speakers Roel Hartman gave a presentation of some HTML5 features. He used a notification-service to sent messages to the mobile devices of some of the members of the audience.

That gave me the following.

What would it take to let the database push a refresh on the screen.

Normally you have to implement a timer mechanism that uses resources to poll the database in some frequency, say 5 seconds, to keep the report up to data.
But what if we could use websockets to let the database push a refresh after an insert.

And it works! In its simplest form it’s so easy it almost made me cry.

What I did was the following:

1. Go to Beaconpush and sign up.

After registration you will get an API-key and a Secret Key

2. Create a page with an interactive report that you want to be refreshed.
In the page footer add the following:

    Beacon.connect('*****', ['mychannel']);
    Beacon.listen(function (data) {
      if (data.message=='REFRESH') {
      else {

with ***** your API-key.
Here I use the default channel. Now when we post a JSON-object

{ "message": "REFRESH" }

the message-attribute of the data-object will refresh the report. This will make your page listening to its channel in the beaconpush-server.

3. Create a simple package

create or replace package body beacon
/*Run these ACL-Setting as SYS when you are in 11G
    ( acl         => 'beaconConnectList.xml'
    , description => 'Beaconpush ACL'
    , principal   => 'APEX_040100'
    , is_grant    => true
    , privilege   => 'connect'
    , start_date  => null
    , end_date    => null

    ( acl        => 'beaconConnectList.xml'
    , host       => ''
    , lower_port => null
   , upper_port  => null

procedure push
  ( p_text in varchar2
  l_clob clob;
  ( p_url => '*****/channels/mychannel'
  , p_http_method => 'POST'
  , p_body =>'{ "message": "'||p_text||'" }'



We are almost there. Because in its simplest form I don’t want to use the secret key I disabled it in the account-settings on

Disable secret key usage

That’s it. You can test it by executing a simple

end ;

from your favorite IDE like SQL*Plus to test that the page has automagically refreshed.
Then put this code in some after insert trigger and you are done.
In a production system there are of course still a few things to think of
– using the secret key
– realise how many messages you will push and read the conditions of beaconpush very carefully.

Maybe you don’t want to use beaconpush. Maybe you want to write your own Comet server.


3 thoughts on “Using websockets in APEX for automatic refresh

  1. Pingback: Using websockets in APEX for automatic refresh with nodejs « emoracle

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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