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


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

Who is Participating?
Bob LearnedConnect With a Mentor Commented:
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)

Jeff CertainConnect With a Mentor Commented:
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
Jeff CertainCommented:
The above code requires that you provide your connection string...
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Bob LearnedCommented:
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.

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.

Cosine_ConsultantsAuthor Commented:
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.
Jeff CertainCommented:
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?
Bob LearnedCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.