Try to add the OracleParameters in the same order as defined in SP..... Order DOES Matter?.... http://dotnettips.com/default,month,2004-06.aspx
On Sun, Nov 29, 2009 at 9:55 PM, Dave Zsenak <[email protected]> wrote: > I have a .NET application where I am trying to access an Oracle stored > procedure. I need to pass variables to the procedure and retrieve the > resulting data set. Ideally I would like to be able to loop through > this data to perform summary calculations. I have been researching > this issue for a number of weeks. I haven’t been able to find an > example that is sufficiently complex enough to help. Here are the > relevant bits of code. > Thanks for your assistance. > Dave > > First, the stored procedure. > PROCEDURE BAAN4C.PPV_100_procedure (CRYSTAL_CURSOR IN OUT > CRYSTAL_REF_PKG.CRYSTAL_REF_PKG_TYPE,from_fiscal_period IN > ttfgld106100.t$fprd%type,to_fiscal_period IN ttfgld106100.t$fprd > %type,fiscal_year IN ttfgld106100.t$fyer%type) > AS > BEGIN > OPEN CRYSTAL_CURSOR FOR > SELECT tfgld106.t$dcdt "Date", > (the rest of the select statement follows) > > Here’s my VB code > Public Function Monthly_PPV(ByRef iYear As Integer, ByVal iMonth As > Integer) As Boolean > Dim connStr As String = ConfigurationManager.ConnectionStrings > ("csBaan").ConnectionString > Dim conn As New OracleClient.OracleConnection(connStr) > Dim cmd As New OracleClient.OracleCommand() > cmd.CommandText = "BAAN4C.PPV_100_procedure" > cmd.Connection = conn > cmd.CommandType = CommandType.StoredProcedure > Dim from_fiscal_period As New OracleClient.OracleParameter > from_fiscal_period.OracleType = OracleClient.OracleType.Int16 > from_fiscal_period.Direction = ParameterDirection.Input > from_fiscal_period.Value = iMonth > cmd.Parameters.Add(from_fiscal_period) > Dim to_fiscal_period As New OracleClient.OracleParameter > to_fiscal_period.OracleType = OracleClient.OracleType.Int16 > to_fiscal_period.Direction = ParameterDirection.Input > to_fiscal_period.Value = iMonth > cmd.Parameters.Add(to_fiscal_period) > Dim fiscal_year As New OracleClient.OracleParameter > fiscal_year.OracleType = OracleClient.OracleType.Int16 > fiscal_year.Direction = ParameterDirection.Input > fiscal_year.Value = iYear > cmd.Parameters.Add(fiscal_year) > Dim Crystal_Cursor As New OracleClient.OracleParameter > Crystal_Cursor.OracleType = OracleClient.OracleType.Cursor > Crystal_Cursor.Direction = ParameterDirection.Output > cmd.Parameters.Add(Crystal_Cursor) > conn.Open() > Dim orReader As OracleClient.OracleDataReader = > cmd.ExecuteReader() > conn.Close() > End Function > > I receive the following error message when I execute the code > System.Data.OracleClient.OracleException was unhandled by user code > Code=6550 > ErrorCode=-2146232008 > Message="ORA-06550: line 1, column 7: PLS-00306: wrong number or > types of arguments in call to 'PPV_100_PROCEDURE' ORA-06550: line 1, > column 7: PL/SQL: Statement ignored " > I have clipped out the rest of the error message stack. > >
