Here is agent code that will export data to Oracle via Lotus Connectors:
You will still need an ODBC connection on the Server but you will not need in on the Clients.
This code was not written by me. I came across it years ago and have found it very helpful.
Source and destination datatypes must match.
Using CHAR (Oracle) & text (Notes) is the easiest way to map.
Pain & time is the best way to get this working.
so the code
'OPTIONS
Option Public
'Export Using Connectors
Option Explicit
Uselsx "*lsxlc"
Public Const XFER_NAME_MAPPING = 2
Public Const XFER_SESSION_NAME = "ANY TEXT HERE"
Public Const XFER_SOURCE_CONNECTION = "notes"
Public Const XFER_TARGET_CONNECTION = "oracle8"
Public Const XFER_SOURCE_METADATA = "YOUR NOTES FORM NAME"
Public Const XFER_TRUNCATION_FLAGS = LCFIELDF_TRUNC_PREC
Public Const XFER_RECORD_COUNT = 1
Public Const XFER_STATEMENT = "SELECT (Form = YOUR NOTES FORM NAME' & @Length(SOME FIELD)>3 & SOME_OTHER_FIELD ='1')"
Public Const XFER_FIELD_EXT = 1
'Each time I am moving data to Oracle I am overwriting it
'you could set this to 0 so it will not over write
'or just comment out the truncate command line of code
Public Const XFER_OVERWRITE = 1
Public Const XFER_CREATE = 0
'end of options
Sub Initialize
On Error Goto LSErrorLabel<br/>
Dim session As New LCSession( ) <br/>
Dim record_count As Long<br/>
Dim count As Long<br/>
Dim src_namelist As String<br/>
Dim targ_namelist As String<br/>
Dim stream As New LCStream<br/>
Dim fieldlist As LCFieldlist<br/>
Dim mapped_fieldlist As LCFieldList <br/>
Dim empid As New LCField(LCTYPE_TEXT, 1)<br/>
Dim fullname As New LCField(LCTYPE_TEXT,1) <br/>
Dim staffnumber As New LCField(LCTYPE_TEXT,1) <br/>
Dim pdoc As notesdocument<br/>
Dim db As notesdatabase<br/>
Dim sess As New notessession <br/>
Set db=sess.currentdatabase <br/>
<br/>
Dim src_connect As New LCConnection (XFER_SOURCE_CONNECTION) <br/>
<br/>
Dim targ_connect As New LCConnection (XFER_TARGET_CONNECTION) <br/>
<br/>
<br/>
targ_connect.server = "YOUR ODBC DSN "<br/>
<br/>
targ_connect.UserID = "ORACLE USERNAME"<br/>
targ_connect.Password="ORACLE PASSWORD"<br/>
<br/>
<br/>
src_connect.server= "YOUR NOTES SERVER" <br/>
src_connect.database="YOUR NOTES FILENAME"<br/>
<br/>
src_connect.metadata = XFER_SOURCE_METADATA<br/>
targ_connect.metadata = "YOUR ORACLE TABLE NAME" <br/>
<br/>
src_connect.Connect<br/>
targ_connect.Connect <br/>
record_count = XFER_RECORD_COUNT<br/>
If (record_count = 0) Then<br/>
record_count = 1<br/>
End If<br/>
Set fieldlist = New LCFieldlist (record_count, XFER_TRUNCATION_FLAGS) <br/>
count = src_connect.Execute (XFER_STATEMENT, fieldlist) <br/>
If (XFER_OVERWRITE) Then <br/>
'COMMENT OUT THIS NEXT LINE IF YOU DO NOT WANT TI TO OVERWRITE <br/>
targ_connect.Action LCACTION_TRUNCATE<br/>
End If <br/>
If ((count = 0) Or ((count = LCCOUNT_UNKNOWN) And (fieldlist.FieldCount = 0))) _ <br/>
Goto ResumeLabel <br/>
If (XFER_NAME_MAPPING <> 2 ) Then <br/>
Set mapped_fieldlist = fieldlist<br/>
If (XFER_NAME_MAPPING =1 ) Then<br/>
<br/>
targ_connect.mapbyname="1"<br/>
End If <br/>
Else<br/>
src_connect.mapbyname="1"<br/>
targ_connect.mapbyname="1" <br/>
<br/>
<br/>
src_namelist ="NOTES_FIELD1, NOTES_FILED2" <br/>
targ_namelist ="ORACLE_FIELD1 , ORACLE_FIELD2"<br/>
<br/>
<br/>
<br/>
End If ' end of name mapping section <br/>
<br/>
Set mapped_fieldlist = New LCFieldList(1000,1000) <br/>
Call mapped_fieldlist.MapName (fieldlist, src_namelist, targ_namelist)<br/>
<br/>
count = src_connect.Fetch (fieldlist, 1, record_count)<br/>
While (count > 0) <br/>
Call targ_connect.Insert(MAPPED_fieldlist, 1,count)<br/>
count = src_connect.Fetch (fieldlist, 1, record_count) <br/>
Wend <br/>
<br/>
targ_connect.Disconnect<br/>
src_connect.Disconnect<br/>
Goto ResumeLabel<br/>
' ———————————————————————————————
' here we handle any LotusScript errors
LSErrorLabel:
Dim MessageString As String<br/>
MessageString = Error$ & " at line " & Cstr(Erl()) <br/>
If (Session Is Nothing) Then<br/>
'print MessageString<br/>
<br/>
'print "LEI may not be correctly installed or configured."<br/>
Else<br/>
Dim Msg As String<br/>
Dim MsgCode As Long<br/>
session.GetStatus MessageString, MsgCode, Msg<br/>
If MessageString="" Then<br/>
'print Error$<br/>
strError = Error$<br/>
Else<br/>
'print MessageString<br/>
strError = messagestring<br/>
End If<br/>
End If<br/>
<br/>
<br/>
Resume ResumeLabel <br/>
' ———————————————————————————————–
ResumeLabel:
<br/>
resume next<br/>
End Sub