I've searched Google for hours and haven't found a clear answer so decided to prompt a post in hopes of finding a better result.  I've got some wrapper code that calls the Ent. Library DAAB for Oracle.  Essentially I pass in the Stored Proc name, data access type (SQL or Proc), and database type (Oracle, SQL, DB2).  Based on which items get passed it calls ends up calling ExecuteScalar(dbCommandWrapper, dbIns, dbIns2).  Which then steps into Ent. Library to call Oracle cs classes associated to calling the apprpriate execution. 

I've test the Procedure directly against Oracle with no issues.  I've test the wrapper classes by changing the procedure into direct SQL and everything works fine.  However once I use a procedure passed to ExecuteScalar against Oracle, it always returns the object as 'Nothing'.  The ExecuteScalar works for SQL Server and DB2 when calling a procedure but not Oracle. 

I read one article that indicated that this is because DAAB doesn't allow out parms with ExecuteScalar and that Oracle requires return values to be passed as Out parameters.  Then it goes on to say that a person would need to do all transactions using ExecuteNonQuery instead of ExecuteScalar.  I can get ExecuteNonQuery to work, but not ExecuteScalar.  I just want to confirm Oracle's relationship with MS DAAB and the procedure regarding returns before doing away with using scalar anywhere.  I also am wondering if I should take the time to rewrite the Ent. Lib. to use the ODP.NET connections instead of Microsoft's and if by doing this ExecuteScalar becomes available again.

Suggestions?

There is some bug in that. It never worked for me.. follow this http://forums.asp.net/t/1170399.aspx , I made a work around, instead of using executeScalar, I created RefCursor in SP and used ExecuteReader(). Hope this helps

thanks for the quick response. I'll check out the link you included.  I think I already saw that link but it didn't have and actual final fix for ExecuteScalar which would make sense since you are using nonquery/reader instead.

Does anyone know if this is a legitamite problem in 1.1 and is resolved in later framework versions of the Ent. Lib. or should I plan to use ExecuteReader/ExecuteNonQuery for these types of efforts going forward?

Thanks again.

Can you post an example proc? I can see if it works in the EntLib 3.1, and maybe get you a solution for 1.1. 

CREATE OR REPLACE PACKAGE BODY SCHEMAOWNER.PACKAGENAME AS

PROCEDURE SP_PROC1 (xID IN VARCHAR2)
IS
placeholder INTEGER;
BEGIN
SELECT DISTINCT 1 INTO placeholder
FROM
    TABLENAME1 A, TABLENAME2 A1
WHERE
    A.ID=xID
    AND
    A.ID=A1.ID;
    
END SP_PROC1;

END PACKAGENAME;

I Googled a lot for this, as I'm sure you did, and found that nobody has come up with a really good answer. You are correct that ExecuteScalar does not work as advertised with stored procedures. However, it does work with a plain SQL string. So it seems you have two possible solutions. Either don't use stored procs when using ExecuteScalar, or you'll have to go with the following (which is the only way I could get it to work):

static void Main(string[] args)
{
    try
    {
        object obj = TestDynamicSqlScalar();
        string s = obj.ToString();
        Console.WriteLine("Dynamic SQL scalar: {0}", s);

        obj = TestStoredProcScalar();
        s = obj.ToString();
        Console.WriteLine("Stored proc scalar: {0}", s);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        Console.WriteLine();
        Console.WriteLine(ex.StackTrace);
    }
    finally
    {
        Console.WriteLine();
        Console.WriteLine("Press <ENTER> to continue...");
        Console.ReadLine();
    }
}

public static object TestDynamicSqlScalar()
{
    //  Create the Database object, using the default database service. The
    //  default database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase();

    string sqlCommand = "SELECT COUNT(*) FROM test";
    DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

    return db.ExecuteScalar(dbCommand);
}

public static object TestStoredProcScalar()
{
    //  Create the Database object, using the default database service. The
    //  default database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase();

    string sqlCommand = "testscalar";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    db.AddOutParameter(dbCommand, "rc", DbType.Int32, 0);

    db.ExecuteNonQuery(dbCommand);

    return db.GetParameterValue(dbCommand, "rc");
}

And the stored proc:

create or replace procedure testscalar (
  rc OUT INTEGER
  )
AS
begin
  SELECT COUNT(*) INTO rc FROM test;
end;
 

Hope that helps. 

Same problem I encountered, I ended up doing ExecuteReader, it works like a champ and the same I suggested in my previous post :) 

Did you have to setup a REF CURSOR in order to do the reader? If so, then the single parameter may be less overhead. It probably depends on the individual situation. 

I was curious, so I modified my code to test both the parameter solution and the reader solution. It turns out, there is some overhead with the reader.

Total parameter time: 21.5936117999998
Total reader time:    35.6872715999988

Press <ENTER> to continue...

But that is after 10,000 straight calls. So if you are interested in the absolute peak performance, then the parameter method is the way to go. But for most other uses, the reader is probably just fine. 

Thanks everyone for their responses.  I'm now using ExecuteNonQuery with a single out parameter.  It works fine as long as I put exception blocks in my SQL Procedure for 'No Data Returned'.

That's an interesting link. Did you read the section on "Accessing Return Values"?

http://msdn2.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic5

That might be another way to go. It's roughly the same as the parameter approach already discussed, though.