RE: submitting data to oracle

If you were to install the Oracle client on each machine you could dump the data into delimited files and use sqlldr to upload the data or ftp it to a collection point where a process would upload the files.

You could also check if the free version supports linked databases. If it does you could directly insert the data into Oracle from each SQL Server db.

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Stephens Sent: Friday, February 10, 2006 4:10 PM
To: oracle list
Subject: submitting data to oracle

i am working on a project that has a bunch of sql server databases (the one that comes for free) all over the country. each of these databases will be submitting a substantial amount of data to an Oracle database. the data will run through a web service sitting on a .net app server.

 originally the plan was to submit this data in the form of xml. that has turned out to be unacceptable performance wise.

i suggested creating text files and making use of external tables to load the data. the problem here is that we do not have direct access to the file system. the files would have to be submitted to oracle and then written out to the file system and then loaded. I haven't done any tests here but it seems like kind of a long way around a problem.

  another option is to create a series of insert statements and bundling them up into a clob. i'm not sure that will be much faster (nor scalable) as we would have to write something to strip out the sql statements and then do a bunch of execute immediates.

anyone have any comments on these options or have better options? there has got to be a performant way to do this. it's late on friday and i'm having trouble focusing on anything but the 90 minute IPA i'll be consuming shortly.

chris
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 10 2006 - 15:26:43 CST