469,299 Members | 2,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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

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
0 3594

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by William Buchanan | last post: by
6 posts views Thread by Eugene A | last post: by
1 post views Thread by John Sidney-Woollett | last post: by
12 posts views Thread by Newbie | last post: by
6 posts views Thread by Ian Boyd | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.