Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Data conversion

RE: Data conversion

From: Larry G. Elkins <>
Date: Thu, 10 Aug 2000 19:11:54 -0500
Message-Id: <>


There are numerous ways to get Notes data into an Oracle database. What route you want to take depends upon a number of factors. It has been a while since I have had to do any Notes to Oracle or vice versa type of stuff; so, some things may have changed.

You can do it directly using products such as NotesPump, or, other 3rd party products. You can write LotusScript programs to directly read the Notes data and insert into Oracle, or, you can export the data from Notes and use SQL*Loader to blow in the data. This is not an exhaustive list of the ways to accomplish this.

For example, when faced with a one time transfer, I selected the Notes View that contained the data and exported it to a flat file and used SQL*Loader to load it in. It's been a while; but, I think you could also export it out to a 1-2-3 spreadsheet, which was one easy step away from creating a csv file. At times we used the Notes ODBC driver to access the data and create a file -- maybe connecting to the Notes database from MS Acess, bringing in the data, saving off to a csv format file, and then using SQL*Loader to load the data. In one case where the transfer needed to be recurring and they didn't want to buy an additional product, I wrote a LotusScript program (various ones actually using LS:DO, or pure ODBC, or OracleLSX). Even though I wasn't a Notes programmer, through the on-line help examples and some examples at the Lotus support site, I was able to get something done in about a day (a real Notes programmer would probably have a field day laughing at my code; but, it got the job done). Considering I'm not a Notes programmer and I got in going in a day, it obviously wasn't that tough.

If this is a one time conversion of application and its data, maybe they are migrating the app to Oracle, you might just want to dump all the data to files and use SQL*Loader. With all that said, I still had to create the appropriate table(s) on the Oracle side, or, insert/update existing tables. Back when I did this 4 or 5 years ago, it wasn't like there was some tool that would automatically create the Oracle structures and migrate the data. Maybe there is now.

Anyway, I've just barely touched on the surface here. The bottom line is that it is not that difficult to get the Notes data out to a flat file from which point it isn't very tough to then use SQL*Loader to load the data. For your purposes, you might need an approach that does a direct transfer allowing Notes and Oracle to communicate. Once again, it wasn't real difficult to do the coding. Or, if this is an ongoing need to migrate data, maybe you get something like Notespump or some other 3rd party product that prevents you from having to do the coding all.

If you have any questions, feel free to drop me a line. I don't know how much help I can be; but, there were lots of times I had to get data from one into the other.


Larry G. Elkins
The Elkins Organization Inc.

-----Original Message-----
From: []On Behalf Of Yigal Ran Sent: Thursday, August 10, 2000 11:54 AM To: Multiple recipients of list ORACLE-L Subject: Data conversion

  Can any one tell me what are the steps to convert data from Lotus Notes to Received on Thu Aug 10 2000 - 19:11:54 CDT

Original text of this message