ARTICLE

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

Posted by Praveen Kumar Articles | Visual Basic 2010 November 17, 2004
This aticle shows how to call Oracle stored procedure from ASP.NET.
 
Reader Level:

I want to convert my app. from asp to asp.net. Having a lot of fun :-).

The problem that I have now - I can't call Oracle stored proc.

The asp code (works just perfect) is:

Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = GetDBConnection()
objComm.CommandText = "{ CALL praxis_search_pkg.bpmbhg_ref_cursors(?, ?, ?, ?) }"
objComm.CommandType = adCmdText
Set param1 = objComm.CreateParameter("pi_table", adVarChar, adParamInput, 100, pi_table)
objComm.Parameters.Append param1
Set param2 = objComm.CreateParameter("pi_string", adVarChar,adParamInput,500, querystr)
objComm.Parameters.Append param2
Set param3 = objComm.CreateParameter("pi_max",adNumeric, adParamInput, 100, 6)
objComm.Parameters.Append param3
Set param4 = objComm.CreateParameter("po_status_code", adInteger,adParamOutput) objComm.Parameters.Append param4 objComm.Properties("PLSQLRSet") = true
Const cConnection = "Provider=OraOLEDB.Oracle; Data Source=[database]; User ID=[username]; Password=password]; DistribTX=0; ChunkSize=65000; cachetype=file;"
Private Function GetDBConnection()
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = cConnection
objConnection.CursorLocation = adUseClient
objConnection.Open
Set GetDBConnection = objConnection
End Function

The VB.NET code that I wrote is:

Private strProcName As String = "{ CALL praxis_search.bpmbhg_ref_cursors(?, ?, ?, ?) }"
Private objConnect = New OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=praxislinux;User ID=praxisuser;Password=oracle;DistribTX=0;")
Private
sqlCommand As OleDbDataAdapter = New OleDbDataAdapter(strProcName, objConnect)
Private sqlCommand.SelectCommand.CommandType = CommandType.Text
Private param1 As OleDbParameter = New OleDbParameter("pi_table", OleDbType.VarChar, 100, "prx_bpm_index")
Private param1.Direction = ParameterDirection.Input
sqlCommand.SelectCommand.Parameters.Add(param1)
Dim param2 As OleDbParameter = New OleDbParameter("pi_string", OleDbType.VarChar, 500, querystr)
param2.Direction = ParameterDirection.Input
sqlCommand.SelectCommand.Parameters.Add(param2)
Dim param3 As OleDbParameter = New OleDbParameter ("pi_max", OleDbType.Integer, 6)
param3.Direction = ParameterDirection.Input
sqlCommand.SelectCommand.Parameters.Add(param3)
Dim param4 As OleDbParameter = New OleDbParameter ("po_status_code", OleDbType.Integer)
param4.Direction = ParameterDirection.Output
sqlCommand.SelectCommand.Parameters.Add(param4)
Dim ds As DataSet = New DataSet()
ds.DataSetName = "BPM"
sqlCommand.Fill(ds, "Articles")

and Oracle stored proc is declared like this:

PROCEDURE bpmbhg_ref_cursors ( pi_table IN VARCHAR2, pi_string IN VARCHAR2, pi_max IN NUMBER, po_status_code OUT NUMBER)

May be I'm missing something but I get an error:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'BPMBHG_REF_CURSORS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
If I add "PLSQLRSet=1;" to my connection string - I get an error:
"ORA-00604: error occurred at recursive SQL level 1 ORA-01003: no statement parsed"
If I change CommandType to StoredProcedure - I get an error:
"System.Data.OleDb.OleDbException: Unspecified error"
Does anyone know what could be the problem?

Thanks in advance.
nike
 

Never mind. I've got it work.

I've change the syntax to the following:

Private param1 As OleDbParameter = New OleDbParameter("pi_table", OleDbType.VarChar, 100)
Private param1.Direction = ParameterDirection.Input
sqlCommand.SelectCommand.Parameters.Add(param1).Value = "prx_bpm_index"
Dim param2 As OleDbParameter = New OleDbParameter("pi_string", OleDbType.VarChar, 500)
param2.Direction = ParameterDirection.Input
sqlCommand.SelectCommand.Parameters.Add(param2).Value = querystr
Dim param3 As OleDbParameter = New OleDbParameter ("pi_max", OleDbType.Integer)
param3.Direction = ParameterDirection.Input
sqlCommand.SelectCommand.Parameters.Add(param3).Value = 6
Dim param4 As OleDbParameter = New OleDbParameter ("po_status_code", OleDbType.Integer)
param4.Direction = ParameterDirection.Output
sqlCommand.SelectCommand.Parameters.Add(param4)

share this article :
post comment
 

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

 

Posted by Honey Gupta Feb 16, 2006
Become a Sponsor
MOST LIKED ARTICLE
PREMIUM SPONSORS
  • Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon. Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees. As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
    The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
Team Foundation Server Hosting
Become a Sponsor