Call Oracle stored procedure from VB.Net

Question

_{Thursday, December 12, 2013 8:37 PM}

I am using VB.Net 2010 and 10g.

I want to run a stored procedure in Oracle. This procedure is not a function nor do I expect any rows to return. I just want to run a stored procedure. The stored procedure runs in Oracle just fine. But when I call it in VB it crashes. I think that my code is wrong because I am not calling the procedure the right way. THis is my error: PLS-00201: identifier 'DELETEME' must be declared

Code below:

        Dim oradb As String = "Data Source=XXXX;User ID=XXXX;password=XXXX"
        Dim conn As New OracleConnection(oradb)
        conn.Open()
        Dim cmd As New OracleCommand
        cmd.Connection = conn

        cmd.CommandText = "DeleteMe"  -- DeleteMe is the name of a junk Oracle stored procedure for testing.

        cmd.CommandType = CommandType.StoredProcedure
        Dim dr As OracleDataReader = cmd.ExecuteReader()
        conn.Dispose()

All replies (4)

_{Friday, December 13, 2013 7:46 AM ✅Answered | 1 vote}

cmd.ExecuteNonQuery() is the call for when you do not want any result returned.

As for the error, does the user you are logging in as have the correct permissions to execute "DeleteMe"?

--
Andrew

In addition, this article can tell you how to call Oracle stored procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider:

http://support.microsoft.com/kb/321718

Dim x As Exception
Dim Ds As New DataSet()
Dim Oraclecon As New OracleConnection("Server=YourOracle;Uid=uid;Pwd=pwd")
       

Oraclecon.Open()


Dim myCMD As New OracleCommand()
myCMD.Connection = Oraclecon
myCMD.CommandText = "curspkg_join.open_join_cursor1"
myCMD.CommandType = CommandType.StoredProcedure
myCMD.Parameters.Add(New OracleParameter("n_empno", OracleType.Number)).Value = 123
myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output


Dim MyDA As New OracleDataAdapter(myCMD)
       
Try

    MyDA.Fill(Ds)
Catch x
    MessageBox.Show(x.Message.ToString)
End Try



DataGrid1.DataSource = Ds.Tables(0)

Oraclecon.Close()

Please also check the permission:)

Best Regards,
Please remember to mark the replies as answers if they help

_{Friday, December 13, 2013 3:32 PM ✅Answered}

If it's just an action query, then below is an example that uses ExecuteNonQuery. The Oracle error you are getting indicates that the sp cannot be found. This could be a permissions issue, in which case the user does not have access, or that a public synonymn has non been defined for the sp.

Dim OracleConnectionString As String = "Password=" & password & ";" & _
                                         "User ID=" & userID & ";Data Source=DatabaseName;"

Dim OracleConnection As New OracleConnection(OracleConnectionString)
OracleConnection.Open()

Dim OracleCommand As New OracleCommand
OracleCommand.Connection = OracleConnection
OracleCommand.CommandText = "Packagename.StoredProcedureName"
OracleCommand.CommandType = CommandType.StoredProcedure
OracleCommand.ExecuteNonQuery()

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Thursday, December 12, 2013 10:32 PM | 1 vote}

cmd.ExecuteNonQuery() is the call for when you do not want any result returned.

As for the error, does the user you are logging in as have the correct permissions to execute "DeleteMe"?

--
Andrew

_{Friday, December 13, 2013 6:01 PM}

In addition, this article can tell you how to call Oracle stored procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider:

http://support.microsoft.com/kb/321718

The OracleClient has been deprecated: blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx.

--
Andrew