Apex and anychart

In Apex its easy to create flash or HTML5-charts. I think in most circumstances this will satisfy the wishes of the customer. However, sometimes I just want more....

Anychart expects an XML file with all  the data. On the website of Anychart you can find all the definitions of XML files and related charts. So you could generate the XML by yourself!

In Apex 3.1 is was possible to not just generate the type of charts provided by Oracle, but all the type of charts Anychart provided (column, cone, gauges, pyramids, etc, etc). As long as you where able to generate the correct XML file!

In Apex 4.x this no longer possible, Oracle modified the anychart-executable and you only have acces to a limited set of charts (bar, column, stacked). Unless you switch executables...., but that is not supported of course!

In apex 3,1 you also had the 32K limit. An XML file was limited to a 32K size and if you had a lot of data an error would occur.

In this 'example'(try-out) in Apex4.x I tried to pass the 32K barrier and tried to get a more generic solution for charts with a little bit more user influence.

create stored procedure to generate XML
Create a stored procedure(s) that generates the correct XML based on input-parameters.

   htp.p('Cache-Control: no-cache');
   htp.p('Pragma: no-cache');
   owa_util.http_header_close;
   htp.p('<anychart> ...
   etc

create a html region in Apex
Create a html region with the following source

       <div id="chartDiv"></div>

create a dynamic action
Create a dynamic action (by example on page load)

      chart = new AnyChart('#IMAGE_PREFIX#flashchart/anychart_6  /swf/OracleAnyChart.swf','#IMAGE_PREFIX#flashchart/anychart_6/swf/Preloader.swf');
     chart.width = 800;
     chart.height = 500;
     var ajaxRequest = new  htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getxml',$v('pFlowStepId'));
    ajaxRequest.addParam('x01', 'test');
    ajaxResult = ajaxRequest.get('HTML');
    ajaxRequest = null;
    chart.setData(ajaxResult);
    chart.write('chartDiv');

create an on demand application process (getxml)
Create an application process (on-demand)(authorization not set or ....) which returns the xml by calling the stored procedure.
The limit of 32K is no longer there. The stored procedure is responsible for the layout, colors etc.

Restful services plsql database

To enable restful services from the database (apex) (not restfull services from regions) you must have configured apex listener 2.0 or the weblogic listener.

Enable RESTful services from database
1. Start sqlplus as sys in root-directory of apex
2. Unlock apex_public_user and give new password
3. execute @apex_rest_config
        Set password for apex_rest_public_user and apex_listener (same as apex_public_user?)
  
4. alter user <schema-workspace> grant connect through apex_rest_public_user


Installation of Apex listener (linux)
1. download listener software
2. unpack zipfile
3. install java (if not available) JRE1.6
4. Go to apex-listener directory (apex.war)
5. Define configuration-directory (one level below current directory)

java - jar apex.war configdir $PWD/config

6. Start listener

java - jar apex.war

The first time all kind of configuration information is asked
a. hostname
b. database sid/service
c. port number database
d. location of static resources (path to the images of apex)
e. port http-server listener has to listen on 


URL exampleservice
    http://<host>:<port javalistener>/apex/<workspace>/hr/empinfo/

De service zal default naar '1-ste'  schema gaan waar workspace aan gekoppeld is. Dit schema moet rechten hebben om om via rest-service nar buiten te gaan.

Als sys-user
alter user <> grant connect through apex_rest_public_user;

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;

*/

Creating RESTful webservice in APEX

It's possible to create a report in Apex that works as a service.

To be able to define a webservice the administrator has to make a change in the default configuration of the apex workspace.

1. Allow Restfull access

Only than developers are able to define a report as as webservice.  

1. Create region
2. Modify attributes 

• Define a static id for the region
•  Enable Restful Access, the url will be shown

Ready.    

The url looks like this:


http://<host>/pls/apex/apex_rest.getReport?app=613&page=5&reportid=hekr01&parmvalues=H01&output=xml


app               = Application name/number
page             = Pagenumber
reportid        = Unique identifier report (defined at creation)
output           = Format (XML or JSON)
parmvalues  = additional parameters (value)

The value of <H01> will be assigned to the first itemof region

Use of multiple parameters:
First the values (comma seperated), than the items.

 http://<host>/pls/apex/apex_rest.getReport?app=613&page=5&reportid=hekr01&parmvalues=H01,H02&output=xml

The values are assigned sequentially to the items of the report based on their (internal) ids.
So, H01 will be assigned to the item with the lowest ID and H02 will be assigned to the next item notwithstanding which region.