ExecuteNonQuery returns ORA-01036 illegal variable name/number

[Jim Brandley]

I have used ExecuteNonQuery with no trouble for inserts, updates and
deletes. The best practices book I have says this is also the most efficient
way to return multiple column values from a single row. The requested fields
can be changed by users adding/removing fields from the form, so a stored
proc will not work - variable number of return values. I tried:
Select name, rank into ( :V1, :V2 ) from mytable where keyvalue = :V3
and get ORA-01036. The bound parameters are named to match the sql
statement. IN params work fine, but I cannot make it happy with the OUT
params. Any help would be appreciated.

 

[Brad Wood]

I have used ExecuteNonQuery with no trouble for inserts, updates and
deletes. The best practices book I have says this is also the most efficient
way to return multiple column values from a single row. The requested fields
can be changed by users adding/removing fields from the form, so a stored
proc will not work - variable number of return values. I tried:
Select name, rank into ( :V1, :V2 ) from mytable where keyvalue = :V3
and get ORA-01036. The bound parameters are named to match the sql
statement. IN params work fine, but I cannot make it happy with the OUT
params. Any help would be appreciated.

Just a thought - try the other provider for oracle (if you're using MS's
, try using oracle's or vice versa).

 

[Jim Brandley]

I thought of that, but we're pretty close to shipping, and that's a
scary step. I'm using the old MS provider.

 

[David Sceppa]

Jim,

As far as I can tell, Oracle does not support this functionality for a
simple SELECT query. Using a very basic query like:

SELECT 'Hello', 'World' INTO 1, 2 FROM DUAL

generated the exception you described, using both the OracleClient that's
built into the .NET Framework, and Oracle's ODP.NET.


To return the desired data through output parameters, wrap your query
in an anonymous block. Here's an example:

BEGIN
SELECT 'Hello', 'World' INTO 1, 2 FROM DUAL;
END;


Once I used this type of query, I was able to retrieve data through
output parameters just fine. Here's a snippet of code:

string strConn, strSQL;
strConn = "Data Source=...";
OracleConnection cn = new OracleConnection(strConn);
cn.Open();

strSQL = "BEGIN";
strSQL += " SELECT 'Hello', 'World' INTO 1, 2 FROM DUAL;";
strSQL += " END;";
OracleCommand cmd = new OracleCommand(strSQL, cn);
cmd.Parameters.Add("1", OracleType.VarChar, 255);
cmd.Parameters.Add("2", OracleType.VarChar, 255);
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.WriteLine("{0}, {1}!",
cmd.Parameters[0].Value,
cmd.Parameters[1].Value);

cn.Close();


I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.

 

[Jim Brandley]

Thank you! I'll give that a try. I thought there must be some way to do it
without a cursor.

 

[Jim Brandley]

That worked great! Thanks for your help. Interestingly, timing the
DataReader and the executeNonQuery consistently returned results within
noise levels at around 600 microseconds in my tests. I found that
surprising.

 

[David Sceppa]

Jim,

Glad to hear that information resolved your problem. Someone else may
be able to address the Oracle performance. There may be a server-side
performance penalty for using an anonymous block.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.