Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance improvement required :-)

RE: Performance improvement required :-)

From: Seefelt, Beth <>
Date: Fri, 13 Jun 2003 10:31:17 -0700
Message-ID: <>

I'm sure you can. You should see it in an ODBC trace log, or you can use trace events on the database. Here's a really simplistic test I did to verify it. I ran this VB code that executes a really dumb query that could not have come from anywhere else - SELECT DUMMY FROM DUAL WHERE DUMMY = 'X' but passed 'X' as a bind variable

Private Sub Form_Load()

    Dim conn1 As New ADODB.Connection
    Dim cmd1 As New ADODB.Command
    Dim rs1 As New ADODB.Recordset
    Dim STRSQLSTRING As String
    Dim param1 As New Parameter

    strConnect = "UID=produser;PWD=prodpass;DSN=WAREHOUSE;"     STRSQLSTRING = "SELECT DUMMY FROM DUAL WHERE DUMMY = ?"          With conn1

        .ConnectionTimeout = 0
        .CommandTimeout = 0
        .CursorLocation = adUseClient
        .Mode = adModeRead
        .Open strConnect

    End With
    If Err.Number Then
      MsgBox Err.Number
      Exit Sub

    End If
    With cmd1
        .ActiveConnection = conn1
        .CommandText = STRSQLSTRING
        .CommandType = adCmdText
        Set param1 = .CreateParameter("DummyValue", adChar,
adParamInput, 1, "X")
        param1.Value = "X"
        .Parameters.Append param1
        Set rs1 = .Execute

    End With

    MsgBox rs1.Fields("DUMMY")

End Sub

Afterward, executed this on the database -

SQL> select sql_text from v$sqlarea where sql_text like 'SELECT DUMMY %';


SELECT DUMMY FROM DUAL WHERE DUMMY = :1 It shows the parameter was definitely passed as a bind variable.

Check out this document on Metalink -
Retrieving Record Set from Stored Proc Using ADO and VB (SCR 782)

It appears to have an example of passing a cursor back to a recordset, though I've never tried it.

HTH. Beth

-----Original Message-----
Sent: Friday, June 13, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L  

> Why can't you use bind variables? I thought using .Parameters method
> (property?) of ADODB.Command would use bind variables.
I thought it didn't. Any way of checking (other than to get the developers to try it?

> What function, and where can't you use it?

Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB.


Please see the official ORACLE-L FAQ:
Author: Seefelt, Beth

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jun 13 2003 - 12:31:17 CDT

Original text of this message