ExecuteNonQuery returns ORA-01036 illegal variable name/number

2 Apr 2005 5:43 PM

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.

4 Apr 2005 4:46 PM

Brad Wood

Jim Brandley wrote:

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

4 Apr 2005 5:03 PM

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.

7 Apr 2005 7:19 PM

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 :p1, :p2 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 :p1, :p2 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 :p1, :p2 FROM DUAL;";
strSQL += " END;";
OracleCommand cmd = new OracleCommand(strSQL, cn);
cmd.Parameters.Add(":p1", OracleType.VarChar, 255);
cmd.Parameters.Add(":p2", 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.

8 Apr 2005 4:31 PM

Jim Brandley

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

Show quoteHide quote

"David Sceppa" <davi***@online.microsoft.com> wrote in message
news:XbdgBb6OFHA.2944@TK2MSFTNGXA01.phx.gbl...
> 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 :p1, :p2 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 :p1, :p2 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 :p1, :p2 FROM DUAL;";
>   strSQL += " END;";
>   OracleCommand cmd = new OracleCommand(strSQL, cn);
>   cmd.Parameters.Add(":p1", OracleType.VarChar, 255);
>   cmd.Parameters.Add(":p2", 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.
>

14 Apr 2005 11:47 PM

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.

Show quoteHide quote

"David Sceppa" <davi***@online.microsoft.com> wrote in message
news:XbdgBb6OFHA.2944@TK2MSFTNGXA01.phx.gbl...
> 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 :p1, :p2 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 :p1, :p2 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 :p1, :p2 FROM DUAL;";
>   strSQL += " END;";
>   OracleCommand cmd = new OracleCommand(strSQL, cn);
>   cmd.Parameters.Add(":p1", OracleType.VarChar, 255);
>   cmd.Parameters.Add(":p2", 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.
>

15 Apr 2005 3:14 PM

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.