Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

1cfa647c-b115-4bce-aaf6-f2aedb2bbe78Oct 25 2017 — edited Oct 26 2017

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?

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 23 2017
Added on Oct 25 2017
1 comment
1,023 views