How do I call Oracle Stored Procedure from ASP.NET?

Sign in to leave a comment.

• HG

  Honey Gupta20y agoPosted Feb 16, 2006, 12:45 AM

  I have an oracle stored procedure which i wanna call from asp.net application. the stored proc is: AUTO_NUM(customer_code IN, user_group IN, auto_no OUT) i need the 3rd args as Output. this is the code. I am not getting any error but also the SP is not running. SP is fine as i have tested it in SQL*Plus editor. Thanks in advance. Private Function Generate_PO_Number() As String Dim myconn As New OracleConnection(ConfigurationSettings.AppSettings("ConString")) myconn.Open() Dim sql As String, trans As OracleTransaction Dim lv_custcode As String = "2156117" Dim lv_usergroup As String = "PA" Dim cmd As New OracleCommand("AUTO_NUM", myconn) cmd.CommandType = CommandType.StoredProcedure Dim poNum As String, poLast As Double, newVal As String, autonum As String Try trans = myconn.BeginTransaction() cmd.Transaction = trans Dim custcode As New OracleParameter("CUSTOMER_CODE", OracleType.Char, 7) custcode.Direction = ParameterDirection.Input custcode.Value = CChar(Trim(lv_custcode)) cmd.Parameters.Add(custcode) Dim groupcode As New OracleParameter("GROUP_CODE", OracleType.Char, 2) groupcode.Direction = ParameterDirection.Input Response.Write("Input1: " & lv_custcode) Response.Write("<br>Input2: " & lv_usergroup & "<br>") groupcode.Value = CChar(Trim(lv_usergroup)) cmd.Parameters.Add(groupcode) Dim autono As New OracleParameter(":AUTONO", OracleType.Char, 14) autono.Direction = ParameterDirection.Output cmd.Parameters.Add(autono) Dim sal As String="" sql=cmd.ExecuteScalar() trans.Commit() Catch ex As Exception Response.Write(ex.Message) trans.Rollback() Exit Function End Try cmd.Dispose() myconn.Close() myconn.Dispose() Return sal End Function