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..