5 Replies Latest reply: May 6, 2010 2:43 PM by gdarling - oracle RSS

    Error in Read Stored Procedure in VB.NET

    770968
      I am getting the following error.

      ORA=06550
      PLS= 00306

      When I try and get the table of data from the Stored Procedure. Below is my test code. I marked where the error is generated.


      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


      Imports System.Data
      Imports System.Data.SqlClient
      Imports System.Configuration
      Imports System.Data.OracleClient

      Public Class Form1
      Inherits System.Windows.Forms.Form

      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      'Dim Connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
      Dim Command As SqlCommand = New SqlCommand()
      Dim sConnectionstring As String _
      = "Password=testrtm1t;User ID=rtmtest;SERVER=emfadbt;Data Source=emfat;Persist Security Info=true"

      Dim sResult As New DataSet()
      Dim x As Exception
      Dim Oraclecon As New OracleConnection(sConnectionstring)

      Oraclecon.Open()


      Dim myCMD As New OracleCommand()
      myCMD.Connection = Oraclecon
      myCMD.CommandText = "RTM_SP1_TEST"
      myCMD.CommandType = CommandType.StoredProcedure
      myCMD.Parameters.Add(New OracleParameter("CustID", OracleType.VarChar)).Value = "75463"
      myCMD.Parameters.Add(New OracleParameter("CustID", OracleType.Cursor)).Direction = ParameterDirection.Input
      myCMD.Parameters.Add(New OracleParameter("Date", OracleType.VarChar)).Value = "01-APR-08"
      myCMD.Parameters.Add(New OracleParameter("Date", OracleType.Cursor)).Direction = ParameterDirection.Input
      myCMD.Parameters.Add(New OracleParameter("sysdate", OracleType.VarChar)).Value = "01-APR-10"
      myCMD.Parameters.Add(New OracleParameter("sysdate", OracleType.Cursor)).Direction = ParameterDirection.Input
      myCMD.Parameters.Add(New OracleParameter("ResultSet", OracleType.VarChar)).Value = DBNull.Value
      myCMD.Parameters.Add(New OracleParameter("ResultSet", OracleType.Cursor)).Direction = ParameterDirection.Output


      Dim MyDA As New OracleDataAdapter(myCMD)


      Try

      MyDA.Fill(sResult) ------------------------------------------------------- ERROR
      Catch x
      MessageBox.Show(x.Message.ToString)
      Oraclecon.Close()
      End
      End Try



      DataGridView1.DataSource = sResult.Tables(0)

      Oraclecon.Close()

      End Sub
      End Class
        • 1. Re: Error in Read Stored Procedure in VB.NET
          gdarling - oracle
          I'm really not sure what your code is trying to do. It looks like you're adding 8 new parameters, is that really what you're trying to do? 4 pairs of parameters with duplicate names, but with different types?

          I'm assuing your proc only takes 4 params. What type of params? ie, what is the interface of your stored procedure?

          You may want to take it one line of code at a time until you get a feel for chaining calls. For example
                          OracleParameter inoutval = new OracleParameter("myinoutval", OracleDbType.Varchar2, 4000);
                          inoutval.Direction = ParameterDirection.InputOutput;
                          inoutval.Value = "foobar";
                          cmd.Parameters.Add(inoutval);
          
                          OracleParameter param2 = new OracleParameter ("mysecondparam", ... etc  ...
          Hope it helps,
          Greg
          • 2. Re: Error in Read Stored Procedure in VB.NET
            770968
            Sounds like your not famailar with Stored Procedures.

            myCMD.Parameters.Add(New OracleParameter("CustID", OracleType.VarChar)).Value = "75463" Input Varible

            myCMD.Parameters.Add(New OracleParameter("CustID", OracleType.Cursor)).Direction = ParameterDirection.Input tells SP that this is a Input

            myCMD.Parameters.Add(New OracleParameter("Date", OracleType.VarChar)).Value = "01-APR-08"

            myCMD.Parameters.Add(New OracleParameter("Date", OracleType.Cursor)).Direction = ParameterDirection.Input

            myCMD.Parameters.Add(New OracleParameter("sysdate", OracleType.VarChar)).Value = "01-APR-10"

            myCMD.Parameters.Add(New OracleParameter("sysdate", OracleType.Cursor)).Direction = ParameterDirection.Input

            myCMD.Parameters.Add(New OracleParameter("ResultSet", OracleType.VarChar)).Value = DBNull.Value Output Varible

            myCMD.Parameters.Add(New OracleParameter("ResultSet", OracleType.Cursor)).Direction = ParameterDirection.Output Tells SP this is the output
            • 3. Re: Error in Read Stored Procedure in VB.NET
              gdarling - oracle
              Hi,

              I'm quite familiar with how to call stored procedures, which is why I answer posts in a forum from people asking why their code isnt working :)

              Your code really doenst make sense, so I thought breaking it down might help you to understand that you're adding *8* parameters to the stored procedure call. Is that really your intent? Why are you adding IN cursors with names as the same as other parameters?

              I'm assuing your proc only takes 4 params. What type of params? What is the interface of your stored procedure?

              Also, please note that this forum is for .net stored procedures, as opposed to calling plsql procedures from .net. Calling plsql procedures from .net would be more appropriate in the ODP.net forum, but you're not using ODP.NET so generally a MS forum may be more appropriate.

              Greg
              • 4. Re: Error in Read Stored Procedure in VB.NET
                770968
                Your right, I only need these lines of code

                myCMD.Parameters.Add(New OracleParameter("CustID", OracleType.VarChar)).Value = "75463"

                myCMD.Parameters.Add(New OracleParameter("Date", OracleType.VarChar)).Value = "01-APR-08"

                myCMD.Parameters.Add(New OracleParameter("sysdate", OracleType.VarChar)).Value = "01-APR-10"

                myCMD.Parameters.Add(New OracleParameter("ResultSet", OracleType.Cursor)).Direction = ParameterDirection.Output


                Turns out I misstyped the 3rd parameter ("sysdate"). It should have been "SystemDate"
                Compilers are really picky sometimes.
                • 5. Re: Error in Read Stored Procedure in VB.NET
                  gdarling - oracle
                  Glad you got it working.

                  Just to clarify, it wasnt the compiler in that case, it was Microsoft's provider throwing the error. Unlike Oracle's provider which defaults to binding parameters in the order they were added (and disregards the names), Microsoft's provider binds by name so they have to match up.

                  Cheers,
                  Greg