Bind Variables ADO - Oracle

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;

Hi kp,

First of all, I would like to confirm my understanding of your issue. I'm
not quite sure what has occurred from your description. Could you let me
know what kind of entry is created when the SP is called?

It would also be helpful if you could post your SP here, with both actual
results and expected results. Thank you!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."