Oracle PL/SQL integration with Slack

Ever heard from Slack.com? Slack is the (not “a” but “the”) platform for team communication. It’s a sort of mixture of email, messaging and a lot more. Well, just go to their website. They are a lot better in explaining their own product of course.

For me it got interesting when I saw their API.
I came up with the idea of setting up a channel within our team and let our database post messages on this channel when some triggering event happen.

Now I get whatsapp-like messages on my mobile phone, tablets and desktops from our database. These are the things that makes me very happy!

This is how I did it:

Step 1 – Set up your company
Just go to Slack.com and follow the wizard. It is very self-explaining.

Step 2 – Set up a channel
You will get two channels: #random and #general.
For this demo I created a third channel #notifications by just clicking the “Create a channel” link. slack1

Step 3 – Configure the integration
Click on the name of your company and select “Configure integrations”
slack2

Just look at the wealth of services they offer. Scroll down to the “DIY Integrations & Customizations” and add a Slack API.
You will be directed to https://api.slack.com/. Just read it.
For now go to the section “Authentication” and add a token.

This token is what you need to authenticate the database as a client. (Yes, it will authenticate itself as you).

Step 4 – Get the certificate(s)
The API I use is a https-call that will be implemented in PL/SQL. For this we will need to store some certificates in an Oracle Wallet.

I use chrome as a browser. Other browsers have comparable functionality.

  • Go to https://api.slack.com/
  • Right-click the green lock and go to the certificates. This one is in Dutch but you will get the picture.
    slack3
  • Export the GeoTrust Global CA and the GeoTrust SSL CA-G2 certificates to file as Base64 encoded x.509 (*.cer)
    You don’t need the leaf certificate *.slack.com

Step 5 – Create a wallet
Using the Oracle Wallet Manager open your wallet or create a new wallet. You don’t need a certificate request.
slack4Under “Operations” choose Import Trusted certificate and import both *.cer files we created before.
slack5
Save the wallet to e.g. E:\oracle\Wallet, open the Wallet menu again and enable Auto login. If you don’t do this you will have to embed the password of the wallet in your PL/SQL.
slack6But what if you are using Oracle XE and you don’t have Wallet manager?
Than it’s good to know that the wallet is “just” an PKCS#12 file. Take a look at this excellent post https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/ that explains how to use Openssl for creating the

So execute

  openssl pkcs12 -export -in slack.cer -out ewallet.p12 - nokeys 

and place this wallet in a directory. E.g. C:\Oracle\Wallet ( remember your password in this case. You will need it when you set your wallet in PL/SQL

Step 6 – Create a procedure in the database
The following PL/SQL implements the chat.postmessage API from slack to send a message to the #notifications channel. It uses the wallet in E:\oracle\Wallet with autologin enabled:

declare
  p_url            varchar2(2000):= 'https://slack.com/api/chat.postMessage';
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_text           varchar2(32767);
  l_token          varchar2(1000)  := 'thetokenfromtheslackapisite';
  l_param          varchar2(32676) := 'channel=#notifications'||chr(38)
                                    ||'text=Hello world from the database';
BEGIN
  utl_http.set_detailed_excp_support ( true );  
  utl_http.set_wallet('file:E:\oracle\wallet');
  -- 
  l_http_request  := utl_http.begin_request
                       ( url=>p_url||'?token='||token
                       , method => 'POST'
                       );
  utl_http.set_header
    ( r      =>  l_http_request
    , name   =>  'Content-Type'
    , value  =>  'application/x-www-form-urlencoded'
    );				
  utl_http.set_header 
    ( r      =>   l_http_request
    , name   =>   'Content-Length'
    , value  =>   length(l_param)
    );
  utl_http.write_text
    ( r      =>   l_http_request
    , data   =>   l_param
    );
  -- 
  l_http_response := UTL_HTTP.get_response(l_http_request);
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_http_response, l_text, 32766);
      DBMS_OUTPUT.put_line (l_text);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body 
    THEN
      utl_http.end_response(l_http_response);
  END;
EXCEPTION
  WHEN OTHERS 
  THEN
    utl_http.end_response(l_http_response);
    RAISE;
END;

Happy testing!

Advertisements

Clientside executables e.g. Forms from webpages

Customers will always come up with requirements that will make your head spin.
Some of our applications are of course Oracle APEX based, but there are still some
old client-side applications like Oracle Forms [yes, the ancient client-server version].

We created a web-based intranet with an application overview. With one click on the application the -web- application would start.

Of course they wanted to start the old Oracle Forms from this portal as well and it should work in all the main browsers.

First of all, all client machines are Windows (XP and 7)

Secondly, we were able to deploy a registry setting on all clients.

With the registry settings we defined a custom protocol

[HKEY_CLASSES_ROOT\edwin]
"URL Protocol"=""
@="URL:edwin Protocol"

[HKEY_CLASSES_ROOT\edwin\shell]

[HKEY_CLASSES_ROOT\edwin\shell\open]

[HKEY_CLASSES_ROOT\edwin\shell\open\command]
@="\"X:\\PROGS\\EdwinProtocol\\dispatcher.exe\" \"%1\""

The X-drive is a network drive that is available to all clients.

The dispatcher.exe is a fairly simple custom-made executable that
receives an argument, matches the argument in a configuration-file to an executable
and then starts that executable.

E.g. our configuration file looks like this

[PROTOCOL]
NAME=edwin://

[FORMA]
EXE=X:\Appl\Oracle8_clientT\BIN\ifrun60.EXE XXX00.fmx
WORKING_DIR=X:\progs\XXX\Produktion

With all this in place, starting up an executable from our intranet is as easy as creating a hyperlink with our custom protocol

edwin://FORMA

As always, that’s it.

Using Knockout.js with Oracle Apex

If you’re one of the cool kids in the JavaScript block, you might have heard of Knockout.js. With Knockout you can e.g. use declarative binding to DOM elements and use it to dynamically refresh your UI. Just go through the Tutorials on the site. They are excellent!

Sounds like Dynamic Actions in Oracle Apex, right? But if you are starting to hate the dense forest and – in my humble opinion – bugginess of Dynamic Actions and are looking around for alternatives, I would suggest a look into Knockout.

I will give a very, very simple example to dynamically show a message after inserting a value in a text item. Some of the basic Apex concepts I will not discuss.

First of all, you will have to download the latest version of knockout.js and link it to your page c.q. application. You could also link to a Content Delivery Network (CDN) like cdnjs.com.

The Item where you have to enter the text in has to be bound to the viewmodel. You can use the attributes part of the HTML element.
element

In my example I have a P60_ELI text field which I bind to the observable ELI.

The text that automagically will appear will be shown in another Text Field P60_ELI_VERBRUIK, bound to the knockout id ELI_VERBRUIK.
element2

To put it all together you have to apply the bindings with some small JavaScript in the page:

function getVerbruik(p_x, p_y) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=SomeOnDemandProcedure',0); 
  //get.add('X',p_x); you could pass the input to the process
  //get.add('Y',p_y);
  gReturn = get.get();    
  return gReturn; 
}  

function viewModel() {
  var self = this;  
  self.ELI = ko.observable();
  self.ELI_VERBRUIK = ko.computed( function() { 
                                     return getVerbruik('ELI', self.ELI() ); /* passing through the value that is entered in the observable ELI field, i.e. the P60_ELI item */
                                   }
                                 );
}
/*
  Bind the right viewModel (you could have created several) to the elements on the page. 
*/ 
$(function(){
  ko.applyBindings(new viewModel());
});
 

The function getVerbruik will give some text back from the database using an ON-DEMAND process. You can of course just give back text, but I would suggest to return some JSON. Just because you can.

Using BIRT with Apex for PDF reports

One of the frequently asked questions in the Apex world is “How can I create a PDF report without BI Publisher”. The answers are always the same “use Apache FOP”, “use JasperReports”, but there is one solution that is not frequently given and that is “look at BIRT”. BIRT is an Eclipse-based open source reporting system for web applications, especially those based on Java and Java EE. BIRT has two main components: a report designer based on Eclipse, and a runtime component that you can add to your app server. BIRT also offers a charting engine that lets you add charts to your own application.

Installation

The BIRT site gives an excellent explanation how to install the Birt Report Designer in your Eclipse IDE or how to install a dedicated report designer, based on the same IDE. So no use for me to explain this. I will suffice to say that de report Designer is a Perspective within the IDE ( Window>Open Perspective > Other> Report Design).

The BIRT site also gives a lot of examples how to create a report. The report eventually will be a file with a rptdesign extension. In a nutshell, it goes like this:

  • Create a new project
  • Use as wizard the Business Intelligence and Reporting Tools > Report Project
  • Create a new report within the project using the appropriate template
  • Create the data source
  • Create the dataset
  • Drag and drop the dataset to the appropriate positions on the report
  • Do your layout thing
  • Save the report

Making a connection with Oracle

To create the data source that connects to an Oracle database you have to choose the JDBC Data Source option. However, after a fresh installation the Oracle Driver is not known yet. Click the button Manage Drivers…

making an connection

In the next screen you have to add the JAR file with the driver class. I had an ojdbc6.jar file from an Oracle client installation. As an alternative, if you have a JDeveloper or SQLDeveloper installation at hand, you should be able to find an ojdbcN.jar file.

After you added the JAR file, the Drivers tab will show you two extra drivers: oracle.jdbc.driver.OracleDriver and oracle.jdbc.OracleDriver. Edit the latter and fill in the template URL

edit driver

Now you can choose the Oracle driver in the data source creation wizard and you can fill in the right credentials

Runtime viewer

If you are like me you have always a Tomcat installation at hand. If not, it’s very simple to download at Apache Tomcat. On the BIRT-site you can download BIRT-runtime. From this zip-file, take the WebViewerExample and extract it to your Tomcat/webapps directory. Rename the directory to birt-viewer. Restart Tomcat and go to http://localhost:8080/ (or the dedicated Tomcatserver instead of localhost of course).
Choose the Manager. It should look something like this:

tomcat

Click the birt-viewer hyperlink and choose on the next page the View Example.

next page

But look at the URL

 http://localhost:8080/birt-viewer/frameset?__report=test.rptdesign&sample=my+parameter.

This gives us a clue how to integrate with Apex.

Your report must be copied to the birt-viewer root directory. Look in the BIRT documentation for the setting such that the servlet will look in an alternative location.

The ojdbcN.jar file you used within the IDE must be copied to the Tomcat/lib directory to enable the server to find the right driver. Restart Tomcat after you copied the file.

Integration with Apex

I created a simple report, connected as scott/tiger that selected everything from DEPT and with a parameter P_DEPTNO. The URL to test this report is

http://localhost:8080/birt-viewer/frameset?__report=departments.rptdesign&p_deptno=10&__format=pdf

The __format parameter in the URL controls, well, the format of course. You can also use values like doc and xls.

Knowing this, integration is rather trivial; just generate an hyperlink in any form that navigates to the URL with the right parameter(s).

Oracle Native web services and SOAPUI

You can find several examples on the net to enable native webservices in an Oracle 11g Database. E.g. http://www.liberidu.com/blog/2008/07/08/howto-create-a-native-database-webservice/ is a good starting point to accomplish this.

I tried to test my webservice in SOAPUI and it didn’t work… at first.

I used the testscript from Mark Drake that can be found on OTN

clear screen
clear buffer

connect / AS sysdba

-- call dbms_xdb.sethttpport(80);
-- alter system register;

grant dba, xdbadmin to scott;

alter user scott identified by tiger account unlock;

set echo on
set termout on
set feed on
cle scre
---------------------------------------------------------
-- Show User and "orawsv" new xdbconfig.xml entry
----------------------------------------------------------

show user
--
DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet
    ( NAME => SERVLET_NAME
    , LANGUAGE => 'C'
    , DISPNAME => 'Oracle Query Web Service'
    , DESCRIPT => 'Servlet for issuing queries as a Web Service'
    , SCHEMA   => 'XDB'
    );
  DBMS_XDB.addServletSecRole
    ( SERVNAME => SERVLET_NAME
    , ROLENAME => 'XDB_WEBSERVICES'
    , ROLELINK => 'XDB_WEBSERVICES'
    );
  DBMS_XDB.addServletMapping
    ( PATTERN => '/orawsv/*'
    , NAME => SERVLET_NAME
    );
END;
/

pause
--
def USERNAME=SCOTT
def PASSWORD=tiger
--
pause
clear screen
----------------------------------------------------------
-- Drop the Access Control List XML file if it exists
----------------------------------------------------------
begin
  dbms_network_acl_admin.drop_acl('/public/localhost.xml');
end;
/
commit;
pause
clear screen
----------------------------------------------------------
-- Create and Assign ACL's to localhost
----------------------------------------------------------
begin
  dbms_network_acl_admin.create_acl
    ( '/public/localhost.xml'
    , 'ACL for 127.0.0.1'
    , '&USERNAME'
    , true
    , 'connect'
    );
  dbms_network_acl_admin.assign_acl
    ( '/public/localhost.xml'
    , '127.0.0.1'
    );
end;
/
COMMIT
/
pause
clear screen
----------------------------------------------------------
-- Show and grant NDWS Roles to a user account
----------------------------------------------------------

-- Only HTTPS allowed
GRANT XDB_WEBSERVICES TO &USERNAME
/
-- HTTP also allowed
GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
/
-- Access also allowed that are accessible to PUBLIC.
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO &USERNAME
/
pause
clear screen

connect &USERNAME/&PASSWORD

set echo on
set termout on
set feed on
SET long 10000
clear screen

----------------------------------------------------------
-- Show the now active WSDL URL
----------------------------------------------------------

show user

var url varchar2(700)

BEGIN
  :url := 'http://&USERNAME:&PASSWORD@localhost:'|| dbms_xdb.getHttpPort()|| '/orawsv?wsdl';
end;
/

print url

pause
SET long 10000 pages 0
cle scre

----------------------------------------------------------
-- Show the output via PL/SQL when WSDL URL called
----------------------------------------------------------
SELECT httpuritype( :url ).getXML()
FROM dual
/
pause
cle scre

----------------------------------------------------------
-- Create simple function that can be called via the NDWS
----------------------------------------------------------
create or replace function GET_SQRT
  ( INPUT_VALUE number
  ) return number
as
begin
  return SQRT(INPUT_VALUE);
end;
/

pause
SET long 10000 pages 0
cle scre
----------------------------------------------------------
-- Setting the URL as input for consumption
----------------------------------------------------------
BEGIN
  :url := 'http://&USERNAME:&PASSWORD@localhost:'||dbms_xdb.getHttpPort()||'/orawsv/SCOTT/GET_SQRT';
end;
/
print url

SELECT httpuritype( :url||'?wsdl' ).getXML()
FROM   dual
/
--cle scre

----------------------------------------------------------
-- Consume NDWS function call via PL/SQL
----------------------------------------------------------
set serveroutput on
--
DECLARE
  V_SOAP_REQUEST XMLTYPE := XMLTYPE(
    '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                           xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
                           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <SOAP-ENV:Body>
        <m:SNUMBER-GET_SQRTInput xmlns:m="http://xmlns.oracle.com/orawsv/&USERNAME/GET_SQRT">
        <m:INPUT_VALUE-NUMBER-IN>2>/m:INPUT_VALUE-NUMBER-IN>
        </m:SNUMBER-GET_SQRTInput> 
      </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>'
  );
  V_SOAP_REQUEST_TEXT CLOB := V_SOAP_REQUEST.getClobVal();
  V_REQUEST           UTL_HTTP.REQ;
  V_RESPONSE          UTL_HTTP.RESP;
  V_BUFFER            VARCHAR2(1024);
BEGIN
  V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
  UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
  V_REQUEST.METHOD := 'POST';
  UTL_HTTP.SET_HEADER 
    ( R     => V_REQUEST
    , NAME  => 'Content-Length'
    , VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT)
    );
  UTL_HTTP.WRITE_TEXT 
    ( R    => V_REQUEST
    , DATA => V_SOAP_REQUEST_TEXT
    );
  --
  V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
  LOOP
    UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
    DBMS_OUTPUT.PUT_LINE(V_BUFFER);
  END LOOP;
  UTL_HTTP.END_RESPONSE(V_RESPONSE);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY 
  THEN
    UTL_HTTP.END_RESPONSE(V_RESPONSE);
END;
/

So in SOAPUI I created a new project. As as base-WSDL I used the new http://SCOTT:TIGER@localhost:8080/orawsv/SCOTT/GET_SQRT.
In the properties of the request I entered scott and tiger (lowercase)

And it didn’t work.

Analyzing the http-log of the request I found out that two requests where send. The first one lacked “Authorization: Basic …..” key in the HTTP-header. The response from the server was a 401 HTTP-error indicating the need for a username/pwd. A second request was send with the Authorization Basic key and the server didn’t respond.

The solution is to include the key in the first request.
In the SOAPUI preference in thet HTTP section you have to check the “Adds authentication information to outgoing request”.

Restart SOAPUI and it works.

Using websockets in APEX for automatic refresh with nodejs

In my former post Using websockets in APEX for automatic refresh I explained how to implement a mechanism with an external websocketserver to push a refresh to an APEX page. It used Beaconpush.

But I didn’t like the dependency on an external websocketserver (I’m just that way), so I investigated what it should take to implement my own websocketserver.
Introducing node.js

Node.js is a platform built on Chrome’s JavaScript runtime for easily building fast, scalable network applications. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient, perfect for data-intensive real-time applications that run across distributed devices.

JavaScript is a growing skill amongst Oracle/APEX developers, so it was a logical choice to look into using server-side JavaScript to implement a http/websocketserver combination. The idea was to create a very simple server which the clients could connect to using the websocket-mechanism. The database should be able post requests to the HTTP-part of the server that would be used to the connected clients.

  • Install node.js

On the website of node.js you can download an installer for node.js. I downloaded the msi (I’m on a Windows 7 machine).

Install websocket.

Node.js works with modules that can be installed. For this example we need the websocket module. To install, use the NPM executable

npm install websocket

As a sidenote, this installation failed on my machine. I think the node.js version was not compatible with the websocket version (1.0.4).  I had to install one version lower:

npm install websocket@1.0.3
  • Create the server.js

As I mentioned before, we are talking here about server-side JavaScript. So you have to create a JavaScript file, e.g. server.js

"use strict";

process.title = 'database-chat';

var config = {
  port:1337
};

/* 
  We need a HTTP-server and a websocketserver
*/
var webSocketServer = require('websocket').server;
var http = require('http');

/* 
  We need to keep record of connected clients
*/
var clients = [ ];

/*
   The HTTP server
*/
var server = http.createServer( function(req, resp){
  var body = '';
  var json;
  req.setEncoding('utf8');
  resp.writeHead(200,{'Content-Type':'application/json' });

  req.on('data', function(data) {
    body += data.toString();
    /*
      When we get a stringified JSON-object, that's oke.
      If not, we just create one with one attribute  
    */
    try {
      json = body;
      var test = JSON.parse(json);
    }
    catch (e) {
      json = JSON.stringify({ 'message':body} );
    }  
    /*
      Push the JSON-string to all the connected clients 
    */
    for (var i=0; i < clients.length; i++) {
      clients[i].sendUTF(json);
    }  
    resp.end(JSON.stringify({ message: 'Send to '+ clients.length + ' clients' }));
  });
});

/* Start listening on the configured port an write to standard output*/
server.listen(config.port, function() {
    console.log((new Date()) + " Server is listening on port " + config.port);1
});

/*
   WebSocket server
 */
var wsServer = new webSocketServer({
    // De WebSocket server is tied to a HTTP server.
    httpServer: server
});

/* If a client connects to the websocketserver,
   this callback accepts the request and adds it to the list
*/
wsServer.on('request', function(request) {
    console.log((new Date()) + ' Websocketconnection from origin ' + request.origin + '.');
    var connection = request.accept(null, request.origin); 
    // we need to know client index to remove them on 'close' event
    var index = clients.push(connection) - 1;
    console.log((new Date()) + ' Connection accepted.');

    connection.on('close', function(connection) {
            console.log((new Date()) + " Peer " + connection.remoteAddress + " disconnected.");
            clients.splice(index, 1);
    });

});
  • Start the server

Starting this server.js script is easy. In a command window type

node server.js

You will get something like Newly started server.js

But what is a server without the client?

First of all you will have to have some sort of procedure on the database that will sent a message to your server.js

procedure push 
  ( p_text in varchar2
  )
is
  l_clob clob;
begin
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST
              ( p_url    => 'http://127.0.0.1:1337'
              , p_method => 'POST'
              , p_body   => '{"message":"'||p_text||'"}'
              );
end;

With such a procedure you can e.g. push the message REFRESH to you page and refresh the region.
Create a page with an interactive report that you want to be refreshed.
In the page footer add the following script:

    
$(function(){
  window.WebSocket = window.WebSocket || window.MozWebSocket;

  if (!window.WebSocket) {
    console.log('Sorry, no websockets');
  }
  else {
    var connection = new WebSocket('ws://127.0.0.1:1337');

    connection.onerror = function (error) {
      console.log( 'Sorry, but there is some problem with your connection or the server is down.');
    };

    connection.onmessage = function (message) { 
      console.log(message.data);   
      try {
        var json = JSON.parse(message.data);

        if (json.message=='REFRESH') {
          gReport.pull();
        }
        else { 
          console.log(json.message);
        } 

      } 
      catch (e) {
        console.log('This is not valid JSON: ' + message.data);
      }
    };
  }
});

and that’s it.

Mind you, this is just one implementation, but you can push every database event to the clients page.