Generate XML/Json from database

To interact with external services XML or JSON format is necessary. It's not that hard to generate XML or JSON from the database.

Statement for xml
     SELECT dbms_xmlgen.getxml ('SELECT team,categorie from FWD_TEAMS) XML 
   FROM dual;

Output
  <ROWSET>
  <ROW>
  <TEAM>JA1</TEAM>
  <CATEGORIE>...
  </ROW>
  </ROWSET>


Statement for JSON (in apex in on-demand-process?)

    declare
     l_sql varchar2(200);
  BEGIN
     l_sql := 'SELECT team,categorie from fwd_teams';
     APEX_UTIL.JSON_FROM_SQL(l_sql);
  END;


Output
   {"row":[{"TEAM":"JD3","CATEGORIE":"Jeugd"}
   ,{"TEAM":"M3F1","CATEGORIE":"Jongste Jeugd"}]}

In Apex of course you can define a region as a webservice. This webservice returns JSON or XML.

Websockets and Apex

Websockets is a technology providing communication channels over TCP. It makes it possible to interact between server and browser, facilitating in live content. The browser doesn't have to contact the webserver (polling) as long as the connection is open. This technology could be used in monitoring applications. Instead of polling the database every x seconds/minutes a message is send to the browser when the content has changed.

Requirements

 A provider  facilitating a push-service or creating one of your own.

Example

1. Create an account with beaconpush.com

2. Create a page with the following javascript included
   
   
   <script type="text/javascript" src="http://cdn.beaconpush.com/clients/client-1.js"></script>
   <script type="text/javascript">
   Beacon.connect('*****', ['mychannel']);
       Beacon.listen(function (data) {
         if (data.message=='REFRESH') {
           gReport.pull();
         }
         else {
           alert(data.message);
         }
                });
   </script>
   
          **** = Unique userid
   
   
           This script shows an alert when the message is not 'REFRESH', else it refreshes 
           the Interactive report.
   
   
   
   
3. At beaconpush generate a message :  { "message": "REFRESH" } 
4. Create a databaseprocedure to trigger the message.

procedure push

  ( p_text in varchar2

  )

is

  l_clob clob;

begin

  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST

  ( p_url => 'http://api.beaconpush.com/1.0.0/*****/channels/mychannel'

  , p_http_method => 'POST'

  , p_body =>'{ "message": "'||p_text||'" }'

  );

end;

        This procedure accepts an varchar variable and sends it to beaconpush. The example 
        below will force the page with the javascript to refresh. Use different channels for different 
        pages

          begin
             push('REFRESH');
         end;

In oracle11 you maybe have to authorize the database to send messages to the external provider.

/*Run these ACL-Setting as SYS when you are in 11G

begin

  dbms_network_acl_admin.create_acl

    ( acl         => 'beaconConnectList.xml'

    , description => 'Beaconpush ACL'

    , principal   => 'APEX_040100'

    , is_grant    => true

    , privilege   => 'connect'

    , start_date  => null

    , end_date    => null

    );

  commit;

end;

begin

  dbms_network_acl_admin.assign_acl

    ( acl        => 'beaconConnectList.xml'

    , host       => 'api.beaconpush.com'

    , lower_port => null

   , upper_port  => null

   );

  commit;

end;

*/