Please consider the following procedure
CREATE OR REPLACE PROCEDURE MyProc
("@RECORD_ID" NUMBER,
"@RELEASED" CHAR)
IS
BEGIN
UPDATE
MyTable
SET
RELEASED = "@RELEASED"
WHERE
ID = "@RECORD_ID";
END;
In older code, we ensured that we had all our parameters prefixed with the "@" sign and relied completely on the code to ensure that the values passed in matched the ordinal positioning exactly.
However, in today's code, we allow calls to be made without caller knowing the parameter list order. We just accept a list of parameters and use the .SelectCommand.BindByName = true to ensure that requirements are met.
Here's a snipped of what we are doing to execute the proc
using (var a = new OracleDataAdapter(procedureName, _connectionString))
{
a.SelectCommand.CommandType = CommandType.StoredProcedure;
a.SelectCommand.BindByName = true;
a.SelectCommand.Parameters.AddRange(parameters.ToArray());
a.SelectCommand.Connection.Open();
a.SelectCommand.ExecuteNonQuery();
...
}
The above approach won't work on the procedure (above) because the SelectCommand.ExecuteNonQuery() throws the following exception:
ORA-06550: line 1, column 39:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
( ) - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table continue avg count current exists max min prior sql
stddev sum variance execute multiset the both leading
trailing forall merge year month day hour minute second
timezone_hour timezone_minute timezone_region timezone_abbr
time timestamp interval date
<a string literal with character set specification>
ORA-06550: line 1, column 56:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql
If we remove a.SelectCommand.BindByName = true;, then it works; however, we would prefer to keep the value to true and the @ sign in the parameters.
Is there a fix or workaround for this? (other than not using the @ sign in the parameter name?