ODP.NET - Working with Bind Variables together with OracleParameter
With the help of OracleParameter, you can include bind variables within any SQL statement. These bind variables are nothing but run-time query parameters. The values in the SQL statement are bound at run time when we use bind variables.
If the same SQL statement is being continuously used (with different values), it is recommended to work with bind variables. When you use bind variables in SQL statements, the statements would automatically cache at server level to improve performance during repeated database operations of the same type.
Following is a simple example that includes a bind variable in a SELECT statement followed by OracleParameter, which fills the bind variable with a value:
Sample Code
Imports Oracle.DataAccess.Client
Public Class Form11
Private Sub btnGetEmployee_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnGetEmployee.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
User Id=scott;Password=tiger")
Try
'create command object to work with SELECT
Dim cmd As New OracleCommand("SELECT empno, _
ename, sal, job FROM emp WHERE empno=:empno", cn)
cmd.Parameters.Add(New OracleParameter(":empno",
Me.txtEmpno.Text))
'open the connection
cmd.Connection.Open()
'get the DataReader object from command object
Dim rdr As OracleDataReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
'check if it has any rows
If rdr.HasRows Then
'read the first row
rdr.Read()
'extract the details
Me.txtEmpno.Text = rdr("empno")
Me.txtEname.Text = rdr("ename")
Me.txtSal.Text = rdr("sal")
Me.txtJob.Text = rdr("job")
Else
'display message if no rows found
MessageBox.Show("Not found")
End If
'clear up the resources
rdr.Close()
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
End Class
Copyright exforsys.com
Within the above highlighted code,:empno is the bind variable. We are placing (or assigning) a value into that bind variable using OracleParameter.
If you want to provide a very clear OracleParameter, you can even write something like the following code:
Sample Code
Dim cmd As New OracleCommand("SELECT empno, ename, _
sal, deptno FROM emp WHERE ename=:ename", cn)
Dim pEmpno As New OracleParameter
With pEmpno
.ParameterName = ":ename"
.OracleDbType = OracleDbType.Varchar2
.Size = 20
.Value = Me.txtEname.Text
End With
cmd.Parameters.Add(pEmpno)
Copyright exforsys.com
In the above code fragment, we are working with a bind variable :ename, which is of type VARCHAR2 and size 20. We will deal with OracleParemeter in more detail in subsequent chapters.
Tutorials 
