Oracle - Getting Select Count(*) from ... as an output parameter in System.Data.OracleClient
- by cbeuker
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..
