By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,871 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,871 IT Pros & Developers. It's quick & easy.

HOWTO : call stored procedure with result function and input/ouput parameters and Oracle database

P: n/a
Here a sample to call a stored procedure with an Oracle Database.

odc.Connection = m_cDb ' use an open connection to your database
odc.CommandType = CommandType.StoredProcedure
odc.CommandText = "pkg_func.GetReqStatus" ' just set the name of the
function, don't used syntax like {? = call
pkg_func.GetReqStatus(?,?,?)}

' the return parameter
odp = New OleDbParameter("result", OleDbType.VarChar)
odp.Direction = ParameterDirection.ReturnValue
odp.Size = 255 ' don't forget the size or you will got an error
odc.Parameters.Add(odp)

' the input parameter
odp = New OleDbParameter("req_id", OleDbType.Integer)
odp.Direction = ParameterDirection.Input
odp.Value = 1024
odp.Size = 10
odc.Parameters.Add(odp)

' the first output parameter
odp = New OleDbParameter("status", OleDbType.VarChar)
odp.Direction = ParameterDirection.Output
odp.Size = 255
odc.Parameters.Add(odp)

' the second output parameter
odp = New OleDbParameter("data", OleDbType.VarChar)
odp.Direction = ParameterDirection.Output
odp.Size = 1024
odc.Parameters.Add(odp)

odc.ExecuteNonQuery()

' now get return values
Dim sResult As String = CType(odc.Parameters("result").Value(),
String)
Dim sStatus As String = CType(odc.Parameters("status").Value(),
String)
Dim sData As String = CType(odc.Parameters("data").Value(), String)

I use the Oracle Client v10.2 and the 'Oracle Provider for OLE DB'
installed with the client.

May 24 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.