locked
I wnat to return Mutple rows using Oracle Procedure RRS feed

  • Question

  • User-880581886 posted

    I wnat to return Mutple rows using Oracle Procedure.

    If there is any Fine solution Kindly Reply Me.

    Tuesday, May 10, 2011 6:14 AM

Answers

  • User-1161841047 posted

    In .NET Side the same as treating with SQL Server except you need to use System.Data.OracleClient for example instead of System.Data.SqlClien.

    here you can find some examples :

    http://www.connectionstrings.com/oracle

    http://msdn.microsoft.com/en-us/library/aa719764(v=VS.71).aspx

    http://www.codeguru.com/csharp/csharp/cs_network/database/article.php/c8477

     

    In the SP side you need to retrive the data using Cursor .

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 10, 2011 7:59 AM
  • User269602965 posted
    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}.rcSelectCountContracts", conn) 
              cmd.CommandType = CommandType.StoredProcedure 
              cmd.Parameters.Clear() 
              cmd.Parameters.Add("CountContracts", 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 rcSelectCountContracts(CountContracts OUT refCursor); 
    END {PACKAGENAME}; 
    / 
     
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS 
     
    PROCEDURE rcSelectCountContracts(CountContracts OUT refCursor) 
    IS 
    BEGIN 
      OPEN CountContracts FOR 
        SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE FROM {SCHEMANAME}.VW_COUNT_CONTRACTS; 
    END; 
     
    END {PACKAGENAME}; 
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 10, 2011 1:39 PM

All replies

  • User-1161841047 posted

    In .NET Side the same as treating with SQL Server except you need to use System.Data.OracleClient for example instead of System.Data.SqlClien.

    here you can find some examples :

    http://www.connectionstrings.com/oracle

    http://msdn.microsoft.com/en-us/library/aa719764(v=VS.71).aspx

    http://www.codeguru.com/csharp/csharp/cs_network/database/article.php/c8477

     

    In the SP side you need to retrive the data using Cursor .

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 10, 2011 7:59 AM
  • User-880581886 posted

    Hi Thank You,

    But that you provided is only the Connectivity Strings and so on Only.

    But i want using C# I need To provide the data into Dataset Usding Oracle SP.

    Tuesday, May 10, 2011 8:58 AM
  • User269602965 posted
    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}.rcSelectCountContracts", conn) 
              cmd.CommandType = CommandType.StoredProcedure 
              cmd.Parameters.Clear() 
              cmd.Parameters.Add("CountContracts", 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 rcSelectCountContracts(CountContracts OUT refCursor); 
    END {PACKAGENAME}; 
    / 
     
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS 
     
    PROCEDURE rcSelectCountContracts(CountContracts OUT refCursor) 
    IS 
    BEGIN 
      OPEN CountContracts FOR 
        SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE FROM {SCHEMANAME}.VW_COUNT_CONTRACTS; 
    END; 
     
    END {PACKAGENAME}; 
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 10, 2011 1:39 PM