I am new to external dbs.
I have a need to display a list from an Oracle table in the Notes client. Using Oracle Stored procedure, I can read each value individually and load into an array to store and display in Notes. This method appears to be inefficient because the list must be reloaded every time the button is clicked.
Is there a better way to dynamically read a list from an Oracle table and display on the form Notes client? I am thinking of something similar to @DBLookup in the Oracle environment.
Thanks.
Rich
Subject: How to display list from Oracle table in Notes client?
Couple of suggestions.
The ‘Enterprise’ forum is the place to go for interfacing Notes to
Hint: Uselsx “*LSXLC” allows you to read all kinds of data out of Oracle.
If you have lots of data, just pull it into a Notes db and update it as often as you need. I read in ‘parts’ from the bill of material once an hour and then just do dblookups against the Notes db containing Oracle data < 1 hour old. My import code checks for dups and also handles rev changes so I always have current parts in one view and ‘all parts ever entered into Oracle’ in another.
If you’re sucking in small amouts of data that must be fresh, then you’re just going to have to read the Oracle data each time you need it.
HTH
Doug
Subject: RE: How to display list from Oracle table in Notes client?
Thank you for your answer. I found a way to use *LSXLC . I am pulling employee data for 2000 employees. So far, I am able to created corresponding documents in Notes (daily) then read it from the Notes client.
I have not figured out a way to read the Oracle data directly. Do you have sample code that does this? Basically, I am looking for similar logic to DBLookup except lookup to Oracle.
-
lookup in Oracle and present 2000 names to the user in drop down list.
-
user selects a name.
-
based on name - employee data is pulled in from Oracle.
Subject: RE: How to display list from Oracle table in Notes client?
It’s not clear what your ideal is for this. Do you need the data to be up to date “every time you click the button”? If so, I don’t see how you can avoid re-reading the list.
Why do you think the current approach is a performance problem? Do you have actual measurements, or are you just worried about it?
Various caching mechanisms are available in Notes; if you can connect to the Oracle table with ODBC, you can use the @DbCommand function, which keeps its own cache. Or, you can periodically read the values in a server agent and store them in a shared profile document, which is also cached by clients.
A little more information about your application might suggest other strategies.