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

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

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_inst

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Oct 28 2008 - 06:17:11 CDT