Oracle - Getting Select Count(*) from ... as an output parameter in System.Data.OracleClient

Question

Greetings all,

I have a question. I am trying to build a parametrized query to get me the number of rows from a table in Oracle. Rather simple. However I am an Oracle newbie..

I know in SQL Server you can do something like:

Select @outputVariable = count(*) from sometable where name = @SomeOtherVariable

and then you can set up an Output parameter in the System.Data.SqlClient to get the @outputVariable.

Thinking that one should be able to do this in Oracle as well, I have the following query

Select count(*) into :theCount from sometable where name = :SomeValue

I set up my oracle parameters (using System.Data.OracleClient - yes I know it will be deprecated in .Net 4 - but that's what I am working with for now) as follows

IDbCommand command = new OracleCommand();
command.CommandText = "Select count(*) into :theCount from sometable where name = :SomeValue";
command.CommandType = CommandType.Text;

OracleParameter parameterTheCount = new OracleParameter(":theCount", OracleType.Number);
parameterTheCount .Direction = ParameterDirection.Output;
command.Parameters.Add(parameterTheCount );

OracleParameter parameterSomeValue = new OracleParameter(":SomeValue", OracleType.VarChar, 40);
parameterSomeValue .Direction = ParameterDirection.Input;
parameterSomeValue .Value = "TheValueToLookFor";
command.Parameters.Add(parameterSomeValue );
command.Connection = myconnectionObject;
command.ExecuteNonQuery();
int theCount = (int)parameterTheCount.Value;

At which point I was hoping the count would be in the parameter parameterTheCount that I could readily access.

I keep getting the error ora-01036 which http://ora-01036.ora-code.com tells me to check my binding in the sql statement. Am I messing something up in the SQL statement? Am I missing something simple elsewhere?

I could just use command.ExecuteScaler() as I am only getting one item, and am probably going to end up using that, but at this point, curiosity has got the better of me. What if I had two parameters I wanted back from my query (ie: select max(ColA), min(ColB) into :max, :min.....)

Thanks..

Answer 1

I think the problem is that you have a trailing space in the parameter name for parameterTheCount.

Edit

Now remove the colons from the parameter names in the constructor to OracleParameter.

Answer 2

Some versions of the ADO does not need the colon : configuring OracleParameter.

Instead of:

new OracleParameter(":theCount", OracleType.Number);

try

new OracleParameter("theCount", OracleType.Number);

Anyway, I think you have to use the ExecuteScalar() function of the IDbCommand and avoiding use of into (which I'm not sure it's valid on this context). I mean:

IDbCommand command = new OracleCommand(); 
command.CommandText = "Select count(*) from sometable where name = :SomeValue";
command.CommandType = CommandType.Text;

OracleParameter parameterSomeValue = new OracleParameter("SomeValue", OracleType.VarChar, 40);
parameterSomeValue .Direction = ParameterDirection.Input;
parameterSomeValue .Value = "TheValueToLookFor";
command.Parameters.Add(parameterSomeValue );
command.Connection = myconnectionObject;
int theCount = (int)command.ExecuteScalar();

Disclaimer: The code have not been compiled, and may be have any little error.

Update: If you take a look on the Oracle SELECT syntax, you will see that The SELECT INTO sentence is not recognized. But it's valid in PLSQL syntax as you can see here. You can try one of the following to see if it works (not tested):

command.CommandText = "begin Select count(*) into :someCount from sometable where name = :SomeValue; end;";