Oracle Stored Procedure from ASP.NET in VB.NET

This article shows how to call Oracle stored procedure from ASP.NET.
  • 2732

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)

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.