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

On Oct 27, 4:05 pm, sybra..._at_hccnet.nl wrote:
> On Mon, 27 Oct 2008 11:10:36 -0700 (PDT), Dave
>
> <David.C.Wat..._at_gmail.com> 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 athttp://otn.oracle.com. free registration required. Hit the tutorial
> tab first.
> Alsohttp://asktom.oracle.commight 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 -- Received on Tue Oct 28 2008 - 08:22:10 CDT