Re: VB - API call to execute procedure, not using bind variables?
Date: Tue, 28 Oct 2008 04:17:11 -0700 (PDT)
Message-ID: <de544810-6e33-4c73-81c2-cb6fb1007426@d45g2000hsc.googlegroups.com>
On Oct 27, 2:10 pm, 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 & ");
Assuming that your VB program (note that the syntax is a bit different for VB.Net) is using ADO to connect to the database, the commands would look something like this:
Dim comStoredProc as ADODB.Command
Set comStoredProc = New ADODB.Command
With comStoredProc
'Set up the command properties .CommandText = "SCHEMA.STORED_PROC( ?, ?, ?, ?)" .CommandType = adCmdStoredProc .CommandTimeout = 30 .ActiveConnection = conDatabase 'ADODB.Connection object 'Add the bind variables .Parameters.Append .CreateParameter("v_id", adVarChar, adParamInput, 30, v_ID) .Parameters.Append .CreateParameter("begin_date", adDBTimeStamp, adParamInput, 8, cDate(v_RepBeginDate)) .Parameters.Append .CreateParameter("end_date", adDBTimeStamp, adParamInput, 8, cDate(v_RepEndDate)) .Parameters.Append .CreateParameter("sched_def_inst", adNumeric, adParamInput, 16, sched_def_inst) .Prepared = True
End With
comStoredProc.Execute
Later, when you need to re-execute the same stored procedure, you would use the following, rather than the full setup as above, assuming that comStoredProc is still in the variable scope:
comStoredProc("v_id") = v_ID comStoredProc("begin_date") = cDate(v_RepBeginDate) comStoredProc("end_date") = cDate(v_RepEndDate) comStoredProc("sched_def_inst") = sched_def_instcomStoredProc.Execute
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Oct 28 2008 - 06:17:11 CDT