Re: oci to sql conversion?

Pieter Breed schrieb:
> Hi,
>
> I have a component that uses the OCI library to do spatial index
> queries. The component is badly written and is based on examples. To
> state it shortly, our company has very little experience with oracle
> and OCI, and the maintenance on the component is a mess. Is it possible
> to rewrite these queries using some flavour of SQL?
oci does use sql.
If you look through your code very carefully, you'll probably come across a bunch of sprintf calls that build the selects and inserts and so on.

> If I can do this, I can connect directly to oracle using some ODBC
> driver

Uh - oci *is* the direct way. Everything else is indirect and few things are more indirect than ODBC.

> and can write my queries in a more maintainable language (like
> c#) with normal sql techniques.

If you are on the windows platform I'd not use ODBC (Microsoft discourages it too now) but use a more modern driver. Oracle offers the OLE DB and ADO ODP.NET) driver and if you just want small scripts, OO4O is a nice COM interface for the OLE DB driver, usable from VBScript and JScript.

But another thing is that oci still offers some capabilities, the other APIs don't. Especially the direct path load API. If your legacy app uses that, for instance for non-spatial data, you may lose some speed.

Also, since you appear not to have much experience in oracle programming, here are the usual performance hints:

• for loading data use array binding (factor 10 plus much decreased load on the database, definitely a must)
• for spatial data, disable indexes during load (factor 2-3, may not be an option for you) That way you *may* reach the speed of the original application. Also, seriously consider sql*loader instead of a homegrown solution.

> I have oracle 8i.

You shouldn't. It's no longer supported by oracle. Going 10g isn't that difficult.

Also, in case you run any benchmarks with OLE DB and ADO with regards to spatial object creation and initialization in the bind arrays I would be very interested in any results.

In a few months we will probably move away from sql*loader because we want to use temporary tables for our data. We are not sure which API is better for this. I *do* know that OO4O is not really fast when it comes to objects and I'm curious whether this is purely COM overhead and also if the ODB.NET uses the .NET memory management. That *might* offset the speed disadvantage of C#'s managed code.

Lots of Greetings!
Volker Received on Thu May 04 2006 - 11:27:24 CDT