Problem with ref cursor

[GirishSharma]

Hello,

I am using Oracle 10.2.0.1 and working on a web application using MS VS 2005. Here it is my oracle code :

CREATE OR REPLACE PACKAGE rollsearch AS
  TYPE t_cursor IS REF CURSOR;
  Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
  c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
  io_cursor OUT t_cursor);
  END rollsearch;

CREATE OR REPLACE PACKAGE BODY rollsearch AS
  Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
  c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
  io_cursor OUT t_cursor)
  IS
  v_cursor t_cursor;
  v_sql varchar2(1000);
  begin
  ...
  ...
  end rol_cursor;
  end rollsearch;

At oracle level, no error; it is working fine (in SQL Plus).

Here it is my button click code :

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Ds As New DataSet()
        Dim Oraclecon As New OracleConnection("Data Source=orcl;User ID=exam2;Password=exam2")
        Oraclecon.Open()
        Dim myCMD As New OracleCommand()
        myCMD.Connection = Oraclecon
        myCMD.CommandText = "rollsearch.rol_cursor"
        myCMD.CommandType = CommandType.StoredProcedure
        myCMD.Parameters.Add(New OracleParameter("c_rol", OracleType.VarChar, 7)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("c_cnm", OracleType.VarChar, 30)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("c_fnm", OracleType.VarChar, 30)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("c_mnm", OracleType.VarChar, 30)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("d_dob", OracleType.DateTime)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("n_year", OracleType.Number, 4)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("c_exam", OracleType.VarChar, 1)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("c_exam_supp", OracleType.VarChar, 1)).Direction = ParameterDirection.Input
        myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
        myCMD.Parameters.Item(0).Value = Trim(TextBox5.Text) 'Roll Number
        myCMD.Parameters.Item(1).Value = Trim(TextBox1.Text) 'Candidate Name
        myCMD.Parameters.Item(2).Value = Trim(TextBox2.Text) 'Father Name
        myCMD.Parameters.Item(3).Value = Trim(TextBox3.Text) 'Mother Name

        If TextBox4.Text <> "" Then
            myCMD.Parameters.Item(4).Value = Convert.ToDateTime(Trim(TextBox4.Text)) 'Date of birth
        End If

        If Left(DropDownList1.Text, 1) = "S" Then
            myCMD.Parameters.Item(5).Value = 0  'Exam
        Else
            myCMD.Parameters.Item(5).Value = Val(DropDownList2.Text) 'Year
        End If

        If Left(DropDownList1.Text, 1) = "S" Then
            myCMD.Parameters.Item(6).Value = ""  'Exam
        Else
            myCMD.Parameters.Item(6).Value = Left(DropDownList1.Text, 1) 'Exam
        End If

        If Left(DropDownList1.Text, 1) = "S" Then
            myCMD.Parameters.Item(7).Value = ""  'Main Supp
        Else
            myCMD.Parameters.Item(7).Value = Left(DropDownList3.Text, 1) 'Main Supp
        End If
        'MessageBox.Show(myCMD.Parameters.Item(0).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(1).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(2).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(3).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(4).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(5).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(6).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Item(7).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        'MessageBox.Show(myCMD.Parameters.Count, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        Dim MyDA As New OracleDataAdapter(myCMD)
        Try
            MyDA.Fill(Ds)
        Catch Myex As Exception
            MessageBox.Show(Myex.Message, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
        End Try
        datagrid1.DataSource = Ds.Tables(0)
        Oraclecon.Close()
    End Sub

I am not understanding the cause of :

System.Data.OracleClient.OracleException was unhandled by user code
  ErrorCode=-2146232008
  Message="ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ROL_CURSOR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
  Source="System.Data.OracleClient"
  StackTrace:
       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at _Default.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Rollsearch\Default.aspx.vb:line 62
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Please let me know, where i am wrong. Kindly let me know, if i am unclear in question and/or neet more clarification.

Thanks & Regards
Girish Sharma

[jmcilhinney]

The only thing I can see at first look is that OracleType.Number maps to type Decimal and you are assigning a Double to that parameter.

[GirishSharma]

The only thing I can see at first look is that OracleType.Number maps to type Decimal and you are assigning a Double to that parameter.

Thank you for reply. You mean for this line ? :
myCMD.Parameters.Add(New OracleParameter("n_year", OracleType.Number, 4)).Direction = ParameterDirection.Input

But, i am not getting how and why it is wrong? How do i correct this; if it is an issue.

Thank you.

[jmcilhinney]

No, not that line. The line where you assign a value to that parameter. You're using Val, which returns a Double. I don;t know that that is the issue but it was all I could see when i looked.

[GirishSharma]

No, not that line. The line where you assign a value to that parameter. You're using Val, which returns a Double. I don;t know that that is the issue but it was all I could see when i looked.

Ok, you mean : ?

        If Left(DropDownList1.Text, 1) = "S" Then
            myCMD.Parameters.Item(5).Value = 0  'Year
        Else
            myCMD.Parameters.Item(5).Value = DropDownList2.Text 'Year
        End If

I removed val function, but the same error.

Regards
Girish Sharma

[jmcilhinney]

Sure, you removed Val and now you're using a String, which is no better than a Double. I said in my first replay that Number maps to type Decimal, so you need a Decimal. At least, that's what I'm guessing is the issue. Whether it is or it's not, you should always be using the correct types regardless. If you want to specify zero then that should be 0D or Decimal.Zero. Otherwise, you must convert the String you have into a Decimal.

[GirishSharma]

Sure, you removed Val and now you're using a String, which is no better than a Double. I said in my first replay that Number maps to type Decimal, so you need a Decimal. At least, that's what I'm guessing is the issue. Whether it is or it's not, you should always be using the correct types regardless. If you want to specify zero then that should be 0D or Decimal.Zero. Otherwise, you must convert the String you have into a Decimal.

I am again much thankful to you for your prompt replies. But i think issue is not of being double/string, because conversion part will be executed if i select any option from dropdownlist, otherwise it will assign zero value to the parameter. Still, i am getting the same error, even i am not selecting any value from dropdownlist. Here it is current status of that part :

        If Left(DropDownList1.Text, 1) = "S" Then
            myCMD.Parameters.Item(5).Value = Decimal.Zero   'Year
        Else
            myCMD.Parameters.Item(5).Value = Convert.ToInt16(Val(DropDownList2.Text)) 'Year
        End If

Regards
Girish Sharma

[jmcilhinney]

Like I said, I don't know whether this is actually the problem or not but this line:

myCMD.Parameters.Item(5).Value = Convert.ToInt16(Val(DropDownList2.Text)) 'Year

is STILL not doing what I said. You've reinstated Val so you're converting the String to a Double, and then you convert that to an Int16. As I have said several times already, you want a Decimal. Not a String. Not a Double. Not an Int16. A Decimal.

[GirishSharma]

But the thing is conversion part is in else part of IF statement (means, if i select any option from downlist then this conversion issue should appear). I am getting the same error, if i am not not selecting any option (conversion issue) from dropdown list. Kindly bear with me of my less knowledge in dotnet. Please let me know, how do i convert the string to a decimal (in this context).

Meanwhile, i am continue to get the solution...

        If Left(DropDownList1.Text, 1) = "S" Then
            myCMD.Parameters.Item(5).Value = System.Data.OracleClient.OracleNumber.Zero    'Decimal.Zero  'Year
        Else
            myCMD.Parameters.Item(5).Value = Convert.ToDecimal(Val(DropDownList2.Text)) 'Year
        End If

And now, i am getting error :

Requested range extends past the end of the array.

And here is exception from clipboard at the line when it is going to execute datagrid1.datasource=ds.tables(0)

System.IndexOutOfRangeException was unhandled by user code
  Message="Cannot find table 0."
  Source="System.Data"
  StackTrace:
       at System.Data.DataTableCollection.get_Item(Int32 index)
       at _Default.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Rollsearch\Default.aspx.vb:line 67
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Regards
Girish Sharma