Bind Variables ADO - Oracle

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: kp (kp_at_noemail.nospam)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 15:40:01 -0600

Hello,

I am trying to call a stored procedure in Oracle from my Visual Basic code.
I am using MS ADO 2.7 library to do this. Everytime I call the stored
procedure it creates a new entry for the SP in the global sql area as if it
is not using bind variables to call the SP. This only happens with numeric
type (NUMBER) of SQL parameters. This in turns tries to fill up the DB
shared pool very soon. Has anyone come across this behaviour while working
with ADO? I have tried this with both Oracle 8i and 9i, same results. any
help would be appreciated.

Thanks,
kp

Below is the code snippet:

Dim strConnect As String:
    strConnect = GetConnectString(strDataSource)
    Set oCon = getNewConnection(strConnect)

    'Prepare the command object ..........................
    Set oCmd = New ADODB.Command
    With oCmd
        .CommandText = "TEST_BINDVARIABLE"
        .CommandType = adCmdStoredProc
        Set .ActiveConnection = oCon
    End With

    'Init the IN and INOUT Params ........................
    oCmd.Parameters.Refresh

   For Each oParam In oCmd.Parameters
        If (oParam.Direction = adParamInput) _
               Or (oParam.Direction = adParamInputOutput) Then
            oParam.Value = arrParamValues(iParamCounter)
        End If
     Next oParam

    'Execute the SP ......................................
    oCmd.Execute

Result:

SQL>select sql_text from v$sql where sql_text like 'begin TEST%';
SQL_TEXT
--------------------------------------------------
begin TEST_BINDVARIABLE(378,182,:V00003); end;
begin TEST_BINDVARIABLE(678,882,:V00003); end;
begin TEST_BINDVARIABLE(1,2,:V00003); end;
begin TEST_BINDVARIABLE(:V00001,:V00002,:V00003); end;

Relevant Pages

  • Sending CLOBs to Oracle using VB/ADO
    ... I have to pass some XML to an Oracle 8i stored procedure, ... without too many problems simply by setting the type of the ADO ... adParamOutput) ...
    (microsoft.public.data.ado)
  • Sending CLOBs to Oracle using VB/ADO
    ... I have to pass some XML to an Oracle 8i stored procedure, ... without too many problems simply by setting the type of the ADO ... adParamOutput) ...
    (microsoft.public.vb.database.ado)
  • RE: Call Oracle Procedure from Excel macro
    ... I can't answer all of your questions for Oracle, ... Set ADO "CommandText" to your stored procedure name and then set the ... In PeopleSoft I was able to build-in the prompts into the stored procedure ...
    (microsoft.public.excel.programming)
  • Missing sign in resultset field
    ... Problems with negative values from an Oracle table / Stored Procedure ... MS ADO Provider ... Only in one laboration I recieve the correct result -2. ...
    (borland.public.delphi.database.ado)
  • Hope springs eternal...
    ... ORACLE to the ADO component ... greater value doesn't translate very well in Delphi. ... you can specify the connection information on a Command item. ...
    (borland.public.delphi.database.ado)