• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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

Hi,

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

Thanks
0
Cosine_Consultants
Asked:
Cosine_Consultants
2 Solutions
 
Jeff CertainCommented:
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")
                  conn.Open()
                  'create a command...
                  Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM Employees", conn)
                  cmd.CommandType = CommandType.Text
                  'execute...
                  Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                  Dim myTable As DataTable = New DataTable
                  adapter.Fill(myTable)

                  'cleanup...
                  cmd.Dispose()
                  conn.Close()
                  'return...
                  Return myTable
            End Function
0
 
Jeff CertainCommented:
The above code requires that you provide your connection string...
0
 
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.

Bob
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
doobdaveCommented:
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.

HTH,
David
0
 
Bob LearnedCommented:
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)

Bob
0
 
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.
 
0
 
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?
0
 
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)
                         
   IS
                         
   BEGIN
   
      OPEN results FOR
           SELECT * ....

   Where results is returned, using the OPEN FOR.

Bob
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now