You can test the oracle connection by installing Oracle from http://www.oracle.com and then setting up the TNSNAMES.ORA File in the ..oracle/..CLIENT/../NETWORK/ADMIN/ directory with your oracle server.
The organisation might have a set TNSNAMES.ORA file available.
If not here is a basic example:
<alias =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>))
)
(CONNECT_DATA = (SERVICE_NAME = <serviceName>))
)
Replace <whatever> with relevant values.
Example values:
<alias> NETDB1
<hostname> Oracle1
<port> 1577
This should be added to or replace the existing TNSNAMES.ORA file in the ADMIN directory .
You can then run NDCtest.exe in the Notes 6.5 paths to test the connection.
When this works, then try setting up a LEI connection. make sure Oracle is installed on the LEI server, and the TNSNAMES.ORA file is on the server.
Setting up a Lotus Notes Connection Document
Set up a Form with the same fields and types as the oracle table for best results. The form can be just for LEI.
Setting up an Oracle Connection Document
The hoststring in the Oracle connection document should be the same as the alias. Some say the service name works as well.
Setting up an Activity
Set up the Connection documents first.
Enable Error log emails for tracking of errors etc.
Error Messages
ORA-12154
TNSNAMES.ORA file on the server does not contain the data source name.
Error: Field count mismatch; Connector: xx, External: yy
You need to change the number of fields in the Notes Form to match the fields in the Oracle Table.
When selecting the table in the oracle connection document the fields are automatically loaded.
Error: [Execute Statement]ORA-00001: unique constraint (<oracle table>) violated ORA-00001
A table has got a unique constraint on it, and you are loading two documents with the same id.
eg if you have a save or replication conflict and you are using UNID as a unique key it will fail!
Error: [Execute Statement]ORA-01401: inserted value too large for column, Connector 'xxxx', Method -Insert- (1401) ORA-01401
This can happen even if you have ticked "Truncate Data when necessary". get the field sizes from the Oracle client and check them with typical values of the documents. One way to do this is to set up a view with each field as a column, with a value such as:
@Length(@Implode(<fieldname>""))
with sorting enabled on the "click on column header to sort" to descend.
Error: Data overflow in field '<fieldname>'
Make the oracle field bigger! See above!
Error: Type mismatch: Connector A field '<notesfield>' is type Text; Connector B field '<oracleTableField>' is type Datetime
On the form used in the Connection document make sure the field types are the same.
Error: Invalid key field '<Notesfield>', Connector '<Notes>', Method -Select-
This is caused by either the first column in the view not being the key field, allowing the server to update the view usually sorts this one out.
Error: Creation of a special view is required for ordered or timestamp selections, but replacement of view '($LEItransfer1)' has not been enabled, Connector 'PDC Test - Notes', Method -Select-
Tick the "allow update / replacement of view" option, or don't use timestamp stuff...
Error: Name is already in use
Check the activity document does not have a conflict.
Error: [Describe Table]ORA-04043: object <user>.<Table> does not exist, Connector '<Connector-Oracle>', Method -Select- (4043) ORA-04043 Error: Metadata object does not exist, Connector '<Connector-Oracle>', Method -Select-
Check the name of the Oracle Table, if you manually entered it, for typos. Pick it automatically for best results.
Error: Invalid key field '<field name>', Connector '<Connector - Notes>', Method -Select-
Change your key field to one that exists on both sources!
Error: [Attach Server]ORA-12154: TNS: palvelun nimeä ei voitu selvittää, Connector '<Oracle>', Method -Connect- (12154) ORA-12154 or in english Error: [Attach Server]ORA-12154: TNS:could not resolve service name, Connector 'Oracle'
Make sure the oracle data source alias name is in the TNSNAMES.ORA file on the server. Probably the first error you will see, when you start out...