How Do You Retrieve the Results Back from an Oracle Stored Procedure in .NET?

Posted on 2012-09-20
Last Modified: 2012-09-24
I wrote a stored procedure in C# .NET to retrieve the batch process id back in a string format.  

What is the correct way to write a method to retrieve the batch process if back in a string format?

Here is my method:

// Stored Procedure to Retrieve Batch Process Id
public static string GenerateBatchProcessId()
            // Setting Oracle Connection String and Opening the Connection
            OracleCommand cmd;
            OracleConnection connection = new OracleConnection(Constants.ConnectionString);
            cmd = new OracleCommand();
            cmd.Connection = connection;

            string batchProcessId = "";

            IDbCommand command = new OracleCommand();

            //command.Parameters.Add(new OracleParameter("p_CSS_APP_ID", OracleDbType.Int64, appId, ParameterDirection.Input));
            //command.Parameters.Add(new OracleParameter("p_BUS_BATCH_PROCESS_ID", OracleDbType.Varchar2, batchProcessId, ParameterDirection.Input));

            cmd.CommandType = CommandType.StoredProcedure;
            batchProcessId = cmd.ExecuteNonQuery().ToString();

            return batchProcessId;

Please provide a solution.  This method needs to return a batch Id from a stored procedure in string format.


Question by:danielolorenz
    LVL 9

    Expert Comment

    The ExecuteNonQuery method doesn't return any value.

    To do so, you should use a DataAdaptor (da) and a DataSet (ds) like this :
                da.SelectCommand = cmd
                da.Fill(ds, tableMappingName)

    Open in new window

    This code is in VB.NET, though really similar to C#.

    Author Comment

    Is there a way to return the results just to a string?
    LVL 9

    Accepted Solution

    By using
    String myVar = cmd.ExecuteScalar();

    Open in new window

    LVL 9

    Expert Comment

    This returns in fact the first row first column of the above DataSet

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    In one of my recent projects, I was working with IP cameras, I need to take some pictures from the camera and do some processing on it. The first step, was to retrieve the image from camera into Image object. So that it can be displayed or …
    This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now