Oracle Bind Variables and OLE/DB

[Mutilated1]

Our application accesses Oracle database using OLE/DB. We prepare a command with a rather large select statement and we want to bind a variable to it. Our customer does not allow us to use a stored procedure. Everything works, except the Oracle database is doing a hard parse on Execute everytime.

I was expecting that the execution plan would be cached and we could only need to bind the parameter every time it changes, and even though it works and we get the correct data, the hard parse is killing the performance as there are several hundred users connected at any time, and they each need to do the same query several hundred times per day.

Yes, I know that this architecture sucks, but I didn't create it, I am just charged with trying to find a way to reduce the hard parsing that is occuring.

So far I have not had any success with reducing the hard parses by using a bind variable. Do you think that this is possible and I just need a different approach, or am I attempting something that is fundamentally impossible ?

Thanks!

[Mutilated1]

Well, we changed the application to use OROO40 instead of OLE/DB and now the bind variables work like a charm. I guess the moral of the story is don't believe it when Microsoft says that Bind Variables in OLE/DB will save performance because its not true, actually just sprintfing the variables into the SQL string and executing it works just as well and its a lot easier to program.

Would a moderator please close this topic, as I now have the answer I needed.

Thanks.