Returning Value from stored procedure call

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Allen

Returning Value from stored procedure call

Post by Allen » Tue 19 Apr 2005 01:09

The code I have so far is as follows… not sure if its correct … so please fix where needed:

The only value I’m really interested in returning is pi_maxtransaction which I call MaxTransactionNumber in a OracleParameter…

public DataSet GetMaxTransactionValue_Package()
{

string ConnectionString = "Server=localhost;User Id=username;Sid=databasename;Password=password;Direct=True;";

OracleConnection Connection = new OracleConnection( ConnectionString );
Connection.Open();

OracleCommand Command = new OracleCommand();
Command.CommandText = "pkg_one.getmaxtransactionorders";
Command.CommandType = System.Data.CommandType.StoredProcedure;

OracleParameter MaxTransactionNumber = Command.Parameters.Add("PI_MAXTRANSACTION", OracleDbType.Number );
MaxTransactionNumber.Direction = ParameterDirection.Output;

OracleParameter Status = Command.Parameters.Add("PS_STATUS", OracleDbType.VarChar );
Status.Direction = ParameterDirection.Output;

OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
oracleDataAdapter.SelectCommand = Command;

//IS THE FOLLOWING NEEDED…. COULD I JUST RETURN MaxTransactionNumber.value.ToString(); after the ExecuteNonQuery()

OracleCommandBuilder oraCommandBuilder = new OracleCommandBuilder();
oraCommandBuilder.DataAdapter = oracleDataAdapter;

DataSet ReturnData = new DataSet();

Command.ExecuteNonQuery();

oracleDataAdapter.Fill(ReturnData, "Table");

return ReturnData;
}


Here is an example of the Package header … its works fine but I;m not sure how to interface to it in code and what to return from the package stored procedure call?

CREATE PACKAGE Pkg_one

TYPE r_cursor IS REF CURSOR;

PROCEDURE GETMAXTRANSACTIONORDERS ( pi_maxtransaction OUT iNTEGER, ps_status out varchar2 );

PROCEDURE GETINVOICEINFO ( ps_invoicenumber IN VARCHAR2, prc_cursor OUT R_CURSOR, ps_status OUT VARCHAR2);

END Pkg_Plus;

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Thu 21 Apr 2005 08:41

You can use the following code

Code: Select all

    public DataSet GetMaxTransactionValue_Package() {                       
      
      string ConnectionString = "Server=localhost;User Id=username;Sid=databasename;Password=password;Direct=True;";
      OracleConnection Connection = new OracleConnection( ConnectionString ); 
      Connection.Open();                                                                                                        
                                               
      OracleCommand Command = new OracleCommand();
      Command.CommandText = "pkg_plus.GETINVOICEINFO";
      Command.CommandType = System.Data.CommandType.StoredProcedure;
 
      Command.CreateParameters();
      command.ExecuteNonQuery();

      OracleParameter p2 = command.Parameters["prc_cursor"];
      OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
 
      DataSet ReturnData = new DataSet();
      oracleDataAdapter.Fill(ReturnData, "Table", (OracleCursor)p2.Value);
      return ReturnData;                      
    }

Code: Select all

    public DataSet GetMaxTransactionValue_Package() {                       
                                                
      string ConnectionString = "Server=localhost;User Id=username;Sid=databasename;Password=password;Direct=True;";
      OracleConnection Connection = new OracleConnection( ConnectionString ); 
      Connection.Open();                                                                                                        

      OracleCommand Command = new OracleCommand();
      Command.CommandText = "pkg_plus.GETINVOICEINFO";
      Command.CommandType = System.Data.CommandType.StoredProcedure;
      Command.CreateParameters();

      OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
      oracleDataAdapter.SelectCommand = Command;

      DataSet ReturnData = new DataSet();
      oracleDataAdapter.Fill(ReturnData, "Table");
      return ReturnData;                      
    }

Post Reply