Unnecessary calls to DBMS_DESCRIBE Oracle Stored procedure

From: Hooman (Hooman_at_discussions.microsoft.com)
Date: 01/27/05 

Date: Wed, 26 Jan 2005 17:33:06 -0800

I have utilized Microsoft Activex Data Object 2.6 library to call some Oracle
stored procedures from my COM+ object. It works fine, but the trace data
shows that we have extra calls to DBMS_DESCRIBE.

It actually happens when we are calling the stored procedures that are going
to return a result set in one of their out put parameters.

It seems that the problem is related to the implementation of ORACLE OLE/ADO
Provider (Driver). I found this discussion in a web site:

************************************************
The problem is supposed to be fixed in the Oracle 9.2 provider...

Here is a short excerpt from Oracle Support in one of the posts:

"Apparently what is happening is the provider (ADO/OLEDB) calls
OCIStmtExecute twice. Once with DESCRIBE_ONLY to do an explicit describe and
again to execute. The parse stat count is being incremented twice, once for
each execute call. The parse happens only during the first call which is a
hard parse. On the second call, the count is being incremented even though a
hard parse is not made. As a result, the parse count shows 2 even though it
is actually being parsed only once, the first time."

************************************************

I already checked version of the Oracle OLE/ADO provider in our server and
it is 9.2, so that bug has not been fixed in that version.

Do you have any idea to prevent those extra calls?

Thanks,
Hooman Malek

Relevant Pages

  • Re: Statspack Help Please
    ... High logical reads probably points to badly untuned SQL ... >Module: JDBC Thin Client ... >Notice that the Parse & Executions are mostly the same. ... Sybrand Bakker, Senior Oracle DBA ...
    (comp.databases.oracle.server)
  • Re: Statspack Help Please
    ... > Shared Pool Size: 112M Log Buffer: 512K ... > Notice that the Parse & Executions are mostly the same. ... Cost Based Oracle: Fundamentals ...
    (comp.databases.oracle.server)
  • Cannot Insert into Oracle
    ... I'm trying to insert into an oracle db. ... connection it seems, I can retrieve records from the db just fine. ... the line with the regex in it is just to parse the text file so ...
    (perl.dbi.users)
  • Oracle Packages
    ... to the Oracle Database through OLE DB Provider for Oracle and I'm seeing the ... Tables,Views and Stored Procedures in the Server Explorer. ...
    (microsoft.public.dotnet.framework)
  • Safe way to escape form variables to insert in sql (to oracle)
    ... I'm connecting to a oracle db via ODBC (can't use native oracle functions) and i need to parse the input from a form to insert in a sql query. ... I would like to know if using placeholders is enough or should i do something else to have safe queries without strange chars. ...
    (alt.php)