Oracel SP call from VB: ASP3 vs ASP.NET

[vituja123]

Hi All,

I am trying to convert my old ASP3 code to asp.net(1.1) The old code works fine and there isn't an issue with the SP but the ASP.NET code gives me this error below:

Run-time exception thrown : System.Data.OracleClient.OracleException - ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TRANSFER_SAMPLE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


------------------------------------------------------------------------
ASP.NET
Dim cmd As OracleCommand = New OracleCommand
Dim rowId As OracleString

'*************
Dim Tnum As String
Dim OID As String
Dim NID As String
Dim OutResult As String
Tnum = "049990008001B01P01"
OID = "ALLWMA"
NID = "VITUJA"

cmd.Connection = OraConnClient
cmd.CommandText = "pta_auction_pkg.Transfer_Sample"
'cmd.CommandText = "pta_auction_pkg.Transfer_Sample(?,?,?, {resultset 0, result})"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("p_ORIGNALID", OracleType.VarChar, 6).Value = OID
cmd.Parameters.Add("p_NEWID", OracleType.VarChar, 6).Value = NID
cmd.Parameters.Add("p_TNUMBER", OracleType.VarChar, 20).Value = Tnum
cmd.Parameters.Add("result", OracleType.VarChar, 256).Direction = ParameterDirection.Output

OraConnClient.Open()
Dim rowsAffected As Integer = cmd.ExecuteOracleNonQuery(rowId)
OraConnClient.Close()
-----------------------------------------------------------
OLD ASP3 Classic

Dim cn, rs, cmd, param
Dim SQL

set cn = Server.CreateObject( "ADODB.Connection" )
cn.Open CONNECT
SQL = "{call pta_auction_pkg.Transfer_Sample(?,?,?, {resultset 0, result})}"

set cmd = server.CreateObject ("ADODB.Command")
with cmd
set .ActiveConnection = cn
.CommandText = SQL
.CommandType = 1 'adCmdText

set param = .CreateParameter("p_ORIGNALID", adVariant , 1 , ,OLDID)
.Parameters.Append param
set param = .CreateParameter("p_NEWID", adVariant , 1 , ,NEWID)
.Parameters.Append param
set param = .CreateParameter("p_TNUMBER ", adVariant , 1 , ,TNUMBER)
.Parameters.Append param
end with

set rs = server.CreateObject ( "ADODB.Recordset" )
set rs = cmd.execute

Transfer_SAMPLE=rs(0).value

Set cmd = Nothing
Set param = Nothing
rs.close
Set rs = Nothing
cn.close
Set cn = Nothing

 

[vituja123]

I just figured it out. The output parameter "result" is a CURSOR datatype within the SP. So I made the datatype match accordingly and it worked here.