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

"Mark D Powell" <Mark.Powell_at_eds.com> schreef in bericht news:d8656290-32fc-45f5-b5dc-df449b6e1b5b_at_c60g2000hsf.googlegroups.com... 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 --

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