Re: submitting data to oracle

Just building on your thought of external tables, have you thought of the simple straight forward sql*loader, it is still one of the best performer. Just curious, any idea why you decided external tables to load data over sql*loader, though both follow the same approach, one of our test suggested external tables to be good for occasional reads of small file. For bulk loads we still rely on sql*loader and present version comes with lot more features that can improve performance.

Thanks!
amar

On Fri, 2006-02-10 at 16:10 -0500, Chris Stephens wrote:
> 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