1 Reply Latest reply: Jul 28, 2012 1:58 PM by Lannie Liberty RSS

    Returning values from Oracle Stored procedures

    user8196955
      I am using VB.Net 2010 and Oracle 10g.


      How do I return values from an Oracle stored procedure using Visual Basic and ODP.Net? I need code examples but what I have found on the Net has been no help.
        • 1. Returning values from Oracle Stored procedures
          Lannie Liberty
          VB.NET code in ASP.NET application

          ' Get data from stored procedure '
          Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
          Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.Parameters.Clear()
          cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
          conn.Open()
          Using oda As New OracleDataAdapter(cmd)
          Dim ds As New DataSet()
          oda.Fill(ds)
          Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
          End Using
          End Using
          End Using
          Catch ex As Exception
          End Try

          Oracle PL/SQL code

          CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
          PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
          END {PACKAGENAME};
          /

          CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS

          PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
          IS
          BEGIN
          OPEN ListBirdNames FOR
          SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
          END;

          END {PACKAGENAME};
          /