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.

Advertisements

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.

Disable Oracle 11g ACL

More as a reminder to myself. This can be found everywhere on the web. You should know this when setting up a development environment.
Connect as sys

 begin  
   begin 
     dbms_network_acl_admin.drop_acl( acl => ‘all-network-PUBLIC.xml’ ); 
   exception 
     when others 
     then 
       null; 
   end; 
   dbms_network_acl_admin.create_acl 
     ( acl         => ‘all-network-PUBLIC.xml’ 
     , description => ‘Network connects for all’ 
     , principal   => ‘PUBLIC’ 
     , is_grant    => true 
     , privilege   => ‘connect’ 
     ); 
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE 
     ( acl       => ‘all-network-PUBLIC.xml’ 
     , principal => ‘PUBLIC’ 
     , is_grant  => true 
     , privilege => ‘resolve’ 
     ); 
   dbms_network_acl_admin.assign_acl ( acl => ‘all-network-PUBLIC.xml’ , host => ‘*’ ); 
end; 
/ 
commit;