1 Reply Latest reply on Oct 27, 2017 2:11 AM by Gaz in Oz

    @ 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?