dcsimg
Results 1 to 9 of 9

Thread: Problem with ref cursor

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2009
    Posts
    33

    Problem with ref cursor

    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 :
    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;
    Code:
    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 :
    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 :
    Code:
    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

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,868

    Re: Problem with ref cursor

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2009
    Posts
    33

    Re: Problem with ref cursor

    Quote Originally Posted by jmcilhinney View Post
    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.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,868

    Re: Problem with ref cursor

    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.

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2009
    Posts
    33

    Re: Problem with ref cursor

    Quote Originally Posted by jmcilhinney View Post
    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 : ?
    Code:
            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

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,868

    Re: Problem with ref cursor

    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.

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2009
    Posts
    33

    Re: Problem with ref cursor

    Quote Originally Posted by jmcilhinney View Post
    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 :

    Code:
            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

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,868

    Re: Problem with ref cursor

    Like I said, I don't know whether this is actually the problem or not but this line:
    Code:
    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.

  9. #9

    Thread Starter
    Member
    Join Date
    Feb 2009
    Posts
    33

    Re: Problem with ref cursor

    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...
    Code:
            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)

    Code:
    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
    Last edited by GirishSharma; Jul 13th, 2010 at 01:52 AM. Reason: Some more addition

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width