@ Sign in procedure parameters names causes it not to be found and/or errors.

1cfa647c-b115-4bce-aaf6-f2aedb2bbe78

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?

Gaz in Oz

<random hex like string> wrote: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:

...so what does the "NonQuery" look like, that is trying to be executed?

Print it out and paste it here, that way some one actually seeing the query might be able to help.