Stored Procedure with Output Parameters
- From: eny <eny@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 14 Nov 2006 07:00:01 -0800
Hi all
I'm tryng to use MSDAORA access in a Oracle 9 database.
I have a stored procedure named TEST with this Sign :
PROCEDURE TEST
(
PC_01 IN VARCHAR2
,PC_02 IN VARCHAR2
,PO_01 OUT INTEGER
,PO_02 OUT VARCHAR2
)
....
i'have a button that start the store:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
'con oracle:
Dim conn As New System.Data.OracleClient.OracleConnection
conn.ConnectionString = "Data Source=port22;Persist Security
Info=True;User ID=cont_amm;Password=cont_amm;Unicode=True"
conn.Open()
Dim sql As String = "TEST"
Dim cmd As New OracleCommand(sql, conn)
cmd.CommandType = CommandType.StoredProcedure
Dim Par_01 As New OracleParameter("STPAR01", OracleType.VarChar)
Par_01.Size = 400
Par_01.Value = CType("ZZZZZZ", System.Data.OracleClient.OracleString)
cmd.Parameters.Add(Par_01)
Dim Par_02 As New OracleParameter("STPAR02", OracleType.VarChar)
Par_02.Value = "YYYYYY"
cmd.Parameters.Add(Par_02)
Dim Par_03 As New OracleParameter("STPAR03", OracleType.Number)
Par_03.Direction = ParameterDirection.Output
cmd.Parameters.Add(Par_03)
Dim Par_04 As New OracleParameter("STPAR04", OracleType.VarChar)
Par_04.Direction = ParameterDirection.Output
Par_04.Size = 3000
cmd.Parameters.Add(Par_04)
Try
Dim dr As OracleDataReader = cmd.ExecuteScalar
Catch et As OracleException
Dim errorMessage As String = "Code: " & et.Code & vbCrLf & _
"Message: " & et.Message
Label2.Text = errorMessage
End Try
conn.Close()
End Sub
End Class
i cath the exception:
Code: 6550
Message: ORA-06550: line1, column 7:
PLS-00306: wrong number of types of arguments in call to 'TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I'm not sure about cmd.ExecuteScalar
do I miss anything? please help and advise... thanks!
.
- Follow-Ups:
- Re: Stored Procedure with Output Parameters
- From: Bob Barrows [MVP]
- Re: Stored Procedure with Output Parameters
- From: Stephen Howe
- Re: Stored Procedure with Output Parameters
- Prev by Date: Re: problem with WHERE clause when using ADO and Microsoft.Jet.OLEDB.4
- Next by Date: Re: Stored Procedure with Output Parameters
- Previous by thread: Re: problem with WHERE clause when using ADO and Microsoft.Jet.OLEDB.4
- Next by thread: Re: Stored Procedure with Output Parameters
- Index(es):