This discussion is archived
5 Replies Latest reply: May 6, 2010 12:43 PM by gdarling - oracle RSS

Error in Read Stored Procedure in VB.NET

770968 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points