Re: VB - API call to execute procedure, not using bind variables?

From: Shakespeare <>
Date: Tue, 28 Oct 2008 14:48:11 +0100
Message-ID: <49071818$0$182$>

"Mark D Powell" <> schreef in bericht On Oct 27, 4:05 pm, wrote:
> On Mon, 27 Oct 2008 11:10:36 -0700 (PDT), Dave
> <> wrote:
> >We are executing the below statement through a visual basic API and
> >it's executing the SP with the literal values instead of bind
> >variables, thus filling up our library cache with multiple unique
> >statements and hard parsing over and over and over again...
> >Does anyone know how we can change the code in VB to pass it to Oracle
> >and use bind variables?
> >Thanks
> >SCHEMA.STORED_PROC('" & v_ID & "', '" & CStr(Format(v_RepBeginDate,
> >"dd-MMM-yyyy")) & "', '" & CStr(Format(v_RepEndDate, "dd-MMM-yyyy")) &
> >"', " & sched_def_inst & ");
> You don't specify which driver you are using so no exact answer is
> possible.
> The ODBC API is online, generally speaking you replace the actual
> value by a question mark and you issue set parameter calls.
> As to ODP and OLE Oracle has several tutorials on line
> at free registration required. Hit the tutorial
> tab first.
> Also have examples, as well as Oramag.
> --
> Sybrand Bakker
> Senior Oracle DBA

Dave, if you cannot change the code you might depending on your environment get some relief from changing the database parameter, cursor_sharing from the default of EXACT to SIMILAR or FORCE.

Fixing the code is best, but trying SIMILAR for cursor_sharing may be an option. Just be aware that some SQL plans may change adversely because of the setting change and require tuning.

HTH -- Mark D Powell --

Unfortunately, changing the cursor_sharing parameter may even give different results for some queries.
I don't know the status for the most recent Oracle versions, but even with 10.2.xx versions we have noticed buggy behaviour when cursor_sharing is anything different than EXACT.
We run an 'off the shelf' application with a lot of hard coded queries without bind variables and some queries return different results when we change the cursor_sharing to 'FORCE'

Shakespeare Received on Tue Oct 28 2008 - 08:48:11 CDT

Original text of this message