Oracle stored procedure from ADO.NET

[Jeff Washburn]

Hi All,

Here is the oracle sp...

procedure budget_report(report_cursor out rdsrefcur.genrefcur,
report_teva_id varchar2,
report_year number,
report_start_month number default 1,
report_end_month number default 12)

*************************************************************************

Here is the vb code...

Dim cn As New OleDbConnection("connect string")
cn.Open()
Dim cm As New OleDbCommand
cm.Connection = cn
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "TN_ADMIN.REPORTS.BUDGET_REPORT"

Dim parmReport_id As New OleDbParameter
parmReport_id.DbType = DbType.AnsiString
parmReport_id.Direction = ParameterDirection.Input
parmReport_id.ParameterName = "report_id"
parmReport_id.Value = "A001987"

Dim parmReport_year As New OleDbParameter
parmReport_year.DbType = DbType.Double
parmReport_year.Direction = ParameterDirection.Input
parmReport_year.ParameterName = "report_year"
parmReport_year.Value = 2003

Dim parmReport_start_month As New OleDbParameter
parmReport_start_month.DbType = DbType.Double
parmReport_start_month.Direction = ParameterDirection.Input
parmReport_start_month.ParameterName = "report_start_month"
parmReport_start_month.Value = 2

Dim parmReport_end_month As New OleDbParameter
parmReport_end_month.DbType = DbType.Double
parmReport_end_month.Direction = ParameterDirection.Input
parmReport_end_month.ParameterName = "report_end_month"
parmReport_end_month.Value = 11

cm.Parameters.Add(parmReport_id)
cm.Parameters.Add(parmReport_year)
cm.Parameters.Add(parmReport_start_month)
cm.Parameters.Add(parmReport_end_month)

Dim rdr As OleDbDataReader = cm.ExecuteReader
DataGrid1.DataSource = rdr
DataGrid1.DataBind()
cn.Close()

*************************************************************************

When I run this thing I get...

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'BUDGET_REPORT' ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

*************************************************************************

Does anyone have any idea what is wrong? This thing is driving me
nuts.

Thanks,
Jeff

 

[Miha Markic]

Hi Jeff,

I think you are missing cursor parameter:
Dim cursorParameter as new
System.Data.OracleClient.OracleParameter("report_cursor",
System.Data.OracleClient.OracleType.Cursor, 0,
System.Data.ParameterDirection.Output, false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null);

cm.Parameters.Add(cursorParameter)

You should also use OracleDataAdapter with cm as select command and do
something like:

Dim dt as new DataTable

adapter.Fill(dt)

 

[Fire Garden]

Hi Jeff ,

Look again the data types that you have in OleDbParameters.Are you sure that
varchar2 datatype in oracle equals to datatype.AnsiString?
Or else the problem is inside sp.

Chris