Connect to Oracle and Run a PL/SQL (Procedure or function)

Posted on 2005-05-09
Last Modified: 2008-02-01

I want to connect to oracle using a and then execute a PL/SQL procedure or function that returns a cursor (select * from employees).

Question by:Cosine_Consultants
    LVL 24

    Assisted Solution

    by:Jeff Certain
    Well, using the ADO .Net model, you don't pass a cursor back -- you populate an object with the data. The ADO .Net model is "disconnected".
    Imports System.Data

                Public Shared Function GetMyTable() As DataTable
                      Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection("yourConnectionString")
                      'create a command...
                      Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM Employees", conn)
                      cmd.CommandType = CommandType.Text
                      Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                      Dim myTable As DataTable = New DataTable

                      Return myTable
                End Function
    LVL 24

    Expert Comment

    by:Jeff Certain
    The above code requires that you provide your connection string...
    LVL 96

    Expert Comment

    by:Bob Learned
    If you have VB.NET 2003, then you can use the OracleClient also, or you can use the the .NET provider from Oracle, like we do.

    LVL 8

    Expert Comment

    Just to add to what Chaosian and TheLearnedOne have said:

    I was recently delving into Oracle (having worked with SQL Server for a few years) and found that you do indeed have to return a cursor in your stored procedure, in order to get back a RESULT SET.
    If you already have the stored procs set up in Oracle, then the code provided by Chaosian should work.

    LVL 96

    Accepted Solution

    Something like this:

        Dim selectCommand As New OracleCommand
        Dim myDA As New OracleDataAdapter

          selectCommand.Connection = myDBConn
          selectCommand.CommandType = CommandType.StoredProcedure
          selectCommand.CommandText = packageName
          selectCommand.Parameters.Add("client_in", Client.ToUpper)
          selectCommand.Parameters.Add("org_type_in", OrgType.ToUpper)
          selectCommand.Parameters.Add("cmd_in", Command.ToUpper)
          selectCommand.Parameters.Add("sub_in", Subcommand.ToUpper)
          selectCommand.Parameters.Add("org_in", Org.ToUpper)
          selectCommand.Parameters.Add("symbol_in", Symbol.ToUpper)
          selectCommand.Parameters.Add("max_letters_in", maxLetters)

          'Output result set
          selectCommand.Parameters.Add("results", OracleDbType.RefCursor, ParameterDirection.Output)


    Author Comment

    I have used Chaosian Code and it works. But how can I run my stored Procedure to return a cursor. I tried both Chaosian and TheLearnedOne code to run my stored procedure but it does not work.
    LVL 24

    Expert Comment

    by:Jeff Certain
    That's the whole "problem" in this case. Microsoft went with a model intended to optimize datasets for disconnected use (i.e. web app use). When they made that decision, they implemented a model that only allows you to grab the data and operate on it locally. If you want to treat the dataset as if it is attached directly to the database, you need to use the DataAdapter's Update method to update the database after you make changes.

    BTW: you can make all your changes locally, then batch update the database using the update method -- this method examines the rowstate for each row in the specified table and performs the appropraite operation on altered rows. Unaltered rows are ignored.

    Hope this helps -- or have I missed the point?
    LVL 96

    Expert Comment

    by:Bob Learned
    Does your Stored Procedure look something like this?

      PROCEDURE get_layouts
           (layout_id_in   IN    org_chart_layouts.layout_id%TYPE,
            results        OUT   orgchart_pkg.result_type)
          OPEN results FOR
               SELECT * ....

       Where results is returned, using the OPEN FOR.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now