In some – usually rare (which means „do something ASAP”) – moments i was asked to do something with the data stored inside lotus notes database, somewhere on domino server. Internet has many sources of knowledge how to get data form and to domino server – as long you are on windows machine, and use some less elegant language like visual basic. Well, can be done this way – or my way. I don’t like compromises and unnecessary complications. It was simple to me, that all I really need is a oracle database which will process information coming from lotus, and then store it in the vast jungle of tables.Oracle has java stored procedures, and lotus notes client comes as java application. Brilliant combination. First step was to find where in the world is Carmen jar file containing required functionality. The answer: NCSO.jar, this file is hidden inside lotus notes client directory, so simple search will reveal path to it.

All one must do to force it to work as personal servant is to load it to oracle database. It’s as simple as

loadjava  -user sa/??@?????.your.tns -genmissing  -resolve -verbose NCSO.jar

Ready and kicking? We will put some raw source code to oracle, to make our fresh jar usable:

CREATE OR REPLACE TYPE hwt_lot_kval AS OBJECT ( NoteID Varchar2(512),key Varchar2(512), lValue varchar2(4000) );
CREATE OR REPLACE TYPE hwt_lot_kval_TABLE AS TABLE OF hwt_lot_kval;

java source named "hwt_lotus_connector"

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
import lotus.domino.* ;
import java.util.Vector;
import java.util.Enumeration;

public class hwt_lotus_connector
static String host = "network.address.of.domino.sewer:63148" ; // server address, it must have running DIIOP connector
//static String SdominoName = "domino/name" ; 
private static DocumentCollection queryLotus(String user, String password, String database, String dominoName , String lquery) throws java.sql.SQLException, NotesException
   Session s = NotesFactory.createSession(host,user,password) ;

         Database db = s.getDatabase(dominoName, database );

public static oracle.sql.ARRAY getDocuments(String user, String password, String database,String dominoName, String query, ARRAY lkeys) throws java.sql.SQLException, NotesException
Connection conn = new OracleDriver().defaultConnection();
String[] data_in = (String[]) lkeys.getArray(); 
DocumentCollection dc = queryLotus( user, password, database,dominoName, query);
int doccount = dc.getCount();
int keyscount = data_in.length;
int obj_id = 0;
String NoteID = "";
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor ("HWT_LOT_KVAL_TABLE", conn);
STRUCT[] idsArray = new STRUCT[doccount*keyscount];
StructDescriptor itemDescriptor = StructDescriptor.createDescriptor("HWT_LOT_KVAL",conn);
STRUCT itemObject1;

Document  doc = dc.getFirstDocument();

for (int i = 0; i<doccount;i++)
NoteID = doc.getNoteID();
for (int ii = 0; ii<keyscount; ii++, obj_id++)
Object[] itemAtributes = new Object[] {NoteID,new String(data_in[ii]), new String(doc.getItemValueString(data_in[ii]))};

itemObject1 = new STRUCT(itemDescriptor,conn,itemAtributes);
doc = dc.getNextDocument(); 

ARRAY myArray = new ARRAY(arraydesc, conn, idsArray);
return myArray;
show errors;

create or replace FUNCTION hwt_lotusdoc(user varchar2,password varchar2, database varchar2,dominoname varchar2, query varchar2 , sta hwt_ll_arr )
 RETURN hwt_lot_kval_TABLE
 NAME 'hwt_lotus_connector.getDocuments(java.lang.String, java.lang.String, java.lang.String,java.lang.String,java.lang.String ,oracle.sql.ARRAY) return oracle.sql.ARRAY';
show errors


If you encounter some kind of nasty security exception this:

call dbms_java.grant_permission('SA', '', '*', 'connect,resolve');

should send them to grave for good.

How to live with it? It’s elementary dear Watson! You could do some magic inside almost unarmed ;) plsql:

set serveroutput on size 999999
--TYPE hwt_ll_arr AS TABLE OF  VARCHAR2(4000);
dta1 hwt_ll_arr:=hwt_ll_arr('field name');

for somme in (select * from table(hwt_lotusdoc('domino login','and password','database_you_want.nsf','your/domino','what_to_search = value',dta1))) loop
dbms_output.put_line(somme.NoteID||' - - '|| somme.key||' - - '||somme.lvalue);
end loop;


Or, more strange and fancy – put the same magic inside simple sql query!:

select * from table(hwt_lotusdoc('your name nad surename','SOME PASSWORD','database.nsf','your/lotus','what = value',hwt_ll_arr('field you want to search')))

Final words: this example code is nothing more than example, not real implementation. It lacks many quirks and whistles that any production grade code should have, so – use it, explore, and extend!