471,853 Members | 1,588 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

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 3683

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
NeoPa
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.